Oracle PL/SQL After DELETE Trigger Example
Overview
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
0 1 2 3 4 5 6 7 8 9 10 11 12 |
--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
0 1 2 3 4 5 6 7 8 9 10 11 12 |
--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
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
--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
0 1 2 3 4 5 6 7 8 9 10 |
--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 |
0 1 2 |
select * from MEDICAL_BILLS_HISTORY; |
Output
0 1 2 3 4 5 6 |
--deleting values. DELETE FROM MEDICAL_BILLS WHERE bill_id = 1; DELETE FROM MEDICAL_BILLS WHERE bill_id = 3; |
Output
0 1 2 |
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 |
0 1 2 |
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
0 1 2 3 4 5 6 7 8 9 10 11 |
--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
0 1 2 3 4 5 6 7 8 9 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
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
--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
0 1 2 3 4 5 6 7 8 9 10 11 12 |
--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 |
0 1 2 |
select * from TRADING_ACTION_EVENT; |
Output
0 1 2 3 4 5 6 |
--deleting values. DELETE FROM TRADING_DETAILS WHERE TRADE_ID = 1002; DELETE FROM TRADING_DETAILS WHERE TRADE_ID = 1003; |
Output
0 1 2 |
select * from TRADING_DETAILS; |
TRADE_ID | STOCK_NAME | PRICE | TRADING_DATE | TRADE_TYPE |
1001 | STCK_NAME1 | 200 | 15-MAY-2016 | BUY |
0 1 2 |
select * from TRADING_ACTION_EVENT; |
TRADE_ID | ACTION | ACTION_DATE | ACTION_BY |
1002 | DELETE | 18-JUN-2017 | HR |
1003 | DELETE | 18-JUN-2017 | HR |