BEFORE DELETE TRIGGER in Oracle PLSQL

Oracle PLSQL BEFORE DELETE TRIGGER means that the trigger will get executed or triggered just before a DELETE operation is performed on the table for which the trigger is written.

Oracle PLSQL syntax to create a BEFORE DELETE TRIGGER is:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE DELETE
ON table_name
[FOR EACH ROW]
DECLARE
–variable declaration section of code
BEGIN
–code to be triggered
EXCEPTION
WHEN..
–exception handling code
END;

Here in the above Oracle PLSQL trigger syntax the ‘trigger_name’ is the name given to trigger.

---

Some points to remember about PLSQL BEFORE DELETE TRIGGER are:

We cannot create a BEFORE DELETE TRIGGER on an Oracle PLSQL VIEW.

We can update :NEW values with BEFORE DELETE TRIGGER.

We cannot update the :OLD values using BEFORE DELETE TRIGGER.

---

Example of Oracle PLSQL BEFORE DELETE TRIGGER

Suppose we create a table named ‘new_employee’ using the script below:

CREATE TABLE new_employee
(
  employee_id    NUMBER
  ,employee_name VARCHAR2(1000)
  ,creation_date DATE
  ,created_by    VARCHAR2(1000)
);

If we query the ‘new_employee’ table as:

SELECT *
FROM new_employee;

We will get the following output:

EMPLOYEE_ID

EMPLOYEE_NAME

CREATION_DATE

CREATED_BY

Also we create a ‘new_employee_duplicate’ table using the script as:

CREATE TABLE new_employee_duplicate AS
        (SELECT *
         FROM new_employee
        );

If we query the new_employee_duplicate table as:

SELECT *
FROM new_employee_duplicate;

We will get the following result:

EMPLOYEE_ID

EMPLOYEE_NAME

CREATION_DATE

CREATED_BY

At this moment we don’t have any data in the ‘new_employee’ and table ‘new_employee_duplicate’ table.

---

Creating an Oracle PLSQL BEFORE DELETE TRIGGER

Now let’s create a before delete trigger on ‘new_employee’ table so that whenever we are deleting any employee record from the ‘new_employee’ table the same records gets stored in the ‘new_employee_duplicate’ table.

For this purpose we can create a PLSQL BEFORE DELETE TRIGGER as:

CREATE OR REPLACE TRIGGER new_employee_trigger
BEFORE DELETE
ON new_employee
FOR EACH ROW
BEGIN
  INSERT INTO new_employee_duplicate
  VALUES (:old.employee_id
          ,:old.employee_name
          ,:old.creation_date
          ,:old.created_by);
END;

Here we have created a PLSQL BEFORE DELETE TRIGGER named ‘new_employee_trigger’ which will insert a record in the new_employee_duplicate’ table just before a record from the ‘new_employee’ table is deleted.

---

Let’s see PLSQL BEFORE DELETE TRIGGER in action.

Let’s add a row in ‘new_employee’ table as:

INSERT INTO new_employee
VALUES
(1, 'Emp ABCD',SYSDATE,'SCOTT');

Once we run the above INSERT statement we can query the ‘new_employee’ as:

SELECT *
FROM new_employee;

We will get the following result:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY
1	Emp ABCD	12/14/2012 4:38:32 PM	SCOTT

At this moment if we query the ‘new_employee_duplicate’ table as

SELECT *
FROM new_employee_duplicate;

We will get the following result:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY

Now, let’s delete the employee record from the ‘new_employee’ table.

DELETE FROM new_employee;

Now, if we query ‘new_employee’ table as:

SELECT *
FROM new_employee;

We will get the following output:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY

And if we query the ‘new_employee_duplicate’ table as:

SELECT *
FROM new_employee_duplicate;

We will get the following result:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY
1	Emp ABCD	12/14/2012 4:38:32 PM	SCOTT

Here we can see that while performing a delete operation on ‘new_employee’ table a record automatically gets inserted in the ‘new_employee_duplicate’ table because of BEFORE DELETE TRIGGER.

Please note that the values in CREATION_DATE and CREATED_BY columns may be different for you as they depend on system date and logged in user.

---