Welcome to the last tutorial on the “INSTEAD-OF” triggers in Oracle database. Till so far in this series we have seen how to create INSTEAD-OF INSERT trigger and INSTEAD-OF UPDATE trigger. The only trigger that is left now is the INSTEAD-OF DELETE trigger which we will cover in today’s tutorial.

Similar to other instead-of triggers which we have seen in previous tutorial, using INSTEAD-OF DELETE we can override the standard action of Delete DML on a view.

Instead-of Delete trigger Example.

In this example I will again use the View VW_RebellionRider which we created earlier and have consistently used in this Instead-of trigger series so far.

Needless to say that executing DELETE DML on this view will return an error because of its non-updatable nature. Thus the only way to perform DELETE DML on this view is by using an Instead of trigger. Let’s quickly create one.

CREATE OR REPLACE TRIGGER io_delete
INSTEAD OF DELETE ON vw_RebellionRider
FOR EACH ROW
BEGIN
  DELETE FROM trainer WHERE FULL_NAME = :old.FULL_NAME;
  DELETE FROM subject WHERE SUBJECT_NAME= :old.SUBJECT_NAME;
END;
/

On successful execution this trigger will allow you to execute DELETE DML on the view.

This is a very simple example of Instead-of delete trigger intended to teach you the proper way of creating an Instead-of Delete trigger. Hope you enjoyed this quick blog.

Also go ahead and watch to participate in the giveaway on my YouTube channel. Thanks & have a great day!