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

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: