Before Delete and After Delete Triggers

Before Delete and After Delete Triggers–Learn how to use Before and After Delete Triggers with Examples.

Before Delete and After Delete Triggers

BEFORE DELETE TRIGGER

The Before Delete Trigger is fired before Delete operation performed on the table. Before Keyword access the old values before they go into the table for deletion.

Syntax

CREATE [ OR REPLACE ] TRIGGER trigger_name
BEFORE DELETE
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Example Program -to validate the restricted data before deleting data from table.

CREATE OR REPLACE TRIGGER EMPDEL_VALID_ON_EMP BEFORE
  DELETE ON EMP 
  FOR EACH ROW BEGIN
IF :OLD.EMPLOYEE_ID =9 OR :OLD.EMPLOYEE_ID =10 THEN
  RAISE_APPLICATION_ERROR(-20001,'PLEASE DONT DELETE EMPLOYEE ID WITH 9 AND 10');
END IF;

END;

Deletion Query
DELETE FROM EMP WHERE EMPLOYEE_ID=9;

Output
Error report:
SQL Error: ORA-20001: PLEASE DONT DELETE EMPLOYEE ID WITH 9 AND 10
ORA-06512: at “TRAINING593.EMPDEL_VALID_ON_EMP”, line 3
ORA-04088: error during execution of trigger ‘TRAINING593.EMPDEL_VALID_ON_EMP’

AFTER DELETE TRIGGER

The AFTER UPDATE Trigger is fired AFTER DELETE operation performed on the table.

Here is the sample Audit table

CREATE TABLE EMP_del_audit(EMPLOYEE_ID NUMBER,FIRST_NAME VARCHAR2(64 BYTE),
LAST_NAME VARCHAR2(64 BYTE),
EMAIL VARCHAR2(64 BYTE),delete_date DATE);

create or replace
TRIGGER  EMPDEL_AUDIT_ON_EMP
AFTER DELETE ON EMP 
FOR EACH ROW
BEGIN

INSERT INTO EMP_del_audit(EMPLOYEE_ID,FIRST_NAME,
LAST_NAME,
EMAIL ,delete_date)
VALUES(:OLD.EMPLOYEE_ID,:OLD.FIRST_NAME,:OLD.LAST_NAME,:OLD.EMAIL,SYSDATE);
END;

Deletion Query

DELETE FROM EMP WHERE EMPLOYEE_ID=13;

SELECT * FROM EMP_del_audit ORDER BY 1 DESC;

Output