SQLite - 18 - Triggers

Start MCQ

Triggers are a powerful database feature that allows you to automatically respond to certain events or actions that occur within a SQLite database. They are event-driven and can be used to enforce data integrity, automate complex database operations, and maintain consistency in your database. In this discussion, we will explore triggers in SQLite, their purpose, types, syntax, and practical examples.

Purpose of Triggers:

Triggers serve several essential purposes in SQLite:

Data Validation and Integrity: Triggers can enforce data integrity constraints by checking and validating data before it is inserted, updated, or deleted. This ensures that only valid and consistent data is allowed in the database.
Automating Repetitive Tasks: Triggers can automate repetitive or complex tasks that involve multiple SQL statements. For example, you can use triggers to update related records or perform calculations automatically.
Logging and Auditing: Triggers can be used to log changes made to the database, helping to track and audit modifications over time. This is valuable for security and compliance purposes.
Enforcing Business Rules: Triggers can enforce business rules and logic at the database level, ensuring that specific conditions are met before allowing data changes.

Types of Triggers in SQLite:

SQLite supports two main types of triggers:

BEFORE Trigger: A BEFORE trigger is executed before an action (such as INSERT, UPDATE, or DELETE) is performed. It allows you to inspect, modify, or prevent the action based on certain conditions.
AFTER Trigger: An AFTER trigger is executed after an action is completed. It is typically used for tasks that need to be performed after data modifications, such as logging or auditing.

Creating Triggers in SQLite:

To create a trigger in SQLite, you use the CREATE TRIGGER statement with the desired trigger type (BEFORE or AFTER), the trigger name, the event that triggers the action (e.g., INSERT, UPDATE, DELETE), and the SQL code to execute when the trigger is activated.

The basic syntax for creating a trigger is as follows:

CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name
BEGIN
    -- SQL statements to be executed when the trigger is activated
END;

Example 1: A Simple BEFORE Trigger

Let’s create a BEFORE trigger that prevents the insertion of records with a negative price in a Products table:

CREATE TRIGGER prevent_negative_price
BEFORE INSERT ON Products
BEGIN
    SELECT CASE
        WHEN NEW.Price < 0 THEN
            RAISE (ABORT, 'Price cannot be negative');
    END;
END;

In this example:

We create a BEFORE trigger named prevent_negative_price.
The trigger is associated with the Products table and is activated before INSERT operations.
The trigger checks if the new price value (NEW.Price) is negative, and if so, it raises an ABORT error with a message.

Example 2: An AFTER Trigger for Logging

Let’s create an AFTER trigger that logs changes made to a Customers table in a separate CustomerLog table:

CREATE TRIGGER log_customer_changes
AFTER INSERT OR UPDATE OR DELETE ON Customers
BEGIN
    INSERT INTO CustomerLog (CustomerID, Action, Timestamp)
    VALUES (OLD.CustomerID, CASE
            WHEN NEW.CustomerID IS NOT NULL THEN 'UPDATE'
            WHEN OLD.CustomerID IS NOT NULL THEN 'DELETE'
            ELSE 'INSERT'
        END, DATETIME('now'));
END;

In this example:

We create an AFTER trigger named log_customer_changes.
The trigger is associated with the Customers table and is activated after INSERT, UPDATE, or DELETE operations.
The trigger logs the customer changes into a CustomerLog table, recording the action (INSERT, UPDATE, or DELETE), the customer’s ID, and the timestamp.

Using Triggers:

Once you’ve created a trigger, it automatically responds to the specified events or actions without requiring manual intervention. For example, if you attempt to insert a record with a negative price into the Products table in the first example, the trigger will prevent the insertion and raise an error message.

Disabling and Dropping Triggers:

You can disable a trigger temporarily using the PRAGMA command, and you can remove a trigger using the DROP TRIGGER statement:

To disable a trigger:

PRAGMA foreign_keys = OFF;

To enable a disabled trigger:

PRAGMA foreign_keys = ON;

To drop a trigger:

DROP TRIGGER trigger_name;

Replace trigger_name with the name of the trigger you want to remove.

Conclusion:

Triggers are a valuable feature in SQLite, providing a mechanism to automate tasks, enforce data integrity, log changes, and enforce business rules. When used appropriately, triggers can enhance database functionality and maintain data consistency. Understanding how to create, manage, and use triggers is essential for effective SQLite database administration and application development.

SQLite – 18 – Triggers