How to create a back up of deleted record using trigger in Oracle / How to create a delete trigger in Oracle

What is a trigger ?

A trigger in database terms is a block containing SQL statements which is automatically executed when some condition occurs. The conditions could be when a record is inserted, deleted, updated, a table is created, deleted, a constraint is created, deleted or updated. A trigger is of two types : Before and After. Before trigger executes the SQL block before the condition of trigger is met while After trigger executes the SQL block after the condition of trigger is met.

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

CREATE OR REPLACE TRIGGER : These are keywords which create the trigger. These should be followed by a trigger name. Keywords OR REPLACE re=create 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.

Replacing values in the above syntax as per our requirement, the trigger syntax becomes

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

TRIGGER BACKUPTRIGGER compiled

 

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.

Mark Your Impression

Close Menu