Oracle PL/SQL Before DELETE Trigger Example

Overview

This Article will help you to understand “BEFORE DELETE TRIGGER statement” with Before DELETE Trigger Example and it’s detailed description.

This statement specifies that Oracle will fire this trigger BEFORE the DELETE operation is executed.

In real life scenarios, BEFORE DELETE Trigger mostly used for purposes like (1) Restrict invalid DELETE operation. (2) Delete data from other table.

Restrictions on BEFORE DELETE Trigger

1. We can not create this trigger on VIEW.
2. We can not update :OLD or :NEW value.
3. Use of :NEW value will give null as we don’t have any new value at the time of deletion.

Examples

Restrict DELETE

(1) Restrict invalid DELETE operation.

Description

In this example, We have two tables item_details and order_details. order_details contains values of purchase orders of items from item_details table. Now whenever user wants to delete item from item_details, We need to check whether any PENDING order exists for that item or not. If any PENDING order found, then we will not allow item to be deleted and will raise application error from BEFORE DELETE TRIGGER to restrict delete operation on item_details.

First we will create item_details and order_details table.

Then create BEFORE DELETE TRIGGER “trg_before_item_delete” on item_details table.

Insert few values in both table and observe the inserted data.

Delete row from item_details which have PENDING order and observe the error raised by trigger.

Code

DELETE from other table

(2) Delete data from other table.

Description

In this example, We have two tables patient and patient_details. patient containts basic details while patient_details contains values of patient such as desease, doctor name etc. Now whenever user wants to delete data from patient, We need to delete data from patient_details also as we don’t require it any more after patient deletion.

So here we will delete data by BEFORE DELETE TRIGGER on patient table.

First we will create patient table and patient_details table.

Then we will create BEFORE DELETE TRIGGER “trg_delete_from_details” on patient table.

We will insert few values in both tables and observe the values.

Then we will delete data from patient table only and observe values from both table.

Code