• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / plsql / BEFORE DELETE TRIGGER in Oracle PLSQL

BEFORE DELETE TRIGGER in Oracle PLSQL

December 14, 2012 by techhoneyadmin

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.


Filed Under: plsql Tagged With: Before delete trigger in oracle plsql, beforedeletetriggerplsql, CREATETRIGGERSPLSQL, how to create Before delete trigger in oracle plsql, what is Before delete trigger in oracle plsql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in