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
Output
table ITEM_DETAILS created.
Output
table ORDER_DETAILS created.
Output
TRIGGER TRG_BEFORE_ITEM_DELETE compiled
ITEM_ID ITEM_NAME TYPE PRICE_IN_DOLLAR
1 Fidget Spinner TOYS 10
2 Radio ELECTRONICS 15
3 Toys Car TOYS 25
4 Mobile ELECTRONICS 150
ORDER_ID ITEM_ID QUANTITY ORDER_DATE STATUS
101 1 5 10-JUN-2017 COMPLETED
102 2 2 15-JUN-2017 CANCELLED
103 4 1 17-JUN-2017 PENDING
104 4 1 01-JUN-2017 COMPLETED
Output
Error report: SQL Error: ORA-20000: 1 Pending orders found for this item. First COMPLETE or CANCEL the order and then delete. ORA-06512: at “HR.TRG_BEFORE_ITEM_DELETE”, line 11 ORA-04088: error during execution of trigger ‘HR.TRG_BEFORE_ITEM_DELETE’ 20000. 00000 – “%s” *Cause: The stored procedure ‘raise_application_error’ was called which causes this error to be generated. *Action: Correct the problem as described in the error message or contact the application administrator or DBA for more information.
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
Output
table PATIENT created.
Output
table PATIENT_DETAILS created.
Output
TRIGGER TRG_DELETE_FROM_DETAILS compiled
PATIENT_ID NAME PHONE_NO
1 Devil Lal 9898989898
2 Martin Kiyosaki 9090909090
PD_ID PATIENT_ID DESEASE ADMITTED_DATE DOCTOR
101 1 FEVER 10-JUN-2016 Dr. RJ Sharma
102 1 COLD 01-DEC-2016 Dr. RJ Sharma
103 2 ARTHRITIS 01-DEC-2015 Dr. KD Verma
104 2 BACKPAIN 12-FEB-2017 Dr. KD Verma
Output
2 rows deleted.
Output
no rows selected.
Output
no rows selected.
References
  1. Syntax :- Oracle official docs
  2. PL/SQL Triggers :- Oracle official docs
It's good to share...Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Google+Email this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *