AFTER DELETE Trigger Notes

An AFTER DELETE trigger in Oracle is used to execute a set of instructions after a DELETE operation has been performed on a table. This type of trigger is typically used for tasks that should only occur once the data has been deleted, such as logging, auditing, or performing cleanup actions on related tables.

Purpose of an AFTER DELETE Trigger

The AFTER DELETE trigger provides an opportunity to:

·        Log or audit the deletion.

·        Perform cascading operations or clean up related records in other tables.

·        Send notifications (e.g., email alerts or messages to users).

·        Perform referential integrity checks that depend on the successful deletion of data.

Unlike a BEFORE DELETE trigger, which can prevent a delete operation, the AFTER DELETE trigger operates only after the deletion has occurred. It is commonly used for logging, sending notifications, or handling business processes that depend on the successful deletion.

Basic Syntax of an AFTER DELETE Trigger

The basic syntax for creating an AFTER DELETE trigger is as follows:

CREATE OR REPLACE TRIGGER trigger_name

AFTER DELETE

ON table_name

[FOR EACH ROW]

DECLARE

    -- Declare variables, if needed

BEGIN

    -- Trigger body (your PL/SQL code here)

    -- Actions to be performed after the delete

END;

·        trigger_name: The name of the trigger.

·        AFTER DELETE: Specifies that this trigger will be executed after a DELETE operation on the specified table.

·        ON table_name: The table on which the trigger is created.

·        FOR EACH ROW: Indicates that the trigger will execute for each row that is deleted. If omitted, the trigger is statement-level (one execution for the entire DELETE statement, even if multiple rows are deleted).

·        DECLARE and BEGIN...END: Used for declaring variables and writing the trigger's logic in PL/SQL.

Trigger Logic

·        :OLD: In row-level triggers, :OLD is used to access the values of the row before it was deleted. Since the row no longer exists after deletion, you can only access the values that existed prior to the DELETE operation.

·        Trigger Execution: The trigger executes only after the DELETE operation is committed, making it suitable for tasks that should occur once the data is permanently removed.

Example of an AFTER DELETE Trigger

Let’s consider a situation where we want to log the details of a record before it is deleted from the employees table.

CREATE OR REPLACE TRIGGER log_employee_deletion

AFTER DELETE

ON employees

FOR EACH ROW

DECLARE

    v_deleted_time TIMESTAMP;

BEGIN

    -- Capture the deletion time

    v_deleted_time := SYSTIMESTAMP;

    -- Log the deleted employee record into an audit table

    INSERT INTO employee_deletion_log (employee_id, employee_name, deletion_time)

    VALUES (:OLD.employee_id, :OLD.employee_name, v_deleted_time);

END;

Explanation:

·        Trigger Name: log_employee_deletion

·        Table: employees

·        Action: After an employee is deleted, the trigger inserts a record into the employee_deletion_log table, capturing the employee_id, employee_name, and the exact deletion_time.

·        :OLD.employee_id and :OLD.employee_name: These refer to the values of the employee_id and employee_name of the row that was deleted.

Common Use Cases for AFTER DELETE Triggers

1.     Auditing and Logging Deletions:

o   One of the most common uses of an AFTER DELETE trigger is to log deleted rows for auditing purposes. You can create an audit table to track changes, including deletions.

Example: Log deletions of records in an audit table to keep a historical record of who deleted what data and when.

2.     Cascading Operations:

o   You can use an AFTER DELETE trigger to handle cascading changes in other tables. For example, after deleting an order record, you might want to delete related records in the order_items table or update stock levels.

Example: Delete related records in child tables when a parent record is deleted.

3.     Sending Notifications or Alerts:

o   After a record is deleted, you might want to send an email notification or an alert to users or systems.

Example: Send an email alert when a specific record (e.g., an account) is deleted.

4.     Maintaining Referential Integrity:

o   While you can use foreign keys with ON DELETE CASCADE to handle cascading deletes, sometimes you need custom actions after deletion (e.g., recalculating statistics or updating related records).

Example: Update a summary table after a record is deleted to keep data integrity in sync.

5.     Cleaning Up Other Data:

o   In cases where the deletion of one record requires cleanup of other related data, an AFTER DELETE trigger can be used to perform these cleanup actions.

Example: Recalculate totals or averages in a summary table after a record is deleted.

Example: Deleting Related Records

Imagine you have an orders table and an order_items table. If an order is deleted, you might want to delete all the associated items from the order_items table.

CREATE OR REPLACE TRIGGER delete_order_items

AFTER DELETE

ON orders

FOR EACH ROW

BEGIN

    -- Delete the related records from the order_items table

    DELETE FROM order_items WHERE order_id = :OLD.order_id;

END;

Explanation:

·        Trigger Name: delete_order_items

·        Action: After an order is deleted from the orders table, the trigger deletes all rows from the order_items table that are related to the deleted order.

Example: Deleting Related Data with Conditional Logic

You can also implement conditional logic in an AFTER DELETE trigger to handle specific cases before performing actions.

CREATE OR REPLACE TRIGGER delete_customer_orders

AFTER DELETE

ON customers

FOR EACH ROW

BEGIN

    -- Check if the customer has pending orders before deleting

    IF :OLD.customer_status = 'ACTIVE' THEN

        DELETE FROM orders WHERE customer_id = :OLD.customer_id;

    END IF;

END;

Explanation:

·        Trigger Name: delete_customer_orders

·        Action: This trigger deletes records from the orders table related to a customer only if the customer’s status was "ACTIVE" before deletion. This ensures that you only delete orders for active customers.

Performance Considerations

1.     Trigger Overhead:

o   AFTER DELETE triggers add overhead to the DELETE operation. Since the trigger runs after the deletion is complete, it will execute regardless of whether the DELETE was successful or not. You should be mindful of the logic inside the trigger to ensure it does not introduce significant delays.

o   Multiple deletes: If many rows are being deleted in one DELETE statement, the trigger will execute once for each row (if row-level) or once for the entire DELETE statement (if statement-level).

2.     Transaction Management:

o   Since AFTER DELETE triggers are executed after the deletion, they occur within the same transaction. This means the deletion and the trigger’s actions will either both commit or both roll back. Ensure that the operations inside the trigger are designed to maintain data integrity and handle failures appropriately.

3.     Avoiding Mutating Table Errors:

o   Unlike BEFORE DELETE triggers, AFTER DELETE triggers are generally less prone to mutating table errors. This is because the deletion is already complete, and you are working with the data after the fact. However, if you attempt to modify the same table inside the trigger (e.g., delete or update rows in the same table), you could run into issues.

4.     Row-Level vs Statement-Level:

o   Row-Level Triggers: These will execute for each row that is deleted. If you delete 1000 rows, the trigger will execute 1000 times. This can be useful when the action needs to be specific to each row.

o   Statement-Level Triggers: These will execute once per DELETE statement, regardless of the number of rows deleted. This is more efficient if you only need to perform a single action for the entire DELETE statement (e.g., logging or sending a notification).