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
  1. We can not create this trigger on VIEW.
  2. We can not update :OLD or :NEW value.
  3. 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
Output
table MEDICAL_BILLS created.
Output
table MEDICAL_BILLS_HISTORY created.
Output
TRIGGER TRG_AFTER_DELETE_BILL compiled
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
Output
no rows selected.
Output
1 rows deleted. 1 rows deleted.
BILL_ID BILL_NUMBER PARTY_NAME BILL_DATE CREATED_BY CREATED_DATE
2 BILL102 Jemes Petil 10-JUN-2016 HR 18-JUN-2017
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
Output
table TRADING_DETAILS created.
Output
table TRADING_ACTION_EVENT created.
Output
TRIGGER TRG_AFTER_DELETE_TRADE compiled
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
Output
no rows selected.
Output
1 rows deleted. 1 rows deleted.
TRADE_ID STOCK_NAME PRICE TRADING_DATE TRADE_TYPE
1001 STCK_NAME1 200 15-MAY-2016 BUY
TRADE_ID ACTION ACTION_DATE ACTION_BY
1002 DELETE 18-JUN-2017 HR
1003 DELETE 18-JUN-2017 HR
References
  1. Syntax :- Oracle official docs
  2. PL/SQL Triggers :- Oracle official docs
It's good to share...Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Google+Email this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *