Oracle PL/SQL – After UPDATE Trigger example

This article shows you how to use AFTER UPDATE TRIGGER, it will fire after the update operation is executed.

1. Logging example

In this example, after each update on ‘SALARY’ column of employee_salary, it will fire a ‘after update’ trigger and insert the new updated data into a employee_salary_log table, for audit purpose.

1.1 Create tables and trigger.

CREATE TABLE employee_salary
    EMP_ID number(10),
    SALARY number(10),
    EMP_NAME varchar2(50)
);

CREATE TABLE employee_salary_log
    EMP_ID number(10),
    NEW_SALARY number(10),
    UPDATED_DATE date,
    UPDATED_BY varchar2(20)
);

CREATE OR REPLACE TRIGGER trg_log_employee_salary
AFTER UPDATE 
  OF SALARY 
    ON employee_salary 
FOR EACH ROW
DECLARE
username varchar2(20);
BEGIN
    -- get current login user
    SELECT USER INTO username FROM dual;
    -- Insert new values into log table.
    INSERT INTO employee_salary_log VALUES (
		:NEW.EMP_ID, :NEW.SALARY, sysdate, username);
END;

1.2 Insert data to test the trigger.

INSERT INTO employee_salary VALUES (101,15000,'Pranav');
INSERT INTO employee_salary VALUES (201,40000,'Vikram');
INSERT INTO employee_salary VALUES (301,35000,'Nikhil');
-- fire trigger, insert into log table
UPDATE employee_salary SET SALARY = '28000' WHERE emp_id = 101;
-- fire trigger, insert into log table
UPDATE employee_salary SET SALARY = '43000' WHERE emp_id = 301;

1.3 Display the data.

select * from EMPLOYEE_SALARY;

select * from EMPLOYEE_SALARY_LOG;

2. Logging with WHEN condition

In this example, after each update on ‘SALARY’ column of employee_salary with hike greater than 50000, it will fire a ‘after update’ trigger and insert the new updated data into a employee_salary_log table.

2.1 Create tables and trigger.

CREATE TABLE employee_salary
    EMP_ID number(10),
    SALARY number(10),
    EMP_NAME varchar2(50)
);

CREATE TABLE employee_salary_hike_log
    EMP_ID number(10),
    NEW_SALARY number(10),
    HIKE number(10),
    UPDATED_DATE date,
    UPDATED_BY varchar2(20)
);

CREATE OR REPLACE TRIGGER trg_log_salary_hike
AFTER UPDATE 
  OF SALARY 
    ON employee_salary 
FOR EACH ROW
WHEN ((NEW.SALARY - OLD.SALARY) > 50000)
DECLARE
username varchar2(20);
BEGIN
    SELECT USER INTO username FROM dual;
    -- Insert new values into log table.
    INSERT INTO employee_salary_hike_log VALUES (
		:NEW.EMP_ID, :NEW.SALARY, :NEW.SALARY - :OLD.SALARY ,sysdate, username);
END;

2.2 Insert data to test the trigger.

INSERT INTO employee_salary VALUES (101,15000,'Pranav');
INSERT INTO employee_salary VALUES (201,40000,'Vikram');
INSERT INTO employee_salary VALUES (301,35000,'Nikhil');
-- new salary - old salary > 50000, fire after update trigger, insert into log
UPDATE employee_salary SET SALARY = '70000' WHERE emp_id = 101;
UPDATE employee_salary SET SALARY = '100000' WHERE emp_id = 301;
-- new salary - old salary < 50000, no action.
UPDATE employee_salary SET SALARY = '45000' WHERE emp_id = 201;

2.3 Display the data.

select * from EMPLOYEE_SALARY;

select * from EMPLOYEE_SALARY_HIKE_LOG;

References

1. PL/SQL Triggers :- Oracle official docs
2. Oracle PL/SQL – Before UPDATE Trigger example