After Trigger - PL/SQL Program

  • A database trigger is a store procedure that get executed automatically whenever events occurs. For example: Insert, Update, Delete operations.
  • After insert trigger means that, Oracle will fire trigger after the insert operation is executed.
  • After update trigger means that, Oracle will fire trigger after the update operation is executed.

After Insert Trigger

a) When a record is inserted into the 'Emp' table, a record should be inserted in the 'Emp_backup' table.

Answer:

Step 1: Create a table 'emp'

Create table Emp( Empid number(10),
Fname varchar(25),
Lname varchar(25)
);


Create a 'Emp_backup' table

Create table Emp_backup( Empid number(10),
Fname varchar(25),
Lname varchar(25)
);


Step 2:

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 'emp_after_insert' is created. Now, if user will insert a record in the 'Emp' table, it will be automatically inserted into 'Emp_backup' table. As shown in the output.

Output:

after insert trigger

After Update Trigger

b) Update a record in the 'Emp_backup' table when corresponding record is updated in the 'Emp' table.

Answer:

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;


This code will change the 'Empid' from table 'Emp' and record in the 'Emp_backup' will also get changed as shown in the output.

Output:  

after update trigger