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 = :o ld.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;

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

3 Responses to “Oracle: Using the AFTER INSERT and AFTER UPDATE triggers”

  1. May 05, 2013 at 10:33 am, dano said:

    where empid = ld.empid???

    Reply

    • May 05, 2013 at 10:40 am, Vishwanath Dalvi said:

      empid = : old.empid

      please remove the space between colon and old, It considers a smiley if you remove the space.

      Reply

  2. November 15, 2013 at 1:20 pm, Ricardo said:

    thanks :-)

    Reply

Leave a Reply