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


--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)
);

--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

table ITEM_DETAILS created.


--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)
);

--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

table ORDER_DETAILS created.


--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;
/

--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

TRIGGER TRG_BEFORE_ITEM_DELETE compiled


--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;

--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


select * from ORDER_DETAILS;

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


--delete item which have PENDING orders.

DELETE FROM item_details WHERE item_id = 4;

--delete item which have PENDING orders.

DELETE FROM item_details WHERE item_id = 4;

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


--Creating PATIENT table.

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

--Creating PATIENT table.

CREATE TABLE PATIENT(

PATIENT_ID number(10) primary key,

NAME varchar2(30),

PHONE_NO number(12)

);

Output

table PATIENT created.


--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)
);

--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

table PATIENT_DETAILS created.


--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;
/

--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

TRIGGER TRG_DELETE_FROM_DETAILS compiled


--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;

--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


select * from PATIENT_DETAILS;

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


--delete items from patient table.

DELETE FROM patient WHERE patient_id in (1,2);

--delete items from patient table.

DELETE FROM patient WHERE patient_id in (1,2);

Output

2 rows deleted.


select * from PATIENT;

select * from PATIENT;

Output

no rows selected.


select * from PATIENT_DETAILS;

select * from PATIENT_DETAILS;

Output

no rows selected.

References

Assigning HTTP Methods in RESTful Web Services
LeetCode - LRU Cache - 30Days Challenge
Default Response Codes in RESTful Web Services
LeetCode - Kth Smallest Element in a BST
Method Reference in Java 8
Interactive Docker Image Creation

Facebook

Twitter