SQLite Triggers

A trigger is a set of SQL statements that SQLite executes automatically when a specific event occurs on a table — an INSERT, UPDATE, or DELETE. Triggers enforce business rules, maintain audit logs, and keep related data consistent without requiring application code to handle every case.

Trigger Anatomy

CREATE TRIGGER trigger_name
  BEFORE | AFTER | INSTEAD OF   ← when to fire
  INSERT | UPDATE | DELETE       ← what event triggers it
  ON table_name                  ← which table to watch
  [FOR EACH ROW]                 ← fire once per affected row
BEGIN
  -- SQL statements to execute
END;

NEW and OLD References

Inside a trigger:
  NEW.column  → the new value being inserted or updated
  OLD.column  → the original value before update or delete

Event         │ NEW available │ OLD available
──────────────┼───────────────┼───────────────
INSERT        │ Yes           │ No
UPDATE        │ Yes           │ Yes
DELETE        │ No            │ Yes

Example 1: Audit Log Trigger

-- Track every salary change
CREATE TABLE salary_log (
  id           INTEGER PRIMARY KEY,
  employee_id  INTEGER,
  old_salary   REAL,
  new_salary   REAL,
  changed_at   TEXT DEFAULT (datetime('now'))
);

CREATE TRIGGER log_salary_change
  AFTER UPDATE OF salary ON employees
  FOR EACH ROW
BEGIN
  INSERT INTO salary_log (employee_id, old_salary, new_salary)
  VALUES (OLD.id, OLD.salary, NEW.salary);
END;

-- Test:
UPDATE employees SET salary = 90000 WHERE id = 1;

SELECT * FROM salary_log;
id │ employee_id │ old_salary │ new_salary │ changed_at
───┼─────────────┼────────────┼────────────┼─────────────────────
1  │ 1           │ 85000      │ 90000      │ 2024-07-24 10:00:00

Example 2: Prevent Negative Balance

CREATE TRIGGER prevent_negative_balance
  BEFORE UPDATE ON accounts
  FOR EACH ROW
  WHEN NEW.balance < 0
BEGIN
  SELECT RAISE(ABORT, 'Balance cannot go negative');
END;

-- Test:
UPDATE accounts SET balance = -100 WHERE id = 1;
-- Error: Balance cannot go negative
-- Transaction is aborted, balance unchanged

Example 3: Auto-Update a Timestamp

ALTER TABLE products ADD COLUMN updated_at TEXT;

CREATE TRIGGER set_updated_at
  AFTER UPDATE ON products
  FOR EACH ROW
BEGIN
  UPDATE products
  SET updated_at = datetime('now')
  WHERE id = NEW.id;
END;

-- Every UPDATE to products now auto-sets updated_at
UPDATE products SET price = 5.99 WHERE id = 1;

SELECT name, price, updated_at FROM products WHERE id = 1;
name     │ price │ updated_at
─────────┼───────┼─────────────────────
Notebook │ 5.99  │ 2024-07-24 11:30:00

Example 4: Cascade Delete Manually

-- When a customer is deleted, remove their orders too
CREATE TRIGGER delete_customer_orders
  AFTER DELETE ON customers
  FOR EACH ROW
BEGIN
  DELETE FROM orders WHERE customer_id = OLD.id;
END;

RAISE() Function in Triggers

RAISE(ABORT,   'message')  -- rollback current statement, keep transaction
RAISE(FAIL,    'message')  -- rollback current statement
RAISE(IGNORE)              -- silently ignore the triggering statement
RAISE(ROLLBACK,'message')  -- rollback entire transaction

Listing and Dropping Triggers

-- List all triggers
SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'trigger';

-- Drop a trigger
DROP TRIGGER IF EXISTS log_salary_change;

INSTEAD OF Triggers on Views

-- Views are normally read-only in SQLite
-- INSTEAD OF trigger makes them writable

CREATE VIEW employee_view AS
SELECT id, name, salary FROM employees WHERE active = 1;

CREATE TRIGGER update_employee_view
  INSTEAD OF UPDATE ON employee_view
  FOR EACH ROW
BEGIN
  UPDATE employees SET salary = NEW.salary WHERE id = OLD.id;
END;

-- Now you can "update" the view:
UPDATE employee_view SET salary = 95000 WHERE id = 1;
-- Trigger redirects this to the real employees table

Key Takeaways

• Triggers fire automatically on INSERT, UPDATE, or DELETE events
• BEFORE triggers run before the change; AFTER triggers run after
• Use NEW.col for incoming values and OLD.col for original values
• RAISE(ABORT, 'message') stops the operation and returns an error
• INSTEAD OF triggers make views writable by redirecting DML to the underlying tables