So folks, I’m not going to explain the trigger syntax here as its same for all types of triggers. I’ll start off with INSTEAD OF trigger straight away.

INSTEAD OF triggers hold a special functionality as they can be used to update views which are normally not updateable and perform insert, update, delete, merge operations on them.

Syntax:

CREATE TRIGGER trigger_name

INSTEAD OF operation

ON view_name

BEGIN

...

END;

Here:

Operation may be insert/delete/update/merge. Instead of trigger doesn’t fire before or after an event, so we don’t need to specify the BEFORE or AFTER keyword. But we need to specify the operation that the trigger fires instead of. This differentiates INSTEAD OF trigger from other triggers.

Example:

Suppose I have a business system for food delivery involving a number of db tables. For the sake of getting rid of normalization I create a view which is of course taking data from multiple tables. The view contains the driver info and delivery info:

VIEW delivery_v AS

SELECT d.delivery_id, d.delivery_end_date, a.area, dd.driver

FROM delivery_info d, driver dd, area a

WHERE d.driver_id = dd.driver_id AND a.area_id = d.area_id;

We may need this view often as it covers a lot of functionality, so we may as well need provisions to perform insert/update/delete on this view. We will need to explicitly let the database know what to do when a DML statement is issued against this view. So we will need an INSTEAD OF trigger.

Here is a simple DELETE INSTEAD OF trigger that deletes an entry if its not complete:

TRIGGER delivery_del

INSTEAD OF DELETE

ON delivery_info

BEGIN

IF :OLD.delivery_end_date IS NOT NULL

THEN

                RAISE;

END IF;

DELETE delivery_info WHERE delivery_id = :OLD.delivery_id;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE(‘entry cannot be deleted’);

END;

 

Disclaimer - Views expressed in this blog are author's own and do not necessarily represents the policies of aclnz.com