AFTER UPDATE TRIGGER in Oracle PLSQL

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:

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’ using 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.

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:

CREATE OR REPLACE TRIGGER new_employee_trigger
AFTER UPDATE
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 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:

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

Let’s update the record in ‘new_employee’ table as:

UPDATE new_employee
SET employee_name = 'Emp EFGH';

Now 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
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.