BEFORE DELETE Trigger

This Oracle tutorial explains how to create a BEFORE DELETE Trigger in Oracle with syntax and examples.

Description

A BEFORE DELETE Trigger means that Oracle will fire this trigger before the DELETE operation is executed.

Syntax

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

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

The name of the trigger to create.

BEFORE DELETE

It indicates that the trigger will fire before the DELETE operation is executed.

table_name

The name of the table that the trigger is created on.

Restrictions

• You can not create a BEFORE trigger on a view.
• You can update the :NEW values.
• You can not update the :OLD values.

Note

• See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE INSERT, and BEFORE UPDATE triggers.
• See also how to drop a trigger.

Example

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:

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_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;

 

/