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:
1 | CREATE TABLE new_employee |
2 | ( |
3 | employee_id NUMBER |
4 | ,employee_name VARCHAR2(1000) |
5 | ,creation_date DATE |
6 | ,created_by VARCHAR2(1000) |
7 | ); |
If we query the ‘new_employee’ table as:
1 | SELECT * |
2 | 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:
1 | CREATE TABLE new_employee_duplicate AS |
2 | (SELECT * |
3 | FROM new_employee |
4 | ); |
If we query the new_employee_duplicate table as:
1 | SELECT * |
2 | 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:
1 | CREATE OR REPLACE TRIGGER new_employee_trigger |
2 | BEFORE DELETE |
3 | ON new_employee |
4 | FOR EACH ROW |
5 | BEGIN |
6 | INSERT INTO new_employee_duplicate |
7 | VALUES (:old.employee_id |
8 | ,:old.employee_name |
9 | ,:old.creation_date |
10 | ,:old.created_by); |
11 | 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:
1 | INSERT INTO new_employee |
2 | VALUES |
3 | (1, 'Emp ABCD',SYSDATE,'SCOTT'); |
Once we run the above INSERT statement we can query the ‘new_employee’ as:
1 | SELECT * |
2 | 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
1 | SELECT * |
2 | 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.
1 | DELETE FROM new_employee; |
Now, if we query ‘new_employee’ table as:
1 | SELECT * |
2 | 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:
1 | SELECT * |
2 | 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.