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
- We can not create this trigger on VIEW.
- We can not update :OLD or :NEW value.
- 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
0 1 2 3 4 5 6 7 8 9 10 |
--Creating ITEM_DETAILS table. CREATE TABLE ITEM_DETAILS ( ITEM_ID number(10) primary key, ITEM_NAME varchar2(30), TYPE varchar2(50), PRICE_IN_DOLLAR number(10) ); |
Output
0 1 2 3 4 5 6 7 8 9 10 11 |
--Creating ORDER_DETAILS table. CREATE TABLE ORDER_DETAILS ( ORDER_ID number(10) primary key, ITEM_ID number(10), QUANTITY number(5), ORDER_DATE date, STATUS varchar2(20) ); |
Output
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
--Creating Trigger CREATE OR REPLACE TRIGGER trg_before_item_delete BEFORE DELETE on item_details FOR EACH ROW DECLARE pending_orders number; BEGIN pending_orders := 0; -- Find pending orders SELECT count(1) INTO pending_orders FROM order_Details WHERE item_id = :OLD.item_id AND STATUS = 'PENDING'; -- Check whether any pending order exists or not IF (pending_orders > 0) THEN RAISE_APPLICATION_ERROR(-20000,pending_orders||' Pending orders found for this item. First COMPLETE or CANCEL the order and then delete.'); END IF; END; / |
Output
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
--Inserting values INSERT INTO ITEM_DETAILS VALUES (1,'Fidget Spinner','TOYS',10); INSERT INTO ITEM_DETAILS VALUES (2,'Radio','ELECTRONICS',15); INSERT INTO ITEM_DETAILS VALUES (3,'Toys Car','TOYS',25); INSERT INTO ITEM_DETAILS VALUES (4,'Mobile','ELECTRONICS',150); alter session set nls_date_format = 'DD-MON-YYYY'; INSERT INTO ORDER_DETAILS VALUES (101,1,5,'10-JUN-2017','COMPLETED'); INSERT INTO ORDER_DETAILS VALUES (102,2,2,'15-JUN-2017','CANCELLED'); INSERT INTO ORDER_DETAILS VALUES (103,4,1,'17-JUN-2017','PENDING'); INSERT INTO ORDER_DETAILS VALUES (104,4,1,'01-JUN-2017','COMPLETED'); select * from ITEM_DETAILS; |
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 |
0 1 2 |
select * from ORDER_DETAILS; |
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 |
0 1 2 3 4 |
--delete item which have PENDING orders. DELETE FROM item_details WHERE item_id = 4; |
Output
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
0 1 2 3 4 5 6 7 8 |
--Creating PATIENT table. CREATE TABLE PATIENT( PATIENT_ID number(10) primary key, NAME varchar2(30), PHONE_NO number(12) ); |
Output
0 1 2 3 4 5 6 7 8 9 10 11 |
--Creating PATIENT_DETAILS table. CREATE TABLE PATIENT_DETAILS ( PD_ID number(10) primary key, PATIENT_ID number(10), DESEASE varchar2(50), ADMITTED_DATE date, DOCTOR varchar2(30) ); |
Output
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
--Creating Trigger CREATE OR REPLACE TRIGGER trg_delete_from_details BEFORE DELETE on patient FOR EACH ROW BEGIN -- Delete from PATIENT_DETAILS also DELETE FROM PATIENT_DETAILS PD WHERE PD.PATIENT_ID = :OLD.PATIENT_ID; END; / |
Output
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
--Inserting values alter session set nls_date_format = 'DD-MON-YYYY'; INSERT INTO PATIENT VALUES(1,'Devil Lal',9898989898); INSERT INTO PATIENT VALUES(2,'Martin Kiyosaki',9090909090); INSERT INTO PATIENT_DETAILS VALUES(101,1,'FEVER','10-JUN-2016','Dr. RJ Sharma'); INSERT INTO PATIENT_DETAILS VALUES(102,1,'COLD','01-DEC-2016','Dr. RJ Sharma'); INSERT INTO PATIENT_DETAILS VALUES(103,2,'ARTHRITIS','01-DEC-2015','Dr. KD Verma'); INSERT INTO PATIENT_DETAILS VALUES(104,2,'BACKPAIN','12-FEB-2017','Dr. KD Verma'); select * from PATIENT; |
PATIENT_ID | NAME | PHONE_NO |
1 | Devil Lal | 9898989898 |
2 | Martin Kiyosaki | 9090909090 |
0 1 2 |
select * from PATIENT_DETAILS; |
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 |
0 1 2 3 4 |
--delete items from patient table. DELETE FROM patient WHERE patient_id in (1,2); |
Output
0 1 2 |
select * from PATIENT; |
Output
0 1 2 |
select * from PATIENT_DETAILS; |