BEFORE DELETE Trigger FAQS

1. What is the difference between BEFORE DELETE and AFTER DELETE triggers in Oracle?

• BEFORE DELETE Trigger: This trigger executes before a DELETE operation is performed on a table. You can use it to validate data, prevent deletions, or perform actions like logging before the actual data is deleted.
• AFTER DELETE Trigger: This trigger executes after a DELETE operation is performed. It’s typically used when you need to act on data after it has been deleted, such as logging the deletion or cleaning up related data in other tables.

Key Difference: BEFORE DELETE allows you to block or modify the delete operation, while AFTER DELETE is only used for actions once the deletion has already occurred.

 

2. Can a BEFORE DELETE trigger prevent a DELETE operation?

Yes, a BEFORE DELETE trigger can prevent a DELETE operation by raising an error using RAISE_APPLICATION_ERROR. For example:

RAISE_APPLICATION_ERROR(-20001, 'Deletion not allowed');

If the condition in the trigger is met, it will stop the DELETE and show the custom error message.

 

3. Can I reference old values in a BEFORE DELETE trigger?

Yes, you can reference the values of the row before it is deleted using the :OLD keyword in a row-level trigger. This allows you to check the current values of the columns before the deletion.

For example:

IF :OLD.manager_flag = 'Y' THEN

    RAISE_APPLICATION_ERROR(-20001, 'Cannot delete a manager.');

END IF;

Here, :OLD.manager_flag refers to the value of manager_flag before the deletion attempt.

 

4. What is a mutating table error, and how do I avoid it?

A mutating table error occurs when a row-level trigger tries to modify or query the same table that is currently being modified by a DML operation (like DELETE, UPDATE, or INSERT). This is not allowed in Oracle.

For example, if you attempt to modify the same table in a BEFORE DELETE trigger, Oracle will throw a mutating table error.

To avoid this:

• Use statement-level triggers if applicable.
• Use temporary tables or collections to store information from the table and then perform your operations.

 

5. Can a BEFORE DELETE trigger access multiple rows at once?

No, a BEFORE DELETE trigger is typically row-level and processes one row at a time. If you need to process multiple rows in one DELETE statement, you should handle that logic at the statement level (using a statement-level trigger), or you may need to execute bulk operations through PL/SQL code.

 

6. Can I perform cascading actions like DELETE or UPDATE in a BEFORE DELETE trigger?

Yes, you can perform actions like DELETE or UPDATE in a BEFORE DELETE trigger, but you must be careful:

• You can't modify the table being triggered directly due to the mutating table error.
• For cascading DELETE operations, it's typically better to use foreign keys with ON DELETE CASCADE or handle it in a stored procedure or AFTER DELETE trigger.

 

7. Can I use a BEFORE DELETE trigger to perform an audit or log deletions?

Yes, a BEFORE DELETE trigger can be used to log information about the deletion into another table, often referred to as an audit table. For example, you can record the employee_id and employee_name in a log table before the actual deletion occurs:

CREATE OR REPLACE TRIGGER log_deletion

BEFORE DELETE

ON employees

FOR EACH ROW

BEGIN

    INSERT INTO deletion_log (employee_id, employee_name, deletion_time)

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

END;

This allows you to keep a history of the deletions before the records are permanently removed.

 

8. What happens if a BEFORE DELETE trigger raises an exception?

If a BEFORE DELETE trigger raises an exception (such as RAISE_APPLICATION_ERROR), the DELETE operation will be canceled and the exception will propagate, preventing the data from being deleted. This is useful for enforcing business rules or preventing certain rows from being deleted.

For example, if a customer has active orders and you don’t want to delete the customer, you can raise an error:

IF v_order_count > 0 THEN

    RAISE_APPLICATION_ERROR(-20002, 'Cannot delete customer with existing orders.');

END IF;

This stops the deletion if the condition is met.

 

9. Can I create a BEFORE DELETE trigger on a view?

No, you cannot create a trigger directly on a view in Oracle, because views are not "real" tables. However, if you want to create a trigger for DML operations (including DELETE) on a view, you can create instead-of triggers that intercept the DML operations and redirect them to the underlying tables.

Example (Instead of Trigger on View):

CREATE OR REPLACE TRIGGER delete_from_view

INSTEAD OF DELETE

ON my_view

BEGIN

    DELETE FROM base_table WHERE id = :OLD.id;

END;

This allows you to perform a delete on a view by handling it on the underlying base table.

 

10. Can a BEFORE DELETE trigger be defined on multiple tables?

No, a trigger can only be created for one specific table. However, you can create separate triggers on different tables for similar purposes (like validation or logging) or manage multiple tables with a PL/SQL procedure that can be called within the trigger.

 

11. Can a BEFORE DELETE trigger execute DML statements on other tables?

Yes, a BEFORE DELETE trigger can execute DML statements on other tables, but with caution. For example, you might want to update another related table before the delete operation proceeds. However, be mindful of transaction consistency and avoid unnecessary locking or long-running operations that might affect performance.

Example:

CREATE OR REPLACE TRIGGER prevent_delete_update_related_table

BEFORE DELETE

ON orders

FOR EACH ROW

BEGIN

    -- Example: Update the related customer status before deleting an order

    UPDATE customers

    SET status = 'Inactive'

    WHERE customer_id = :OLD.customer_id;

END;

 

12. Can I have multiple BEFORE DELETE triggers on the same table?

No, you can only have one BEFORE DELETE trigger per table. If you need to execute multiple actions before a DELETE, you should combine your logic into one trigger or use stored procedures for modularity.

However, if different actions need to be performed at different stages, you can consider using AFTER DELETE triggers in addition to BEFORE DELETE triggers to separate logic based on when it should occur.