SQL Triggers and Stored Procedures

Run SQL automatically when data changes with triggers, and save reusable routines in the database with stored procedures and functions.

Lesson 24 of 102Advanced17 min read

So far, every SQL statement has run because you, or your application, sent it. But some work should happen automatically, every time, without anyone remembering to do it. When a price changes, record the old one. When an order is placed, reduce the stock. When stock would go below zero, refuse the order.

Triggers do exactly that. A trigger is a piece of SQL attached to a table that runs by itself when rows are inserted, updated or deleted. Because it lives in the database, it applies no matter which program, script or person changes the data.

Stored procedures are the other half of the story: named routines saved in the database that you run with a single CALL. In this lesson you'll write triggers in SQLite, see how procedures and functions look in MySQL and PostgreSQL, and learn when this logic belongs in the database and when it belongs in your application.

How a trigger works

SQL
CREATE TRIGGER trigger_name
AFTER UPDATE OF price ON products
FOR EACH ROW
BEGIN
  -- statements to run
END;
  • Timing: BEFORE runs before the change, which is useful for checking or adjusting values; AFTER runs once it's done, which is useful for logging and follow-up work.
  • Event: INSERT, UPDATE (optionally UPDATE OF column) or DELETE.
  • FOR EACH ROW: the body runs once per affected row. SQLite only supports row triggers, so it can be left out there.
  • An optional WHEN condition limits when the trigger fires.

OLD and NEW

Inside a row trigger, two special names give you the row's values:

EventOLDNEW
INSERTnot availablethe row being added
UPDATEthe row beforethe row after
DELETEthe row being removednot available

So OLD.price is the price before an update and NEW.price the price after. In a BEFORE trigger, PostgreSQL and MySQL also let you change NEW values before they're saved, for example to trim a name or set an updated_at time.

Rejecting bad changes

A BEFORE trigger can stop a change by raising an error, which cancels the statement:

DatabaseRaise an error
SQLiteSELECT RAISE(ABORT, 'message');
MySQLSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'message';
PostgreSQLRAISE EXCEPTION 'message'; (inside a trigger function)

Simple rules such as "price must be positive" are better as a CHECK constraint. Use triggers for rules that need to look at other rows or tables, such as checking stock before an order.

Stored procedures and functions

A stored procedure is a saved, named block of SQL with parameters. In MySQL:

SQL
CREATE PROCEDURE restock(IN p_id INT, IN amount INT)
BEGIN
  UPDATE products SET stock = stock + amount WHERE id = p_id;
END;

CALL restock(5, 30);

PostgreSQL uses CREATE PROCEDURE for routines run with CALL, and CREATE FUNCTION for routines that return a value and can be used inside a query. SQLite has neither: its routines are written in the program that uses it. Procedures are often used to wrap several statements in one transaction so the application calls a single, well-tested routine.

SQL Triggers Syntax

Trigger and procedure syntax

-- SQLite trigger
CREATE TRIGGER name AFTER UPDATE OF col ON table
WHEN OLD.col <> NEW.col
BEGIN
  INSERT INTO log VALUES (OLD.id, OLD.col, NEW.col);
END;
DROP TRIGGER name;

-- MySQL procedure
CREATE PROCEDURE name(IN param INT)
BEGIN ... END;
CALL name(42);

SQL Triggers Summary

  • A trigger runs automatically BEFORE or AFTER an INSERT, UPDATE or DELETE on a table.
  • OLD holds the row before the change and NEW the row after.
  • BEFORE triggers can reject changes by raising an error; AFTER triggers suit logs and follow-up updates.
  • Stored procedures are named routines run with CALL; functions return values for use in queries.
  • Use triggers for rules every change must follow, and keep them small and well named.

SQL Triggers Examples

Each example shows the code, the output it produces and what each part does.

Example 1

An audit log of price changes

SQL
CREATE TABLE price_history (
  product_id INTEGER, old_price REAL, new_price REAL, changed_at TEXT
);

CREATE TRIGGER log_price_change
AFTER UPDATE OF price ON products
WHEN OLD.price <> NEW.price
BEGIN
  INSERT INTO price_history
  VALUES (OLD.id, OLD.price, NEW.price, date('now'));
END;

UPDATE products SET price = 36.50 WHERE id = 3;
UPDATE products SET price = 1.20  WHERE id = 2;
SELECT product_id, old_price, new_price FROM price_history;
Output
product_id | old_price | new_price
-----------+-----------+----------
3          | 34.99     | 36.5
Line-by-line explanation
  1. Line 6The trigger fires only when the price column is updated.
  2. Line 7WHEN skips updates that don't really change the price.
  3. Lines 9 to 10OLD and NEW hold the price before and after the change.
  4. Line 14The pen's price was already 1.20, so no history row is written for it.
Example 2

Keeping stock in step with orders

SQL
CREATE TRIGGER check_stock
BEFORE INSERT ON orders
WHEN (SELECT stock FROM products WHERE id = NEW.product_id) < NEW.quantity
BEGIN
  SELECT RAISE(ABORT, 'Not enough stock');
END;

CREATE TRIGGER reduce_stock
AFTER INSERT ON orders
BEGIN
  UPDATE products SET stock = stock - NEW.quantity
  WHERE id = NEW.product_id;
END;

INSERT INTO orders VALUES (7, 5, 6, 2, '2024-07-10');
SELECT id, name, stock FROM products WHERE id = 6;
INSERT INTO orders VALUES (8, 5, 5, 1, '2024-07-10');
Output
id | name      | stock
---+-----------+------
6  | Desk lamp | 20

Error: Not enough stock
Line-by-line explanation
  1. Lines 1 to 6Before an order is saved, compare its quantity with the product's stock and cancel it if there isn't enough.
  2. Lines 8 to 13After a successful order, reduce the stock automatically.
  3. Lines 15 to 16Ema orders two desk lamps, and stock falls from 22 to 20.
  4. Line 17The lunch box has 0 in stock, so this order is rejected and never saved.
Example 3

A procedure and a function in PostgreSQL

SQL
CREATE PROCEDURE restock(p_id INT, amount INT)
LANGUAGE SQL
AS $$
  UPDATE products SET stock = stock + amount WHERE id = p_id;
$$;

CREATE FUNCTION price_with_tax(price NUMERIC) RETURNS NUMERIC
LANGUAGE SQL
AS $$ SELECT ROUND(price * 1.2, 2) $$;

CALL restock(5, 30);
SELECT name, stock, price_with_tax(price) FROM products WHERE id = 5;
Output
CALL
   name    | stock | price_with_tax
-----------+-------+----------------
 Lunch box |    30 |          10.50
Line-by-line explanation
  1. Lines 1 to 5A procedure performs an action and is run with CALL.
  2. Lines 7 to 9A function returns a value, so it can be used inside SELECT like a built-in function.
  3. Lines 11 to 12Restock the lunch box, then show its new stock and taxed price.

Common SQL Triggers Mistakes

Using a trigger for a simple rule

Don't
CREATE TRIGGER no_negative_price BEFORE INSERT ON products
WHEN NEW.price < 0 BEGIN SELECT RAISE(ABORT, 'bad price'); END;
Do
CREATE TABLE products (..., price REAL CHECK (price >= 0));

Updating the same table in an AFTER UPDATE trigger

Don't
CREATE TRIGGER touch AFTER UPDATE ON products
BEGIN UPDATE products SET updated_at = date('now') WHERE id = NEW.id; END;
-- can fire itself again in databases with recursive triggers enabled
Do
-- PostgreSQL / MySQL: set NEW.updated_at in a BEFORE UPDATE trigger
-- or set updated_at in the UPDATE statement itself

Forgetting the WHEN condition

Don't
CREATE TRIGGER log_price_change AFTER UPDATE ON products
BEGIN INSERT INTO price_history VALUES (OLD.id, OLD.price, NEW.price, date('now')); END;
-- logs every update, even when only stock changed
Do
CREATE TRIGGER log_price_change AFTER UPDATE OF price ON products
WHEN OLD.price <> NEW.price
BEGIN INSERT INTO price_history VALUES (OLD.id, OLD.price, NEW.price, date('now')); END;

SQL Triggers in Real Projects

Triggers and procedures in practice

Banks and healthcare systems use triggers to write audit trails that record who changed what and when, regardless of which application made the change. Shops use them to keep stock and order totals consistent. Companies with several applications sharing one database put important operations, like transferring money between accounts, in stored procedures so every application follows exactly the same steps.

SQL Triggers FAQ

Should I use a trigger or a constraint?

Use a constraint whenever one can express the rule: NOT NULL, UNIQUE, CHECK and foreign keys are simpler, faster and visible in the table definition. Use a trigger for things constraints can't do, such as writing an audit row or updating a summary table.

What is the difference between BEFORE and AFTER triggers?

A BEFORE trigger runs before the row is written, so it can check or change the new values, or reject the change. An AFTER trigger runs once the change has happened, which suits audit logs and updates to other tables, because the change is known to have passed every check.

Does SQLite support stored procedures?

No. SQLite has triggers and views but no stored procedures or CREATE FUNCTION in SQL. Because SQLite runs inside your application, that logic usually lives in the application code, which can also register custom functions with SQLite.

What is the difference between a procedure and a function?

A function returns a value and can be used inside queries, such as in a SELECT list. A procedure is run on its own with CALL and performs a series of actions; in PostgreSQL 11 and later, procedures can also commit transactions part-way through. MySQL and SQL Server draw the line in similar ways.

Are triggers bad practice?

Not inherently, but hidden logic is easy to forget: a trigger that changes other tables can surprise developers and make bulk operations slow. Keep triggers small, name them clearly, document them, and prefer constraints or explicit application code for core business rules.

SQL Triggers Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the trigger so it runs after a row is deleted from orders.

CREATE TRIGGER log_delete
 DELETE ON orders
BEGIN
  INSERT INTO deleted_orders VALUES (OLD.id);
END;
Multiple choiceeasy

In an UPDATE trigger, what does OLD.price contain?

Multiple choicemedium

Which trigger timing can stop a bad insert before it's saved?

Predict the outputmedium

In this lesson's audit example, how many rows does price_history contain after the two UPDATEs?

Multiple choicehard

What's the difference between a stored procedure and a function in PostgreSQL?

SQL Triggers Quiz

5 questions to check what you learned.

  1. What starts a trigger?

    • A timer
    • A SELECT query
    • An INSERT, UPDATE or DELETE on its table
    • Restarting the database

  2. Which is better for "quantity must be at least 1"?

    • A trigger
    • A CHECK constraint
    • A stored procedure
    • A view

  3. How do you run a stored procedure in MySQL?

    • SELECT name();
    • RUN name;
    • EXEC TRIGGER name;
    • CALL name();

  4. Why might a developer be surprised by a trigger?

    • Triggers slow down SELECT queries
    • Triggers delete tables
    • Triggers can't be dropped
    • Triggers run silently, so an UPDATE can change other tables without the code showing it

  5. Why put an audit log in a trigger rather than in application code?

    • It records changes made by every program and person, not just one application
    • It's faster to write
    • Applications can't write to tables
    • Triggers use less storage

Related SQL topics

22TransactionsGroup changes into all-or-nothing transactions with BEGIN, COMMIT and ROLLBACK, and understand ACID.11Constraints & KeysProtect your data with NOT NULL, UNIQUE, DEFAULT, CHECK, PRIMARY KEY and FOREIGN KEY constraints.25Security & PermissionsProtect databases from SQL injection with parameterized queries, give each user only the access they need with GRANT and roles, and follow everyday security habits.
Esc
↑↓ to navigateEnter to open