SQLite Triggers: After Delete

Triggers in SQLite are special kind of stored procedures that are automatically executed in response to certain events or actions performed on a table. The AFTER DELETE trigger is executed automatically after a row is deleted from a table. In this tutorial, we will learn how to create and use AFTER DELETE triggers in SQLite.

Syntax

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

CREATE TRIGGER trigger_name
AFTER DELETE
ON table_name
BEGIN
    -- code to be executed after delete
END;

Example

Suppose we have a table called employees that stores employee information. We want to log every time an employee is deleted from the table. We can achieve this using an AFTER DELETE trigger.

CREATE TRIGGER log_employee_delete
AFTER DELETE
ON employees
BEGIN
    INSERT INTO employee_log (id, name, deleted_at)
    VALUES (OLD.id, OLD.name, DATETIME('now'));
END;

In the example above, we create an AFTER DELETE trigger called log_employee_delete on the employees table. When a row is deleted from the employees table, the trigger is automatically executed and logs the deleted employee's information into an employee_log table.

Output

When an employee is deleted from the employees table, the log_employee_delete trigger will automatically insert a row into the employee_log table with the deleted employee's information and the date and time of deletion.

| id | name     | deleted_at                   |
|----|----------|------------------------------
| 1  | John Doe | 2021-12-01 14:42:00.000000  |

Explanation

In the example above, we create an AFTER DELETE trigger called log_employee_delete on the employees table. When a row is deleted from the employees table, the trigger is automatically executed.

The OLD keyword refers to the deleted row, and we use it to insert the deleted employee's information (id, name) and the date and time of deletion into the employee_log table.

Use

A common use case for AFTER DELETE triggers is to log deleted rows into an audit table. They can also be used to enforce referential integrity and to perform cascading deletes.

Important Points

• AFTER DELETE triggers are executed automatically after a row is deleted from a table.
• Use the OLD keyword to reference the deleted row in the trigger code.
• Make sure to use the correct syntax for the trigger declaration to avoid syntax errors.
• Be careful not to create recursive or infinite triggers that can cause performance issues.

Summary

In this tutorial, we learned how to create and use AFTER DELETE triggers in SQLite. We saw an example of how to log deleted rows into an audit table using an AFTER DELETE trigger on an employees table. AFTER DELETE triggers can be used for a variety of use cases such as logging, referential integrity, and cascading deletes. It's important to use triggers carefully and to avoid creating recursive or infinite triggers.