Oracle/PLSQL: BEFORE DELETE Trigger
A BEFORE DELETE Trigger means that Oracle will fire this trigger before the DELETE operation is executed.
The syntax for an BEFORE DELETE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
BEFORE DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
trigger_name is the name of the trigger to create.
Restrictions:
- You can not create a BEFORE trigger on a view.
- You can update the :NEW values.
- You can not update the :OLD values.
For example:
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 could then create a BEFORE DELETE trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_delete
BEFORE DELETE
ON orders
FOR EACH ROWDECLARE
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;