This Oracle tutorial explains how to create an AFTER DELETE Trigger in Oracle with syntax and examples.
An AFTER DELETE Trigger means that Oracle will fire this trigger after the DELETE operation is executed.
The syntax to create an AFTER DELETE Trigger in Oracle/PLSQL is:
CREATE [ OR REPLACE ] TRIGGER trigger_name
[ FOR EACH ROW ]
-- variable declarations
-- trigger code
-- exception handling
Parameters or Arguments
Optional. If specified, it allows you to re-create the trigger is it already exists so that you can change the trigger definition without issuing a DROP TRIGGER statement.
The name of the trigger to create.
It indicates that the trigger will fire after the DELETE operation is executed.
The name of the table that the trigger is created on.
- You can not create an AFTER trigger on a view.
- You can not update the :NEW values.
- You can not update the :OLD values.
- See also how to create AFTER INSERT, AFTER UPDATE, BEFORE DELETE, BEFORE INSERT, and BEFORE UPDATE triggers.
- See also how to drop a trigger.
Let's look at an example of how to create an AFTER DELETE trigger using the CREATE TRIGGER statement.
If you had a table created as follows:
CREATE TABLE orders
( order_id number(5),
We could then use the CREATE TRIGGER statement to create an AFTER DELETE trigger as follows:
TIP: When using SQLPlus, you need to enter slash on a new line after the trigger. Otherwise, the script won't execute.
CREATE OR REPLACE TRIGGER orders_after_delete
FOR EACH ROW
-- Find username of person performing the DELETE on the table
SELECT user INTO v_username
-- Insert record into audit table
INSERT INTO orders_audit