SQLite - Triggers

SQLite - Triggers

A trigger is a stored program that is automatically executed or ‘fired’ by SQLite in response to a specific event on a particular table. These events are INSERT, DELETE, or UPDATE operations.

Triggers are powerful tools for:

• Auditing and Logging: Recording changes made to data for security or history.
• Enforcing Complex Business Rules: Implementing constraints that are too complex for a simple CHECK constraint.
• Data Validation and Transformation: Modifying or validating data before it’s inserted or updated.
• Maintaining Data Integrity: Keeping summary tables or related data in sync automatically.

Syntax

Here is the generalized syntax for creating a trigger. Inside a trigger, you can access the data before the change using OLD.column_name and the data after the change using NEW.column_name.

CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | DELETE | UPDATE OF column_name]
ON table_name
FOR EACH ROW
WHEN (condition)
BEGIN
    -- Trigger logic using OLD and NEW references
    -- e.g., INSERT, UPDATE, DELETE, or SELECT statements;
    -- Can also use RAISE() function to abort operation.
END;

Example: A Comprehensive Audit Log

Let’s create a robust audit trail for a PRODUCTS table. We’ll log all inserts, updates, and deletes into an AUDIT_LOG table.

CREATE TABLE PRODUCTS(
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    NAME TEXT NOT NULL,
    PRICE REAL NOT NULL CHECK(PRICE > 0)
);

CREATE TABLE AUDIT_LOG(
    LOG_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    PRODUCT_ID INT,
    ACTION TEXT NOT NULL,
    OLD_DATA TEXT,
    NEW_DATA TEXT,
    TIMESTAMP TEXT NOT NULL
);

-- Now, create the trigger to populate the audit log
CREATE TRIGGER product_audit_trigger
AFTER INSERT ON PRODUCTS
FOR EACH ROW
BEGIN
    INSERT INTO AUDIT_LOG (PRODUCT_ID, ACTION, NEW_DATA, TIMESTAMP)
    VALUES (NEW.ID, 'INSERT',
            'Name: ' || NEW.NAME || ', Price: ' || NEW.PRICE,
            datetime('now', 'localtime'));
END;

-- In a real application, you would create separate triggers for UPDATE and DELETE too.

Let’s test our INSERT trigger.

sqlite> INSERT INTO PRODUCTS (NAME, PRICE) VALUES ('Laptop', 1200.00);
sqlite> SELECT * FROM AUDIT_LOG;

The trigger automatically creates a log entry:

LOG_ID      PRODUCT_ID  ACTION      OLD_DATA    NEW_DATA                      TIMESTAMP
----------  ----------  ----------  ----------  ----------------------------  -------------------
1           1           INSERT                  Name: Laptop, Price: 1200.0   2023-10-27 10:30:00

Advanced Usage: Preventing Operations

A BEFORE trigger combined with the RAISE() function can prevent an operation from happening. For example, let’s prevent anyone from deleting the last product.

CREATE TRIGGER prevent_last_product_delete
BEFORE DELETE ON PRODUCTS
FOR EACH ROW
WHEN (SELECT COUNT(*) FROM PRODUCTS) = 1
BEGIN
    SELECT RAISE(ABORT, 'Cannot delete the last product.');
END;

-- Now, trying to delete the only product will fail:
sqlite> DELETE FROM PRODUCTS WHERE ID = 1;
Error: Cannot delete the last product.

Managing Triggers

You can list all triggers in the database by querying the sqlite_schema table (an alias for the older sqlite_master).

SELECT name, tbl_name FROM sqlite_schema WHERE type = 'trigger';

To drop a trigger, use the DROP TRIGGER statement. It’s good practice to include IF EXISTS.

DROP TRIGGER IF EXISTS product_audit_trigger;