SQLite Triggers: Automating Database Actions

SQLite Triggers provide a powerful way to automate actions and maintain data integrity within your database. With triggers, you can set up stored procedures that get automatically executed when certain events occur, like when data is inserted, updated, or deleted from a table.

Let’s explore triggers in SQLite and see how they can save you time and effort in managing your database.

What is are SQLite Triggers?

A trigger in SQLite is a special type of stored procedure that automatically runs (“fires”) when a specified database event occurs on a particular table.

Triggers are useful for enforcing business rules, validating input data, maintaining referential integrity, auditing changes, and automating repetitive tasks.

For example, you could create a trigger automatically updating a summary table whenever new data is inserted into the main table. Or one that prevents invalid data from being added to the database by raising an error.

SQLite supports the following types of SQLite Triggers:

BEFORE – Runs BEFORE the event (insert, update, delete) occurs
AFTER – Runs AFTER the event occurs
INSTEAD OF – Replaces the event (for views only)

Triggers in SQLite fire once for each row affected by the database event, making them FOR EACH ROW triggers.

Creating Triggers in SQLite

To create a trigger in SQLite, you use the CREATE TRIGGER statement. The basic syntax is:

CREATE TRIGGER trigger_name 
BEFORE|AFTER|INSTEAD OF event_type ON table_name
BEGIN
   -- trigger logic
END;

Where:

trigger_name – Name you want to give the trigger
BEFORE|AFTER|INSTEAD OF – When to fire the trigger
event_type – INSERT, UPDATE, or DELETE
table_name – Table on which to create the trigger

Let’s look at an example that creates an AFTER INSERT trigger:

CREATE TRIGGER new_user_trigger
AFTER INSERT ON users
BEGIN
  -- trigger logic
END;

This trigger will fire after a new row is inserted into the users table.

Inside the trigger logic, we can access special variables like NEW and OLD to reference the affected rows:

NEW – Access newly inserted rows
OLD – Access deleted or updated rows

Now, let’s see some real-world examples of triggers in SQLite.

Example 1: Validating Input with a BEFORE Trigger

One common use case for triggers is to validate input data before allowing it into the database. This helps maintain data integrity and enforce business rules.

Let’s look at an example table called orders:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  order_date TEXT NOT NULL 
);

We want to enforce that the quantity column cannot be negative. To do this, we can create a BEFORE INSERT trigger that checks for negative quantities and raises an error if found:

CREATE TRIGGER quantity_check
BEFORE INSERT ON orders
FOR EACH ROW
WHEN NEW.quantity < 0
BEGIN
  SELECT RAISE(ABORT, 'Quantity cannot be negative'); 
END;

This trigger will fire before each INSERT on the orders table. It checks if NEW.quantity (the new quantity being inserted) is less than 0. If so, it raises an error and aborts the INSERT.

Let’s test it out:

INSERT INTO orders (product_id, quantity, order_date)
VALUES (1, -5, '2023-01-01');

-- Error: Quantity cannot be negative

It worked! Our trigger blocked the invalid INSERT

Also read: SQLite Upsert: Using INSERT ON CONFLICT.

This is a simple example, but it demonstrates how triggers can enforce custom validation rules in SQLite.

Example 2: Updating a Summary Table on INSERT

Another common use case for triggers is maintaining “summary” tables that store aggregates from the primary data tables.

For example, let’s say we have an orders table that tracks all orders, and we want to keep a summary table orders_by_month that sums the total quantity ordered per month.

Whenever a new order comes in, we need to insert it into orders and update orders_by_month. Triggers are perfect for automating this.

First, let’s create the tables:

CREATE TABLE orders (
   id INTEGER PRIMARY KEY,  
   product_id INTEGER NOT NULL,
   quantity INTEGER NOT NULL,
   order_date TEXT NOT NULL
);

CREATE TABLE orders_by_month (
  month TEXT PRIMARY KEY,
  total_qty INTEGER NOT NULL 
);

Now we can create an AFTER INSERT trigger on orders to update orders_by_month automatically:

CREATE TRIGGER update_monthly_totals  
AFTER INSERT ON orders
BEGIN
  UPDATE orders_by_month 
  SET total_qty = total_qty + NEW.quantity 
  WHERE month = strftime('%Y-%m', NEW.order_date);
  
  INSERT INTO orders_by_month(month, total_qty) 
  SELECT strftime('%Y-%m', NEW.order_date), NEW.quantity
  WHERE NOT EXISTS (SELECT 1 FROM orders_by_month 
                    WHERE month = strftime('%Y-%m', NEW.order_date));  
END;

This trigger does two things:

For existing months, it updates the total quantity
For new months, it inserts a new record

This ensures orders_by_month always stays up to date whenever orders changes.

Let’s test it by inserting a new order:

INSERT INTO orders 
VALUES (1, 1, 10, '2023-01-15'); 

-- orders_by_month is updated!
-- 2023-01: 10

Our trigger fired after the INSERT and automatically propagated the change to orders_by_month. Very handy!

Example 3: Creating Audit Logs with SQLite Triggers

A typical administrative task is creating audit logs to record changes made to necessary tables. Triggers provide an easy way to automate this logging without changing your application code.

Let’s look at an example with a users table:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

CREATE TABLE user_audit (
  id INTEGER PRIMARY KEY, 
  user_id INTEGER NOT NULL,
  change_type TEXT NOT NULL,
  change_time TIMESTAMP NOT NULL
);

We want to log all inserts, updates and deletes to the user_audit table. Here are triggers that will accomplish this:

CREATE TRIGGER audit_user_insert
AFTER INSERT ON users
BEGIN
  INSERT INTO user_audit 
  VALUES (NULL, NEW.id, 'INSERT', DATETIME('now'));
END;

CREATE TRIGGER audit_user_update
AFTER UPDATE ON users
BEGIN
  INSERT INTO user_audit
  VALUES (NULL, OLD.id, 'UPDATE', DATETIME('now')); 
END;

CREATE TRIGGER audit_user_delete 
AFTER DELETE ON users
BEGIN
  INSERT INTO user_audit
  VALUES (NULL, OLD.id, 'DELETE', DATETIME('now'));
END;

These insert a new row into user_audit on each insert, update or delete, recording the change details.

Let’s test them out:

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]');

-- Insert audit created

UPDATE users 
SET email = '[email protected]'
WHERE id = 1;

-- Update audit created  

DELETE FROM users
WHERE id = 1;

-- Delete audit created

As you can see, triggers provide an easy way to capture audit trails without any extra coding needed automatically!

Example 4: Enforcing Referential Integrity with SQLite Triggers

Referential integrity is a key concept in database design that prevents orphaned records by enforcing parent-child relationships between tables.

For example, a orders table may have a foreign key referencing a users table. If a user is deleted from users, we also want to delete their associated orders.

Triggers can enforce referential integrity by cascading deletes and updates across related tables.

Let’s look at an example with users, orders, and items:

CREATE TABLE users (
  id INTEGER PRIMARY KEY, 
  name TEXT NOT NULL 
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  FOREIGN KEY(order_id) REFERENCES orders(id)  
);

If we delete a user, we want to delete their orders as well. And if we delete an order, we want to delete its items too.

Here are some triggers that will enforce these cascading deletes:

CREATE TRIGGER delete_user_orders
AFTER DELETE ON users
BEGIN
  DELETE FROM orders 
  WHERE user_id = OLD.id;
END;

CREATE TRIGGER delete_order_items  
AFTER DELETE ON orders
BEGIN
  DELETE FROM order_items
  WHERE order_id = OLD.id;
END;

Let’s see this in action:

DELETE FROM users
WHERE id = 1;

-- User 1 deleted
-- Orders for user 1 deleted  
-- Order items for those orders deleted

This ensures referential integrity is maintained automatically via triggers!

Example 5: Preventing Circular Trigger Logic using SQLite Triggers

When using triggers, you need to be careful to avoid circular trigger logic that causes infinite loops. This can occur when triggers cyclically update tables.

For example, let’s say we have two tables tableA and tableB with AFTER UPDATE triggers that update each other:

CREATE TRIGGER tableA_trigger 
AFTER UPDATE ON tableA
BEGIN
  UPDATE tableB SET x = NEW.x; 
END;

CREATE TRIGGER tableB_trigger
AFTER UPDATE ON tableB 
BEGIN
  UPDATE tableA SET x = NEW.x;
END;

Now if we update tableA, it will update tableB, which will update tableA again, in an endless loop!

To prevent this, SQLite provides the recursive_triggers pragma. When enabled, it prevents recursive trigger execution:

PRAGMA recursive_triggers = TRUE; 

UPDATE tableA SET x = 5; -- Works now!

So remember to watch out for circular trigger logic and use recursive_triggers to avoid problems.

Trigger Tips and Tricks

Here are some additional tips to use triggers effectively in SQLite:

Combine triggers – Use one trigger to handle multiple events (INSERT, UPDATE, DELETE) instead of separate triggers to avoid duplication.
ONLY fire when needed – In UPDATE triggers, use WHEN NEW.column <> OLD.column to only fire when specific columns change.
Mind performance – Avoid heavy processing in triggers to minimize performance impact. Index columns used in WHERE clauses.
Handle errors – Use the RAISE() function to throw errors and BEGIN...EXCEPTION blocks to catch errors.
Make idempotent – Ensure trigger logic gives same results if run multiple times for same condition.
Encapsulate logic – Move complex trigger procedures into a separate function for reusability.
Verify changes – Query affected tables after trigger execution to verify changes.
Manage transactions – Use BEGIN TRANSACTION and COMMIT/ROLLBACK to control transactions.

Using triggers effectively takes some practice, but they are a very powerful tool for automating tasks and enforcing constraints in SQLite. Start simple and work your way up!

Summary

Here’s a quick summary of what we covered about triggers in SQLite:

Triggers automatically run stored procedures on database events like INSERT, UPDATE, DELETE.
Create triggers with CREATE TRIGGER specifying name, event timing (BEFORE/AFTER), event type (INSERT/UPDATE/DELETE), and table.
Use NEW and OLD to access affected rows in trigger logic.
Common uses include validating input, maintaining summaries, auditing, and referential integrity.
Avoid circular triggers and take care with performance, errors, and transactions.
Use triggers judiciously to automate database tasks and enforce business rules.

I hope this guide gives you a good overview of how to use triggers effectively in SQLite. They are a powerful tool that can save you lots of time and effort once you master them!