Instead of Triggers

Instead of Triggers -Learn how  when to  use instead of Triggers  with programming Example.

When you are not allowed modify a view directly then you can apply instead of triggers concept to update the views.

For views concept please click on following URL’s: ORACLE VIEWS , Complex views in oracle.

The INSTEAD OF trigger is used to modify the base tables directly instead of modifying the view for the given event.

INSTEAD OF trigger” is the special type of trigger that is used when any DML event is going to occur on the complex view.

If we observe the concept of complex views in oracle, we can understand that views cannot updatable in some special cases. For those special cases we can update using instead of triggers.

For table and views reference please look into the concept of Complex views in oracle.

Creation of View

CREATE VIEW std_dept_view
  ( std_no,std_name,Dept_name
  ) AS
SELECT std.std_no,
  std.std_name,
  dept.dept_name
FROM Student std,
  Department dept
WHERE std.dept_no=dept.dept_no;

Updation of view

UPDATE std_dept_view SET STD_NAME ='SHAIK MOULALI' WHERE STD_NO=3;

Output

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

To avoid the error encounter during updating view we are going to use “instead of trigger.”

Creation of Instead of Trigger

create or replace
TRIGGER std_dept__view_modify_trg
INSTEAD OF UPDATE
ON std_dept_view
FOR EACH ROW
BEGIN
update Student set std_name=:new.std_name where std_no=:old.std_no;
END;

Creation of INSTEAD OF trigger for ‘UPDATE’ event on the ‘std_dept__view_modify_trg’ view at the ROW level. It contains the update statement to update the student name in the base table ‘Student’.
Update statement uses ‘:NEW’ and ‘: OLD’ to find the value of columns before and after the update.

Output

Trigger Created

Update of view after instead-of trigger

Now the error will not come as the “instead of trigger” will handle the update operation of this complex view. And when the code has executed the student_name of student will be updated to SHAIK MOULALI where student number is 3.

begin
UPDATE std_dept_view SET STD_NAME ='SHAIK MOULALI' WHERE STD_NO=3;
commit;
end;

Output

3 rows updated.

Select query for view

select * from std_dept_view;

Output