Oracle PL/SQL After DELETE Trigger Example

Overview

This Article will help you to understand “AFTER DELETE TRIGGER statement” with After DELETE Trigger Example and it’s detailed description.

This statement specifies that Oracle will fire this trigger AFTER the DELETE operation is executed.

In real life scenarios, AFTER DELETE Trigger mostly used for purposes like (1)Insert data into history table After DELETE of the data. (2) Insert data to action event table to track DELETE events.

Restrictions on AFTER 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

Insert data

(1) Insert data into history table After DELETE of the data.

Description

In this example we have created a trigger which will insert rows into history table After each deletion on transaction table. Whenever user will DELETE data of medical_bills, the data will be inserted into medical_bills_history by Trigger for After DELETE.

First, we will create transaction table medical_bills.

Then we will create history table medical_bills_history.

Then we will create “trg_after_delete_bill” Trigger on table medical_bills.

We will insert different values in medical_bills table. Then select data from medical_bills and medical_bills_history both tables.

Then we will delete few rows of medical_bills table and will observe values of medical_bills and medical_bills_history tables.

You will notice, in history table, the deleted values are inserted.

Code


--Creating medical_bills table.

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

--Creating medical_bills table.

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

);

Output

table MEDICAL_BILLS created.


--Creating medical_bills_history table.

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

--Creating medical_bills_history table.

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

);

Output

table MEDICAL_BILLS_HISTORY created.


--Creating Trigger

CREATE OR REPLACE TRIGGER trg_after_delete_bill
AFTER DELETE
  ON medical_bills
  FOR EACH ROW
DECLARE
username varchar2(10);
  
BEGIN
	
	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;
/

--Creating Trigger

CREATE OR REPLACE TRIGGER trg_after_delete_bill

AFTER DELETE

ON medical_bills

FOR EACH ROW

DECLARE

username varchar2(10);

BEGIN

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;

Output

TRIGGER TRG_AFTER_DELETE_BILL compiled


--Inserting values

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

select * from MEDICAL_BILLS;

--Inserting values

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

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;

select * from MEDICAL_BILLS_HISTORY;

Output

no rows selected.


--deleting values.

DELETE FROM MEDICAL_BILLS WHERE bill_id = 1;

DELETE FROM MEDICAL_BILLS WHERE bill_id = 3;

--deleting values.

DELETE FROM MEDICAL_BILLS WHERE bill_id = 1;

DELETE FROM MEDICAL_BILLS WHERE bill_id = 3;

Output

1 rows deleted. 1 rows deleted.


select * from MEDICAL_BILLS;

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;

select * from MEDICAL_BILLS_HISTORY;

BILL_ID	BILL_NUMBER	PARTY_NAME	BILL_DATE	DELETED_BY	DELETED_DATE
1	BILL101	Peter Thomas	12-MAY-2016	HR	18-JUN-2017
3	BILL103	Fujit su	10-JUL-2015	HR	18-JUN-2017

Insert data to other table

(2) Insert data to action event table to track DELETE events.

Description

In this example we have created a trigger which will insert data into action event table After each deletion on trading_details table. Whenever user will DELETE data of trading_details, the data will be inserted into trading_action_event by Trigger for After DELETE.

First, we will create tables trading_details and trading_action_event.

Then we will create “trg_after_delete_trade” Trigger on table trading_details.

We will insert different values in trading_details table. Then select data from trading_details and trading_action_event both tables.

Then we will delete few rows of trading_details table and will observe values of trading_details and trading_action_event tables.

You will notice, in action event table, the deleted action is inserted with action date and action by values.

Code


--Creating trading_details table.

CREATE TABLE trading_details
(
	TRADE_ID number(10) primary key,
	STOCK_NAME varchar2(20),
	PRICE number(10),
	TRADING_DATE date,
	TRADE_TYPE varchar2(4)
);

--Creating trading_details table.

CREATE TABLE trading_details

(

TRADE_ID number(10) primary key,

STOCK_NAME varchar2(20),

PRICE number(10),

TRADING_DATE date,

TRADE_TYPE varchar2(4)

);

Output

table TRADING_DETAILS created.


--Creating trading_action_event table.

CREATE TABLE TRADING_ACTION_EVENT
(
	TRADE_ID number(10),
	ACTION varchar2(6),
	ACTION_DATE date,
	ACTION_BY varchar2(10)
);

--Creating trading_action_event table.

CREATE TABLE TRADING_ACTION_EVENT

(

TRADE_ID number(10),

ACTION varchar2(6),

ACTION_DATE date,

ACTION_BY varchar2(10)

);

Output

table TRADING_ACTION_EVENT created.


--Creating Trigger

CREATE OR REPLACE TRIGGER trg_after_delete_trade
AFTER DELETE
  ON TRADING_DETAILS
  FOR EACH ROW
DECLARE
username varchar2(10);
  
BEGIN
	
	SELECT user INTO username FROM dual;
	
  -- Insert deleted trade_id with action as DELETE with action date and action_by as current username.
  INSERT INTO TRADING_ACTION_EVENT VALUES(:OLD.TRADE_ID,'DELETE',sysdate,username);
  
END;
/

--Creating Trigger

CREATE OR REPLACE TRIGGER trg_after_delete_trade

AFTER DELETE

ON TRADING_DETAILS

FOR EACH ROW

DECLARE

username varchar2(10);

BEGIN

SELECT user INTO username FROM dual;

-- Insert deleted trade_id with action as DELETE with action date and action_by as current username.

INSERT INTO TRADING_ACTION_EVENT VALUES(:OLD.TRADE_ID,'DELETE',sysdate,username);

END;

Output

TRIGGER TRG_AFTER_DELETE_TRADE compiled


--Inserting values

alter session set nls_date_format = 'DD-MON-YYYY';

INSERT INTO TRADING_DETAILS VALUES (1001,'STCK_NAME1',200,'15-MAY-2016','BUY');

INSERT INTO TRADING_DETAILS VALUES (1002,'STCK_2',105,'10-JUN-2016','SELL');

INSERT INTO TRADING_DETAILS VALUES (1003,'STCK_3',350,'12-DEC-2016','BUY');

select * from TRADING_DETAILS;

--Inserting values

alter session set nls_date_format = 'DD-MON-YYYY';

INSERT INTO TRADING_DETAILS VALUES (1001,'STCK_NAME1',200,'15-MAY-2016','BUY');

INSERT INTO TRADING_DETAILS VALUES (1002,'STCK_2',105,'10-JUN-2016','SELL');

INSERT INTO TRADING_DETAILS VALUES (1003,'STCK_3',350,'12-DEC-2016','BUY');

select * from TRADING_DETAILS;

TRADE_ID	STOCK_NAME	PRICE	TRADING_DATE	TRADE_TYPE
1001	STCK_NAME1	200	15-MAY-2016	BUY
1002	STCK_2	105	10-JUN-2016	SELL
1003	STCK_3	350	12-DEC-2016	BUY


select * from TRADING_ACTION_EVENT;

select * from TRADING_ACTION_EVENT;

Output

no rows selected.


--deleting values.

DELETE FROM TRADING_DETAILS WHERE TRADE_ID = 1002;

DELETE FROM TRADING_DETAILS WHERE TRADE_ID = 1003;

--deleting values.

DELETE FROM TRADING_DETAILS WHERE TRADE_ID = 1002;

DELETE FROM TRADING_DETAILS WHERE TRADE_ID = 1003;

Output

1 rows deleted. 1 rows deleted.


select * from TRADING_DETAILS;

select * from TRADING_DETAILS;

TRADE_ID	STOCK_NAME	PRICE	TRADING_DATE	TRADE_TYPE
1001	STCK_NAME1	200	15-MAY-2016	BUY


select * from TRADING_ACTION_EVENT;

select * from TRADING_ACTION_EVENT;

TRADE_ID	ACTION	ACTION_DATE	ACTION_BY
1002	DELETE	18-JUN-2017	HR
1003	DELETE	18-JUN-2017	HR

References

Oracle PL/SQL Before DELETE Trigger Example
Oracle PL/SQL Before INSERT Trigger Example
Oracle PL/SQL Before UPDATE Trigger Example
Oracle PL/SQL After UPDATE Trigger Example
RENAME Trigger
DROP Trigger

Facebook

Twitter

Google+