Trigger before delete in Oracle.

Preamble

In this tutorial you will learn how to create a trigger BEFORE DELETE (before deleting) in Oracle with syntax and examples.

Oracle executes (excites) the BEFORE DELETE trigger before executing the DELETE operator.

Syntax of the BEFORE DELETE trigger

CREATE [ OR REPLACE ] TRIGGER name_trigger
BEFORE DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
- variable declaration
BEGIN
- trigger code
EXCEPTION
WHEN ...
- emergency handling
END;

Parameters or arguments

• Trigger_name – The name of the trigger to be created.
• BEFORE DELETE – indicates that the trigger is triggered before the DELETE operator is executed.
• table_name – The name of the table for which the trigger was created.

Restrictions

• You cannot create a trigger in views.
• You cannot update :NEW (new) values.
• You cannot update :OLD (old) values.

Example

Let’s consider an example of how to create a BEFORE DELETE trigger using the CREATE TRIGGER construction.
If you have created a table of the following structure:

CREATE TABLE orders
( order_id number(5),
number(4),
cost_per_item number(6,2),
total_cost number(8,2)
);

Then we will create a 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 the person who's doing DELETE in the table.
SELECT user INTO v_username
FROM dual;
-- Insert a row in the audit table
INSERT INTO orders_audit
( order_id,
Ah,
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;

About Enteros

​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​