Creating a Trigger With the AFTER and FOR EACH ROW Option

Example: Creating a Database Trigger WIth the AFTER Option shows the code for a trigger on the employees table. In the example, the table-level trigger fires after salaries in the employees table are updated and writes a record in an audit table.

With the FOR EACH ROW option, the trigger writes a record to the emp_audit table for each update. This record contains the employee ID, the date of the update, the updated salary, and the original salary. Note the use of the :OLD.column_name and :NEW.column_name to access the values in the columns before and after the update.

With the AFTER keyword, the trigger can also query or change the same table. Triggers can only do that after the initial changes are applied, and the table is back in a consistent state.

Because the trigger uses the FOR EACH ROW clause, it might be executed multiple times, such as when updating or deleting multiple rows. You might omit this clause if you just want to record the fact that the operation occurred, but not examine the data for each row.

Creating a Database Trigger WIth the AFTER Option

-- create a table to use for with the trigger in this example if
-- it has not already been created previously
-- if the table does not exist, the trigger will be invalid
CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE, 
                         new_sal NUMBER(8,2), old_sal NUMBER(8,2) );
-- create or replace the trigger
CREATE OR REPLACE TRIGGER audit_sal
   AFTER UPDATE OF salary ON employees FOR EACH ROW
BEGIN
-- bind variables are used here for values
  INSERT INTO emp_audit VALUES( :OLD.employee_id, SYSDATE, 
                                 :NEW.salary, :OLD.salary );
END;
/
-- fire the trigger with an update of salary
UPDATE employees SET salary = salary * 1.01 WHERE manager_id = 122;

-- check the audit table to see if trigger was fired
SELECT * FROM emp_audit;