After insert trigger in oracle plsql

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.