How to use Triggers In SQL

Triggers in SQLite are special type of stored program that are automatically executed when any specific event occurs such as inserting, deleting, or updating data in a table. Triggers are not designed for implementing the stored procedures, they can be used for encapsulating and executing the complex logic in response to manipulating the operation.

Syntax:

To create the trigger in SQLite we use the CREATE TRIGGER statement

CREATE TRIGGER [IF NOT EXISTS] trigger_name
    [BEFORE|AFTER] [DELETE|INSERT|UPDATE] [OF column1, column2, ...]
    ON table_name
    [FOR EACH ROW]
    [WHEN condition]
BEGIN
    -- Trigger logic here
END;

Here,

• IF NOT EXISTS: Optional clause to avoid an error if the trigger already exists.
• trigger_name: The name of the trigger.
• BEFORE|AFTER: Specifies whether the trigger should be executed before or after the triggering event.
• DELETE|INSERT|UPDATE: The type of data manipulation operation that will trigger the execution.
• OF column1, column2, …: Optional list of columns to which the trigger should respond (for UPDATE triggers).
• table_name: The name of the table on which the trigger will operate.
• FOR EACH ROW: Optional clause to specify that the trigger should be executed for each row affected by the triggering event.
• WHEN condition: Optional condition that must be satisfied for the trigger to execute.

Example: Finding the Total Price of a Product

First of all, we create a category total table that stores the total price of products according to the category.

CREATE TABLE category_totals (
    category_id INTEGER PRIMARY KEY,
    total_price REAL,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

Now, we create the trigger for update and insert operation

-- Trigger to update category totals when a product is inserted or updated

CREATE TRIGGER update_category_total
AFTER INSERT OR UPDATE ON products
BEGIN
    UPDATE category_totals
    SET total_price = (
        SELECT SUM(price)
        FROM products
        WHERE category_id = NEW.category_id
    )
    WHERE category_id = NEW.category_id;
END;

-- Trigger to handle category total when a product is deleted

CREATE TRIGGER delete_category_total
AFTER DELETE ON products
BEGIN
    UPDATE category_totals
    SET total_price = (
        SELECT SUM(price)
        FROM products
        WHERE category_id = OLD.category_id
    )
    WHERE category_id = OLD.category_id;
END;

Output:

Trigger "update_category_total" created successfully.
Trigger "delete_category_total" created successfully.

This trigger is triggered when an update or insert operation can be done on the product table.

Explanation: This output indicates that both triggers were successfully created without any errors. If there were any issues during creation, the compiler would provide error messages detailing the problem.

SQLite is a popular, lightweight, self-contained, serverless, and open-source relational database management system (RDBMS) that is widely used in various applications. SQLite does not directly support stored procedures like other database management systems (DBMS) such as MySQL or PostgreSQL. To achieve the functionality of stored procedures in SQLite there are different approaches. In this article, we will see all approaches, their syntax, and examples.