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.

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

上一篇: Java 8 Streams map() examples
下一篇: Java 8 Tutorials
 评论 ( What Do You Think )
名称
邮箱
网址
评论
验证
   
 

 


  • 微信公众号

  • 我的微信

站点声明:

1、一号门博客CMS,由Python, MySQL, Nginx, Wsgi 强力驱动

2、部分文章来源于互联网, 若有侵权, 联系邮箱:summer@yihaomen.com, 同时欢迎大家注册用户,主动发布文章.

3、鄂ICP备14001754号-3, 鄂公网安备 42280202422812号