Using BEFORE DELETE Trigger in Oracle

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

Description

A BEFORE DELETE Trigger skill that Oracle will furnace this trigger earlier than 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 approves you to re-create the set off is it already exists so that you can alternate the set off definition besides issuing a DROP TRIGGER statement. trigger_name The name of the set off to create. BEFORE DELETE It suggests that the trigger will furnace before the DELETE operation is executed. table_name The identify of the table that the trigger is created on.

Restrictions

You can now not create a BEFORE set off on a view. You can update the :NEW values. You can not replace the :OLD values.

Note

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

Example

Let’s seem to be at an instance of how to create an BEFORE DELETE trigger the usage of 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 announcement to create an BEFORE DELETE trigger as follows:

TIP: When using SQLPlus, you need to enter shrink on a new line after the trigger. Otherwise, the script may not 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;

/