BEFORE Triggers

BEFORE triggers run prior to row changes.

They are commonly used for validation and guardrails that prevent invalid writes.

Core Concepts

flowchart LR
  A[Incoming write] --> B[BEFORE trigger runs]
  B --> C{Validation passes?}
  C -->|Yes| D[Row change proceeds]
  C -->|No| E[Raise error and abort]

Typical use	Example
Block invalid values	Disallow amount <= 0
Enforce custom rule	Reject out-of-range status codes

Code Examples

-- Setup table for validation demo.
CREATE TABLE payments_bt (
  payment_id INTEGER PRIMARY KEY,
  amount     REAL NOT NULL
);

Expected output
Table payments_bt created.

-- BEFORE INSERT trigger: reject non-positive amounts.
CREATE TRIGGER trg_payments_before_insert
BEFORE INSERT ON payments_bt
FOR EACH ROW
WHEN NEW.amount <= 0
BEGIN
  SELECT RAISE(ABORT, 'amount must be positive');
END;

Expected output
Validation trigger created successfully.

-- Valid insert passes.
INSERT INTO payments_bt (amount)
VALUES (50.0);

-- Invalid insert fails.
INSERT INTO payments_bt (amount)
VALUES (-10.0);

Statement	Expected result
Insert 50.0	Success
Insert -10.0	Error: amount must be positive

-- Verify only valid row exists.
SELECT payment_id, amount
FROM payments_bt;

payment_id	amount
1	50.0

SQLite-Specific Nuances

SQLite Nuance

RAISE() is SQLite-specific trigger control for aborting or failing statements with custom messages.

Common Pitfalls / Best Practices

Pitfall

Using BEFORE triggers for complex transformations that are hard to reason about or test.

Best Practice

Keep BEFORE triggers focused on clear validation rules, and produce actionable error messages.

Quick Challenge

Add a BEFORE UPDATE trigger to block updates where NEW.amount <= 0 in payments_bt.

View Solution

CREATE TRIGGER trg_payments_before_update
BEFORE UPDATE ON payments_bt
FOR EACH ROW
WHEN NEW.amount <= 0
BEGIN
  SELECT RAISE(ABORT, 'updated amount must be positive');
END;

Expected output
Trigger created and invalid updates are now blocked.