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 table Emp_backup( Empid number(10),
Fname varchar(25),
Lname varchar(25)
);
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 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:
