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:

  1. Restrict invalid DELETE operation.
  2. 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.

ITEM_DETAILS

CREATE TABLE ITEM_DETAILS
(
	ITEM_ID number(10) primary key,
	ITEM_NAME varchar2(30),
	TYPE varchar2(50),
	PRICE_IN_DOLLAR number(10)
);
ORDER_DETAILS

CREATE TABLE ORDER_DETAILS
(
	ORDER_ID number(10) primary key,
	ITEM_ID number(10),
	QUANTITY number(5),
	ORDER_DATE date,
	STATUS varchar2(20)
);
trg_before_item_delete

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.

PATIENT

CREATE TABLE PATIENT
(
	PATIENT_ID number(10) primary key,
	NAME varchar2(30),
	PHONE_NO number(12)
);
PATIENT_DETAILS

CREATE TABLE PATIENT_DETAILS
(
	PD_ID number(10) primary key,
	PATIENT_ID number(10),
	DISEASE varchar2(50),
	ADMITTED_DATE date,
	DOCTOR varchar2(30)
);
trg_delete_from_details

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

References

  1. Creating a DML Trigger: Examples
  2. PL/SQL Triggers :- Oracle official docs
  3. Oracle PL/SQL – Before UPDATE Trigger example