Oracle PL/SQL – After DELETE Trigger example

This article shows you how to use AFTER DELETE TRIGGER, it will fire after the delete operation is executed. In real life scenarios, it is mostly used for purposes like:

  1. Auditing or logging

1. After DELETE Trigger

In this example, if the user deleted a row of medical_bills, the deleted row will be inserted into medical_bills_history by after delete trigger on table medical_bills

1.1 Create tables and trigger.

medical_bills

CREATE TABLE medical_bills
(
    BILL_ID number(10) primary key,
    BILL_NUMBER varchar2(20),
    PARTY_NAME varchar2(50),
    BILL_DATE date,
    CREATED_BY varchar2(20),
    CREATED_DATE date
);
medical_bills_history

CREATE TABLE medical_bills_history
(
    BILL_ID number(10),
    BILL_NUMBER varchar2(20),
    PARTY_NAME varchar2(50),
    BILL_DATE date,
    DELETED_BY varchar2(20),
    DELETED_DATE date
);

trg_after_delete_bill

CREATE OR REPLACE TRIGGER trg_after_delete_bill
AFTER DELETE
  ON medical_bills
  FOR EACH ROW
DECLARE
username varchar2(10);
  
BEGIN

  -- current login user, in this example, system
  SELECT user INTO username FROM dual;
	
  -- Insert OLD values in history table with username of user performing delete and sysdate as deleted_Date.
  INSERT INTO medical_bills_history VALUES(:OLD.BILL_ID,
           :OLD.BILL_NUMBER,:OLD.PARTY_NAME,:OLD.BILL_DATE,username,sysdate);
  
END;

1.2 Insert data for testing.


INSERT INTO MEDICAL_BILLS values (1,'BILL101','Peter Thomas','12-MAY-2016','HR',sysdate);

INSERT INTO MEDICAL_BILLS values (2,'BILL102','Jemes Petil','10-JUN-2016','HR',sysdate);

INSERT INTO MEDICAL_BILLS values (3,'BILL103','Fujit su','10-JUL-2015','HR',sysdate);

1.3 Display the data.


select * from MEDICAL_BILLS;
BILL_ID BILL_NUMBER PARTY_NAME BILL_DATE CREATED_BY CREATED_DATE
1 BILL101 Peter Thomas 12-MAY-2016 HR 18-JUN-2017
2 BILL102 Jemes Petil 10-JUN-2016 HR 18-JUN-2017
3 BILL103 Fujit su 10-JUL-2015 HR 18-JUN-2017

select * from MEDICAL_BILLS_HISTORY;

-- no result

1.4 Delete some rows from table MEDICAL_BILLS


DELETE FROM MEDICAL_BILLS WHERE bill_id = 1;

DELETE FROM MEDICAL_BILLS WHERE bill_id = 3;

1.5 Display the data again.


select * from MEDICAL_BILLS;
BILL_ID BILL_NUMBER PARTY_NAME BILL_DATE CREATED_BY CREATED_DATE
2 BILL102 Jemes Petil 10-JUN-2016 HR 18-JUN-2017

select * from MEDICAL_BILLS_HISTORY;
BILL_ID BILL_NUMBER PARTY_NAME BILL_DATE CREATED_BY CREATED_DATE
1 BILL101 Peter Thomas 12-MAY-2016 SYSTEM 18-JUN-2017
3 BILL103 Fujit su 10-JUL-2015 SYSTEM 18-JUN-2017

References

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

About the Author

author image
Dhaval Dadhaniya
Dhaval Dadhaniya is a software engineer by profession and reader/writter by passion. He is working in a well reputed MNC as an applications Developer with 5 years of experience. He have good understanding and knowledge of Java, Database, Spring, Hibernate and exploring other technologies related to Software development. Befriend him on Facebook

Comments

avatar