Oracle/PLSQL: BEFORE DELETE Trigger
Learn how to create a BEFORE DELETE Trigger in Oracle with syntax and examples.
A BEFORE DELETE Trigger means that Oracle will fire this trigger before the DELETE operation is executed.
The syntax to create a BEFORE DELETE Trigger in Oracle/PLSQL 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;
Parameters or Arguments
OR REPLACE is 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.
trigger_name is the name of the trigger to create.
BEFORE DELETE indicates that the trigger will fire before the DELETE operation is executed.
table_name is the name of the table that the trigger is created on.
- You can not create a BEFORE trigger on a view.
- You can update the :NEW values.
- You can not update the :OLD values.
Let's look at an example of how to create an BEFORE DELETE trigger 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 could then use the CREATE TRIGGER statement to create an BEFORE DELETE trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_delete BEFORE 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;