Main Tutorials

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.

employee_salary

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

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

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;
Note
In before update trigger, do not update the employee_salary table, it will create a recursively trigger and run until it has run out of memory.

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;
EMP_ID SALARY EMP_NAME
101 28000 Pranav
201 40000 Vikram
301 43000 Nikhil

select * from EMPLOYEE_SALARY_LOG;
EMP_ID NEW_SALARY UPDATED_DATE UPDATED_BY
101 28000 10-JUN-17 SYSTEM
301 43000 10-JUN-17 SYSTEM

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.

employee_salary

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

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

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;
EMP_ID SALARY EMP_NAME
101 70000 Pranav
201 45000 Vikram
301 100000 Nikhil

select * from EMPLOYEE_SALARY_HIKE_LOG;
EMP_ID NEW_SALARY HIKE UPDATED_DATE UPDATED_BY
101 70000 55000 10-JUN-17 HR
301 100000 65000 10-JUN-17 HR

References

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

About Author

author image
Dhaval Dadhaniya is a software engineer by profession and reader/writter by passion. He is working in a well reputed MNC as an applications Developer with 5 years of experience. He have good understanding and knowledge of Java, Database, Spring, Hibernate and exploring other technologies related to Software development. Befriend him on Facebook

Comments

Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments