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!
