70.901
Defining Triggers in SQLite

---

Objectives

Upon completion of this lesson, you will be able to:

• define a trigger in SQLite
• remove a trigger from a table

---

Overview

SQLite, like most relational database management systems, supports triggers. Triggers in SQLite are essentially database “callbacks”, which means they are automatically executed (“fired” or “triggered”) by the SQLite SQL execution engine in response to certain events on a particular table or view. These events generally are modification events and include INSERT, UPDATE, and DELETE operations. Triggers can be defined to execute before or after the specified event, providing a useful mechanism for enforcing data integrity, maintaining audit trails, and implementing complex business logic directly within the database. The benefit is that business logic is centralized. However, triggers are written in a proprietary programming language unique to each database and thus can be difficult to maintain and reduce database mobility.

Key Features of Triggers in SQLite

• Automatic Execution: Triggers run automatically in response to specified data modification events, without the need for explicit invocation.
• Event Types: Triggers can be set to fire before or after INSERT, UPDATE, and DELETE operations.
• Row-Level and Statement-Level Triggers: SQLite supports row-level triggers, which execute once for each row affected by the triggering event. Statement-level triggers, as seen in some other RDBMS systems, execute once per SQL statement, are not directly supported but can be simulated using control structures within the trigger.
• Support for WHEN Clause: Triggers in SQLite can include a WHEN clause to specify a condition for the trigger’s execution, allowing for more precise control over when the trigger fires.
• Nested Triggers: SQLite allows triggers to fire other triggers (nested execution), which can be controlled by the PRAGMA recursive_triggers setting.

Common Uses of Triggers in SQLite

• Data Validation: To enforce complex data validation rules that cannot be defined through standard SQLite constraints.
• Integrity Constraints: To implement complex integrity constraints or to enforce referential actions (like cascading deletes or updates) that go beyond SQLite’s foreign key constraints.
• Auditing: To automatically record changes to data, such as logging modifications to certain tables for auditing purposes.
• Automatic Modification: To automatically update or transform data in response to database operations, such as updating a “last modified” timestamp column whenever a row is updated.

Example of a Trigger in SQLite

Here’s a simple example of a trigger in SQLite that automatically updates a “last_modified” timestamp column in a table called “my_table” whenever a row is updated:

CREATE TRIGGER UpdateLastModifiedTime
AFTER UPDATE ON my_table
BEGIN
    UPDATE my_table SET last_modified = CURRENT_TIMESTAMP WHERE rowid = NEW.rowid;
END;

This trigger is defined to fire after an update operation on “my_table”. It sets the “last_modified” column to the current timestamp for the row that was updated.

Considerations on Use

• Performance Impact: Triggers can impact database performance, especially if they perform complex operations or if many triggers fire in response to frequent data modification events.
• Debugging Complexity: Debugging can become more challenging with triggers, as the logic is executed implicitly, which may lead to unexpected behavior if not carefully managed.

Tutorial

In the short video tutorial below, Khoury Boston’s Prof. Schedlbauer shows how to define and test triggers in a SQLite database. Note that while SQLite supports triggers it does not support stored procedures. Triggers are commonly used in SQLite to maintain user defined integrity constraints, keep derived attributes updated, and ensure that values are in defined ranges.

Every database has its own syntax for defining triggers and imposes its own restrictions on what can be done in a trigger and what cannot: SQLite is no different.

Slide Deck: 70.901 - Triggers in SQLite

Step-by-Step Guide

Building a trigger in SQLite involves specifying the timing, event, and action you want the trigger to perform in response to changes in the database. Here’s a step-by-step guide on how to create a trigger in SQLite:

1. Determine the Trigger’s Purpose

First, decide what you want the trigger to do. For example, you might want to log changes to a table, enforce complex constraints, or automatically update related data.

2. Choose the Trigger Event and Timing

Decide on the event (INSERT, UPDATE, DELETE) that will fire the trigger and whether it should happen BEFORE, AFTER, or INSTEAD OF the event.

Basic Syntax

The basic syntax for creating a trigger in SQLite is:

CREATE TRIGGER [IF NOT EXISTS] trigger_name
[BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[FOR EACH ROW]
[WHEN condition]
BEGIN
    -- Trigger logic here
END;

• [IF NOT EXISTS]: Optional. Specifies that the trigger should only be created if no other trigger of the same name already exists.
• trigger_name: The name of the trigger.
• [BEFORE|AFTER|INSTEAD OF]: Specifies when the trigger should be fired relative to the event.
• event_name: The type of operation that activates the trigger (INSERT, UPDATE, DELETE).
• table_name: The name of the table on which the trigger operates.
• [FOR EACH ROW]: Specifies that the trigger should be fired once for each row affected by the event. Omitting this means the trigger is considered a “statement-level trigger.”
• [WHEN condition]: An optional condition for the trigger to fire. If the condition is not met, the trigger does not execute.
• – Trigger logic here: The SQL statements to be executed when the trigger fires.

3. Define the Trigger Action

Write the SQL statements that should be executed when the trigger fires. These can include operations like INSERT, UPDATE, DELETE, or even calling other functions.

Example: Logging Updates to a Table

Suppose you have a table employees and you want to log every update made to an employee’s salary in a salary_changes log table. You could create an AFTER UPDATE trigger like this:

CREATE TRIGGER log_salary_change
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN OLD.salary != NEW.salary
BEGIN
    INSERT INTO salary_changes(employee_id, old_salary, new_salary, change_date)
    VALUES (NEW.employee_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
END;

This trigger fires after the salary column of an employee is updated, but only if the new salary is different from the old salary. It then logs the change in the salary_changes table.

4. Execute the CREATE TRIGGER Statement

Run the CREATE TRIGGER statement in your SQLite environment to create the trigger. You can do this through an SQLite client or through a scripting interface that interacts with SQLite.

5. Test the Trigger

After creating the trigger, perform operations on the target table to ensure the trigger behaves as expected. Check the outcomes based on the logic you’ve defined.

Best Practices

• Ensure the logic within your triggers is efficient and does not lead to unintended side effects.
• Keep the trigger logic simple to avoid complex interdependencies that can make debugging difficult.
• Consider the performance implications of triggers, as complex or numerous triggers can impact the database performance.

Creating triggers in SQLite can greatly enhance the functionality and integrity of your database by automating tasks and enforcing rules directly within the database.

Order of Execution of Multiple Triggers

In SQLite, when multiple triggers are defined for the same event on a single database object, such as a table, and they are of the same time (i.e., BEFORE, AFTER, or INSTEAD OF), the triggers are executed in the order they were created. This means that the trigger that was defined first will be executed first, followed by the next trigger defined, and so on. This order of execution is consistent as long as the triggers remain defined in the database. Other databases may execute the triggers in a different order or perhaps even non-deterministic, so relying on a specific order of execution is dangerous and can lead to unintended behavior and should be avoided.

However, SQLite does not provide built-in functionality to explicitly specify the order of trigger execution beyond this. If a specific execution order is required, the developer must manage the creation order carefully or design the triggers in such a way that their execution order does not impact the application logic or data integrity.

It’s important to design triggers and their logic with consideration of this execution order, especially when triggers might have dependencies on the effects of one another. Proper testing and validation are essential to ensure that the combined effect of multiple triggers on the same event does not lead to unintended consequences or violate data integrity constraints.

Summary

Triggers in SQLite offer a flexible mechanism for implementing automatic responses to database events, making them a valuable feature for developers looking to enforce data integrity, automate database logic, and manage complex relationships within their SQLite databases.

---

Files & Resources

All Files for Lesson 70.901

---

References

• SQLite Triggers
• SQLite INSTEAD OF Triggers

Errata

None collected yet. Let us know.

Let us know.