sqlite
  1. sqlite-triggers
PreviousNext

SQLite Triggers

SQLite triggers are user-defined programs that are automatically executed in response to a certain event or condition, such as an insert, update, or delete operation on a table. Triggers can be used to enforce integrity constraints, audit changes to data, or perform custom actions.

Syntax

The syntax for creating a trigger in SQLite is as follows:

CREATE TRIGGER trigger_name
[AFTER/BEFORE] [INSERT/UPDATE/DELETE] 
ON table_name 
[FOR EACH ROW]
BEGIN
-- SQL statements to be executed
END;

Example

Suppose we have a table called orders with columns id, customer_name, order_date, and total_amount. We can create a trigger to make sure that the total_amount column is always greater than 0.

CREATE TRIGGER check_total_amount 
BEFORE INSERT ON orders 
FOR EACH ROW
BEGIN
    IF NEW.total_amount <= 0
    THEN 
        SELECT RAISE(ABORT, "Total amount must be greater than 0");
    END IF;
END;

Output

When an incorrect value is inserted into the orders table, as shown below:

INSERT INTO orders (id, customer_name, order_date, total_amount) 
VALUES (1, 'John Doe', '2022-01-01', -100);

The trigger would raise an error and rollback the insert operation with the error message:

Error: Total amount must be greater than 0

Explanation

In the example above, we create a trigger called check_total_amount that is executed before an insert operation on the orders table. We specify that the trigger should execute for each row (FOR EACH ROW), and we define the SQL statements to be executed between the BEGIN and END keywords.

The IF statement checks if the value being inserted into the total_amount column is less than or equal to 0. If the condition is true, the RAISE statement raises an exception with an error message.

Use

Triggers in SQLite can be used to enforce data integrity constraints, such as checking for values within a certain range or format, auditing changes to data, or performing custom actions. They can ensure that database operations follow business rules and can be used to implement complex logic without modifying application code.

Important Points

  • Triggers can be defined for insertions, deletions, and updates on tables or views.
  • Triggers can be executed either before or after the occurrence of an event.
  • RAISE statement can be used to raise an exception and roll back a transaction.
  • Triggers can be disabled and enabled using the PRAGMA foreign_keys command.
  • Care should be taken to ensure that triggers do not negatively impact database performance.

Summary

In this tutorial, we learned about triggers in SQLite and their syntax for creating user-defined programs that execute in response to certain events or conditions. We saw an example of using a trigger to enforce a data integrity constraint and how it raises an exception when a condition is not met. It's important to use triggers judiciously to improve data consistency and reliability in your database.

SUMAfter Insert
Published on: