Oracle PLSQL AFTER UPDATE TRIGGER means that the trigger will get executed or triggered just after an UPDATE operation is performed on the table for which the trigger is written.
Oracle PLSQL syntax to create an AFTER UPDATE TRIGGER is:
CREATE OR REPLACE TRIGGER trigger_name
AFTER UPDATE
ON table_name
[FOR EACH ROW]
DECLARE
–variable declaration section of code
BEGIN
–code to be triggered
EXCEPTION
WHEN..
–exception handling code
END;
Here in the above Oracle PLSQL trigger syntax the ‘trigger_name’ is the name given to AFTER INSERT TRIGGER.
Some points to remember about PLSQL AFTER UPDATE TRIGGER are:
We cannot create an AFTER INSERT TRIGGER on an Oracle PLSQL VIEW.
We cannot update :NEW values with AFTER INSERT TRIGGER.
We cannot update the :OLD values using AFTER INSERT TRIGGER.
Example of Oracle PLSQL AFTER UPDATE TRIGGER
Suppose we create a table named ‘new_employee’ using the script below:
1 | CREATE TABLE new_employee |
2 | ( |
3 | employee_id NUMBER |
4 | ,employee_name VARCHAR2(1000) |
5 | ,creation_date DATE |
6 | ,created_by VARCHAR2(1000) |
7 | ); |
If we query the ‘new_employee’ table as:
1 | SELECT * |
2 | FROM new_employee; |
We will get the following output:
| EMPLOYEE_ID | EMPLOYEE_NAME | CREATION_DATE | CREATED_BY |
Also we create a duplicate table of ‘new_employee’ table as new_employee_duplicate’ using the script below:
1 | CREATE TABLE new_employee_duplicate |
2 | AS(SELECT * |
3 | FROM new_employee); |
If we query the ‘new_employee_duplicate’ table as
1 | SELECT * |
2 | FROM new_employee_duplicate; |
We will get the following output:
| EMPLOYEE_ID | EMPLOYEE_NAME | CREATION_DATE | CREATED_BY |
At this moment we don’t have any data in ‘new_employee’ and ‘new_employee_duplicate’ tables.
Now let’s create a trigger on ‘new_employee’ table so that whenever we are updating any employee record in the ‘new_employee’ table the same record also gets stored in ‘new_employee_duplicate’ table.
For this purpose we can create a PLSQL AFTER UPDATE TRIGGER as:
1 | CREATE OR REPLACE TRIGGER new_employee_trigger |
2 | AFTER UPDATE |
3 | ON new_employee |
4 | FOR EACH ROW |
5 | DECLARE |
6 | v_creator_name VARCHAR2(1000); |
7 | v_creation_date DATE; |
8 | BEGIN |
9 | --Getting the name of the current logged in User |
10 | SELECT USER INTO v_creator_name |
11 | FROM dual; |
12 | --setting system date in v_creation_date |
13 | v_creation_date := sysdate; |
14 | --Inserting data the new_employee_duplicate table |
15 | INSERT INTO new_employee_duplicate |
16 | VALUES (:new.employee_id |
17 | ,:new.employee_name |
18 | ,v_creation_date |
19 | ,v_creator_name); |
20 | END; |
Here we have created a PLSQL AFTER UPDATE TRIGGER named ‘new_employee_trigger’ which will insert a record in the ‘new_employee_duplicate’ table as soon as update operation is performed on ‘new_employee’ table.
Let’s see PLSQL AFTER UPDATE TRIGGER in action.
Let’s add a row in ‘new_employee’ table as:
1 | INSERT INTO new_employee |
2 | VALUES |
3 | (1, 'Emp ABCD',sysdate,'SCOTT'); |
Once we run the above INSERT statement we can query the ‘new_employee’ as:
1 | SELECT * |
2 | FROM new_employee; |
We will get the following result:
| EMPLOYEE_ID | EMPLOYEE_NAME | CREATION_DATE | CREATED_BY |
| 1 | Emp ABCD | 12/12/2012 5:34:49 PM | SCOTT |
Also if we query the ‘new_employee_duplicate’ table as
1 | SELECT * |
2 | FROM new_employee_duplicate; |
We will get the following output:
| EMPLOYEE_ID | EMPLOYEE_NAME | CREATION_DATE | CREATED_BY |
Let’s update the record in ‘new_employee’ table as:
1 | UPDATE new_employee |
2 | SET employee_name = 'Emp EFGH'; |
Now if we query the ‘new_employee’ table as:
1 | SELECT * |
2 | FROM new_employee; |
We will get the following output:
| EMPLOYEE_ID | EMPLOYEE_NAME | CREATION_DATE | CREATED_BY |
| 1 | Emp EFGH | 12/12/2012 5:34:49 PM | SCOTT |
Also, if we query the ‘new_employee_duplicate’ table we will get:
| EMPLOYEE_ID | EMPLOYEE_NAME | CREATION_DATE | CREATED_BY |
| 1 | Emp EFGH | 12/12/2012 5:34:49 PM | SCOTT |
Here using the Oracle PLSQL AFTER UPDATE TRIGGER we can see that in the ‘new_employee_duplicate’ table a record got inserted as soon as we updated a record in ‘new_employee’ table.
Please note that the values in CREATION_DATE and CREATED_BY columns may be different for you as they depend on system date and logged in user.