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 occurred and an ‘AFTER UPDATE’ trigger after an update event has 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.

Let’s create a ‘emp’ table.

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

Now create a ‘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 see the ‘emp’ table.

SQL> select * from emp;
EMPID FNAME LNAME 
---------- ------------------------- ------------------------- 
1 jon gibson

Now see 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;

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

As we have 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;