Oracle PLSQL AFTER INSERT TRIGGER means that the trigger will get executed or triggered just after an INSERT operation is performed on the table for which the trigger is written.
Oracle PLSQL syntax to create an AFTER INSERT TRIGGER is:
CREATE OR REPLACE TRIGGER trigger_name
AFTER INSERT
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 INSERT 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 INSERT TRIGGER
Suppose we create a table named ‘new_employee’ using the script below:
CREATE TABLE new_employee ( employee_id NUMBER ,employee_name VARCHAR2(1000) ,creation_date DATE ,created_by VARCHAR2(1000) );
If we query the ‘new_employee’ table as:
SELECT * 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’ suing the script below:
CREATE TABLE new_employee_duplicate
AS(SELECT *
FROM new_employee);
If we query the ‘new_employee_duplicate’ table as
SELECT * 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.
Creating an Oracle PLSQL AFTER INSERT Trigger on new_employee table.
Now let’s create a trigger on ‘new_employee’ table so that whenever we are entering 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 INSERT TRIGGER as:
CREATE OR REPLACE TRIGGER new_employee_trigger
AFTER INSERT
ON new_employee
FOR EACH ROW
DECLARE
v_creator_name VARCHAR2(1000);
v_creation_date DATE;
BEGIN
--Getting the name of the current logged in User
SELECT USER INTO v_creator_name
FROM dual;
--setting system date in v_creation_date
v_creation_date := sysdate;
--Inserting data the new_employee_duplicate table
INSERT INTO new_employee_duplicate
VALUES (:new.employee_id
,:new.employee_name
,v_creation_date
,v_creator_name
);
END;
Here we have created a PLSQL AFTER INSERT TRIGGER named ‘new_employee_trigger’ which will insert a record in the ‘new_employee_duplicate’ table as soon as insert operation is performed on ‘new_employe’ table.
Let’s see PLSQL AFTER INSERT TRIGGER in action.
Let’s add a row in ‘new_employee’ table as:
INSERT INTO new_employee VALUES (1, 'Emp ABCD',sysdate,’SCOTT’);
Once we run the above INSERT statement we can query the ‘new_employee’ as:
SELECT * 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
SELECT * FROM new_employee_duplicate;
We will get the following output:
| EMPLOYEE_ID | EMPLOYEE_NAME | CREATION_DATE | CREATED_BY |
| 1 | Emp ABCD | 12/12/2012 5:34:49 PM | SCOTT |
Here using the Oracle PLSQL AFTER INSERT TRIGGER we can see that in the ‘new_employee_duplicate’ table a record got inserted as soon as we inserted 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.