How to create a delete trigger in Oracle

What is delete trigger
A delete trigger is a SQL block which is executed when a record is deleted from a table.
This trigger is generally utilized to create a backup of records deleted from a table.
The SQL block for trigger contains queries which insert the record(or some values from the record) deleted from a table in a backup table.

How to create a delete trigger
For the demonstration of a delete trigger, let us create the table on which the trigger will be created and a backup table which will be populated when the trigger is fired.

SQL query to create the source table is :

CREATE TABLE student (
   id number(10),
  name VARCHAR2(50) NOT NULL,
  age NUMBER(10) NOT NULL,
  classdetail VARCHAR2(200) DEFAULT NULL NULL,
  PRIMARY KEY (id)
  );

SQL query to create backup table will be

CREATE TABLE student_back (
   id number(10),
  name VARCHAR2(50) NOT NULL,
  age NUMBER(10) NOT NULL,
  classdetail VARCHAR2(200) DEFAULT NULL NULL,
  PRIMARY KEY (id)
  );

For simplicity, the table structure of both tables is exactly the same though it is not mandatory to do so.

Trigger Syntax

Syntax for creating a delete trigger is as follows

CREATE OR REPLACE TRIGGER [Trigger Name]
BEFORE DELETE ON [Table Name]
REFERENCING OLD AS [Variable name]
FOR EACH ROW
BEGIN
[SQL Statements which constitute this trigger]
END;

Explanation

Above trigger syntax can be divided and understood in following parts.
CREATE OR REPLACE TRIGGER
These are keywords which create the trigger.
These should be followed by a trigger name. Keywords OR REPLACE recreate the trigger if it already exists.

BEFORE DELETE ON
These keywords signify the type of event at which the trigger will execute.
In this case it is a trigger which will execute before any record will be deleted from the table name followed by these keywords.

REFERENCING OLD AS
This keyword assigns a handle to the record deleted from the table.
A variable name is provided after these keywords, fields of the deleted records can be accessed using this handle.
These keywords are optional and are required only when you want to access the deleted record.

FOR EACH ROW
These keywords signify that this trigger should be associated to rows of the table and executes the trigger for every row deleted.
Thus, if 5 rows are deleted then the SQL statements in the trigger will be executed 5 times.

BEGIN
This keyword marks the beginning of SQL statements which constitute this trigger.

END
This keyword is the last statement of a trigger and marks the end of SQL statements which constitute this trigger.
All SQL statements of a trigger are enclosed with in BEGIN and END keywords.

CREATE OR REPLACE TRIGGER BackupTrigger
BEFORE DELETE ON student
REFERENCING OLD AS deleted
FOR EACH ROW
BEGIN
— SQL query to insert deleted record in the back up table
INSERT INTO student_back VALUES(:deleted.id,:deleted.name,:deleted.age,:deleted.classdetail);
END;

The name of trigger is BackupTrigger which is created on table student.
This trigger refers the record deleted from the table with the keyword deleted.
This is a user defined keyword and could be any string.
The columns of record can be accessed using :deleted(name of handle) and dot(.) operator as shown in example above.
SQL statement between BEGIN and END block constitutes the purpose of this trigger and inserts a record in the student_back table with the values of the deleted record from the student table using an INSERT SQL query.

When this trigger is executed in Oracle SQL Developer, following success message is displayed

Let’s tweak in

1. Keywords OR REPLACE are optional and are used to re-create the trigger if it has already been created.
   In the absence of these keywords, if you try to create a trigger if it already exists then an error of the form ORA-04081: trigger ‘BACKUPTRIGGER’ already exists is raised.
2. In order to remove trigger, DROP TRIGGER statement followed by the name of trigger is used.
3. Even if a trigger has compilation errors, it is created but gives errors when the condition for that trigger is raised such as when a record is deleted in case of a DELETE trigger.
4. Just as we can reference the old record with OLD keyword, new record can also be referenced using NEW keyword.
   This is utilized in case of INSERT triggers.