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.

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

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

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;

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;

select * from MEDICAL_BILLS_HISTORY;

References

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