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
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
About Author
Comments
Subscribe
0 Comments