AFTER DELETE TRIGGER in Oracle PLSQL

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

Oracle PLSQL syntax to create a AFTER DELETE TRIGGER is:

CREATE OR REPLACE TRIGGER trigger_name
AFTER 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 AFTER DELETE TRIGGER are:

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

We cannot update :NEW values with AFTER DELETE TRIGGER.

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

Example of Oracle PLSQL AFTER DELETE TRIGGER

Suppose we have a table named ‘employee’ as shown below:

Let’s create a new table named ‘employee_count’ using the script as:

CREATE TABLE employee_count AS
  (no_of_employees   NUMBER
   ,department_name VARCHAR2(100));

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

Creating an Oracle PLSQL AFTER DELETE TRIGGER

Now let’s create an after delete trigger on ‘employee’ table so that whenever we are deleting any employee record from the ‘employee’ table (say ‘Sales’ department )the new count of employees in sales department is stored in ‘employee_count’ table with the department name.

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

CREATE OR REPLACE TRIGGER employee_count_trigger
AFTER DELETE
ON employee
DECLARE
v_count_employees NUMBER;
BEGIN
  SELECT count(*) INTO v_count_employees
  FROM employee
  WHERE department = 'Sales';
  INSERT INTO employee_count
  VALUES (v_count_employees
          ,'Sales');
END;

Here we have created a PLSQL AFTER DELETE TRIGGER named ‘employee_count_trigger’ which will insert a record in the ‘employee_count’ table just after a record from the ‘employee’ table is deleted.

Let’s see PLSQL AFTER DELETE TRIGGER in action.

Let’s delete a row from the ‘employee’ table as:

DELETE FROM employee
WHERE employee_id = 107;

Once we run the above DELETE statement we can query the ‘employee’ as:

SELECT *
FROM employee;

We will get the following result:

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

SELECT *
FROM employee_count;

We will get the following result:

Here we can see that while performing a delete operation on ‘employee’ table a record automatically gets inserted in the ‘employee_count’ table because of AFTER 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.