Oracle PL/SQL – Before DELETE Trigger example
This article shows you how to use BEFORE DELETE TRIGGER
, it will fire before the delete operation is executed. In real life scenarios, it is mostly used for purposes like:
- Restrict invalid DELETE operation.
- Delete data from another table.
1. Restrict invalid DELETE operation
In this example, We have two tables item_details
and order_details
. The table order_details
contains values of purchase orders of items from item_details
table. Now whenever the 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 is found, then we will not allow the item to be deleted and will raise application error from BEFORE DELETE TRIGGER
to restrict delete operation on item_details
1.1 Create tables and trigger.
CREATE TABLE ITEM_DETAILS
(
ITEM_ID number(10) primary key,
ITEM_NAME varchar2(30),
TYPE varchar2(50),
PRICE_IN_DOLLAR number(10)
);
CREATE TABLE ORDER_DETAILS
(
ORDER_ID number(10) primary key,
ITEM_ID number(10),
QUANTITY number(5),
ORDER_DATE date,
STATUS varchar2(20)
);
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;
1.2 Insert data for testing.
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');
1.3 Display the data.
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 |
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 |
1.4 Delete item which have PENDING orders.
DELETE FROM item_details WHERE item_id = 4;
-- output
Error report -
ORA-20000: 1 pending orders found for this item. First COMPLETE or CANCEL the order and then delete.
ORA-06512: at "SYSTEM.TRG_BEFORE_ITEM_DELETE", line 11
ORA-04088: error during execution of trigger 'SYSTEM.TRG_BEFORE_ITEM_DELETE'
2. DELETE from another table
In this example, We have two tables patient
and patient_details
. The patient
contains basic details while patient_details
contains values of a patient such as disease, doctor name etc.
Now whenever the 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 a patient is deleted. So here we will delete data by BEFORE DELETE TRIGGER
on the patient table.
2.1 Create tables and trigger.
CREATE TABLE PATIENT
(
PATIENT_ID number(10) primary key,
NAME varchar2(30),
PHONE_NO number(12)
);
CREATE TABLE PATIENT_DETAILS
(
PD_ID number(10) primary key,
PATIENT_ID number(10),
DISEASE varchar2(50),
ADMITTED_DATE date,
DOCTOR varchar2(30)
);
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;
2.2 Insert data for testing.
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');
2.3 Display the data.
select * from PATIENT;
PATIENT_ID | NAME | PHONE_NO |
---|---|---|
1 | Devil Lal | 9898989898 |
2 | Martin Kiyosaki | 9090909090 |
select * from PATIENT_DETAILS;
PD_ID | PATIENT_ID | DISEASE | 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 |
2.4 Delete items from patient
table. Review the patient_detail
table, the related data will be deleted by trigger automatically.
DELETE FROM patient WHERE patient_id = 2;
-- output
-- 1 row deleted.
select * from PATIENT;
PATIENT_ID | NAME | PHONE_NO |
---|---|---|
1 | Devil Lal | 9898989898 |
select * from PATIENT_DETAILS;
PD_ID | PATIENT_ID | DISEASE | ADMITTED_DATE | DOCTOR |
---|---|---|---|---|
101 | 1 | FEVER | 10-JUN-2016 | Dr. RJ Sharma |
102 | 1 | COLD | 01-DEC-2016 | Dr. RJ Sharma |
Hi, i do the below query:
CREATE OR REPLACE TRIGGER RIB
BEFORE INSERT OR UPDATE ON ap_supplier_sites_all
FOR EACH ROW
DECLARE
totover number(10);
BEGIN
totover := 0;
SELECT count(1) INTO totover FROM ap_supplier_sites_all WHERE ap_supplier_sites_all.attribute4 = :NEW.attribute4;
— Check whether any pending order exists or not
IF (totover > 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;
i want to avoid insert or update if the attribute4 exists in the table.
BR,
Thanks for the two examples, this clarifies many doubts and I didn’t know about the procedure RAISE_APPLICATION_ERROR, now i’m using it everywhere