PL/SQL Row-Level Triggers

In PL/SQL, triggers are special stored procedures that automatically execute in response to specific events on a table or view. Among these, row-level triggers are particularly useful for enforcing business rules, maintaining data integrity, and automating tasks.

This article explores row-level triggers with practical examples and explanations.

What are Row-Level Triggers?

Row-level triggers execute once for each row affected by an INSERT, UPDATE, or DELETE operation. This makes them ideal for performing actions specific to each row being processed.

Syntax of Row-Level Triggers

The basic syntax for creating a row-level trigger in PL/SQL is:

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE [INSERT | UPDATE | DELETE] ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic here
END;

Parameters:

• AFTER | BEFORE: Specifies whether the trigger should fire before or after the triggering event.
• INSERT | UPDATE | DELETE: Specifies the DML operation that activates the trigger.
• FOR EACH ROW: Indicates that the trigger is row-level, meaning it fires for each row affected by the DML operation.

Examples of PL/SQL Row-Level Triggers

Example 1: Auditing Changes in a Table

Suppose you have an employees table and want to track any changes to employee salaries. You can create a row-level trigger that logs these changes into an employee_audit table.

Step 1: Create the employees Table

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    salary NUMBER,
    last_modified DATE
);

Step 2: Create the employee_audit Table

CREATE TABLE employee_audit (
    audit_id NUMBER PRIMARY KEY,
    emp_id NUMBER,
    old_salary NUMBER,
    new_salary NUMBER,
    change_date DATE
);

Step 3: Create the Trigger

CREATE OR REPLACE TRIGGER trg_audit_salary_changes
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (audit_id, emp_id, old_salary, new_salary, change_date)
    VALUES (employee_audit_seq.NEXTVAL, :OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
END;

Explanation: This trigger logs the employee ID, old salary, new salary, and the date of change in the employee_audit table whenever an employee's salary is updated.

Testing Example 1:

Insert Initial Data into employees:

INSERT INTO employees (emp_id, emp_name, salary, last_modified) VALUES (1, 'Alice', 50000, SYSDATE);
INSERT INTO employees (emp_id, emp_name, salary, last_modified) VALUES (2, 'Bob', 45000, SYSDATE);
INSERT INTO employees (emp_id, emp_name, salary, last_modified) VALUES (3, 'Charlie', 60000, SYSDATE);

Update Salary to Trigger the Audit:

UPDATE employees SET salary = 55000 WHERE emp_id = 1;

Expected Output:

• The employees table will show Alice's updated salary.
• The employee_audit table will have a new record logging the change:

SELECT * FROM employee_audit;

Output:

AUDIT_ID | EMP_ID | OLD_SALARY | NEW_SALARY | CHANGE_DATE
---------|--------|------------|------------|-------------
1        | 1      | 50000      | 55000      | 23-AUG-2024

Example 2: Enforcing Business Rules

Next, we enforce a business rule that prevents employees from having a negative salary.

CREATE OR REPLACE TRIGGER trg_no_negative_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
    END IF;
END;

Explanation: This trigger checks the new salary value before an insert or update. If the salary is negative, an error is raised, preventing the operation.

Testing Example 2:

Attempt to Insert an Employee with a Negative Salary:

INSERT INTO employees (emp_id, emp_name, salary, last_modified) VALUES (4, 'David', -10000, SYSDATE);

Output:

The operation will fail, and you will receive the following error message: ORA-20001: Salary cannot be negative.

Example 3: Automatically Updating a Timestamp

To maintain a last modified timestamp for each row in a table, you can use a row-level trigger.

CREATE OR REPLACE TRIGGER trg_update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    :NEW.last_modified := SYSDATE;
END;

Explanation: This trigger automatically updates the last_modified column to the current date and time whenever a row in the employees table is updated.

Testing Example 3:

Update an Employee's Name:

UPDATE employees SET emp_name = 'Alicia' WHERE emp_id = 1;

Output:

The employees table will show the updated name and the last_modified timestamp:

SELECT * FROM employees WHERE emp_id = 1;

EMP_ID | EMP_NAME | SALARY | LAST_MODIFIED
-------|----------|--------|--------------
1      | Alicia   | 55000  | 23-AUG-2024

Conclusion

Row-level triggers in PL/SQL are useful tools that automatically perform tasks whenever data is added, updated, or deleted in a table. They work on each individual row, allowing you to enforce rules and ensure data accuracy. By understanding how to use these triggers, you can make your PL/SQL applications more reliable and efficient, handling data operations with greater control and precision.