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:

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.

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;

select * from ORDER_DETAILS;

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;

select * from PATIENT_DETAILS;

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;

select * from PATIENT_DETAILS;

References

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