Oracle: Using the AFTER INSERT and AFTER UPDATE triggers

A database trigger is a stored procedure that automatically executes whenever an event occurs. The event may be insert-delete-update operations.

Oracle initiates an ‘AFTER INSERT’ trigger after an insert event has been occurred and an ‘AFTER UPDATE’ trigger after an update event has been occurred.

Let’s see an example for ‘AFTER INSERT’ trigger.

Syntax:

CREATE or REPLACE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
DECLARE
variable declarations
BEGIN
trigger statement
END;

First problem: We want to insert a record in the ’emp_backup’ table, if a record is inserted into the ’emp’ table.

Solution – An AFTER INSERT Trigger

Let’s create an ’emp’ table.

SQL> create table emp(
empid number(10),
fname varchar(25),
lname varchar(25)
);

Now create an ’emp_backup’ table with the same structure as ’emp’ table.

SQL> create table emp_backup(
empid number(10),
fname varchar(25),
lname varchar(25)
);

Now, to insert a record in the ’emp_backup’ table if a record is inserted into ’emp’ table, we will write an ‘AFTER INSERT’ Trigger.

SQL> CREATE or REPLACE TRIGGER emp_after_insert
AFTER INSERT ON emp
FOR EACH ROW
DECLARE
BEGIN
insert into emp_backup values (:new.empid, :new.fname, :new.lname);
DBMS_OUTPUT.PUT_LINE('Record successfully inserted into emp_backup table');
END;

The above trigger named ’emp_after_insert’ is initiated for each row inserted into emp table. Now, if we insert a row in the ’emp’ table, it will be automatically inserted into ’emp_backup’ table.

SQL> insert into emp values(1,'jon','gibson');
Record successfully inserted into emp_backup table
1 row created.

Now query the ’emp’ table.

SQL> select * from emp;

     EMPID FNAME                     LNAME
---------- ------------------------- -------------------------
         1 jon                       gibson

Query the ’emp_backup’ table.

SQL> select * from emp_backup;

     EMPID FNAME                     LNAME
---------- ------------------------- -------------------------
         1 jon                       gibson

Note: If you are not getting the message ‘Record successfully inserted into emp_backup table‘ do the below command before creating the trigger.

SQL> set serveroutput on;

Let’s see an example for ‘AFTER UPDATE’ trigger.

Syntax:

CREATE or REPLACE TRIGGER trigger_name
AFTER UPDATE ON table_name
FOR EACH ROW
DECLARE
variable declarations
BEGIN
trigger statement
END;

Second Problem: We want o update a record in the ’emp_backup’ table if a corresponding record is updated in the ’emp’ table.

As we’ve already created both the tables in above example, we will directly write a trigger for ‘AFTER UPDATE’.

SQL> CREATE or REPLACE TRIGGER emp_after_update
AFTER UPDATE OF empid ON emp
FOR EACH ROW
DECLARE
BEGIN
update emp_backup
set empid = :new.empid
where empid = :old.empid;
DBMS_OUTPUT.PUT_LINE('empid successfully updated into emp_backup table');
END;

The above trigger named ’emp_after_update’ will be initiated whenever ’empid’ column in ’emp’ table gets updated.

Now before updating ’empid’ column in ’emp’ table see the ’emp’ and ’emp_backup’ table records.

SQL> select * from emp;

     EMPID FNAME                     LNAME
---------- ------------------------- -------------------------
         1 jon                       gibson

SQL> select * from emp_backup;

     EMPID FNAME                     LNAME
---------- ------------------------- -------------------------
         1 jon                       gibson

Now Update the ’empid’ column in ’emp’ table.

SQL> update emp
set empid=5
where empid=1;
empid successfully updated into emp_backup table
1 row updated.

After update of ’empid’ in ’emp’ table, let us see the ’emp_backup’ table.

SQL> select * from emp_backup;

     EMPID FNAME                     LNAME
---------- ------------------------- -------------------------
         5 jon                       gibson

Note: If you are not getting the message ‘empid successfully updated into emp_backup table‘ execute the below command before creating the trigger.

SQL> set serveroutput on;