This Oracle tutorial explains how to create an AFTER DELETE Trigger in Oracle with syntax and examples.
Description
An AFTER DELETE Trigger capacity that Oracle will furnace this trigger after the DELETE operation is executed.
Syntax
The syntax to create an AFTER DELETE Trigger in Oracle/PLSQL is:
CREATE [ OR REPLACE ] TRIGGER trigger_name
AFTER DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;Parameters or Arguments
OR REPLACE Optional. If specified, it lets in you to re-create the set off is it already exists so that you can trade the set off definition barring issuing a DROP TRIGGER statement. trigger_name The identify of the set off to create. AFTER DELETE It shows that the trigger will fireplace after the DELETE operation is executed. table_name The name of the desk that the trigger is created on.
Restrictions
You can not create an AFTER set off on a view. You can not update the :NEW values. You can not update the :OLD values.
Note
See additionally how to create AFTER INSERT, AFTER UPDATE, BEFORE DELETE, BEFORE INSERT, and BEFORE UPDATE triggers. See additionally how to drop a trigger.
Example
Let’s seem at an instance of how to create an AFTER DELETE set off using the CREATE TRIGGER statement.
If you had a table created as follows:
CREATE TABLE orders
( order_id number(5),
quantity number(4),
cost_per_item number(6,2),
total_cost number(8,2)
);We may want to then use the CREATE TRIGGER declaration to create an AFTER DELETE trigger as follows:
TIP: When the use of SQLPlus, you want to enter reduce on a new line after the trigger. Otherwise, the script may not execute.
CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
delete_date,
deleted_by)
VALUES
( :old.order_id,
:old.quantity,
:old.cost_per_item,
:old.total_cost,
sysdate,
v_username );
END;
/