Triggers, Stored Procedures, and Functions

Databases can run code themselves — automatically in response to changes (triggers), or when called (stored procedures and functions).

Triggers

A trigger runs automatically before or after an INSERT, UPDATE, or DELETE. A common use is an audit log that records every change:

CREATE TABLE price_history (
  product_id INTEGER,
  old_price  NUMERIC,
  new_price  NUMERIC,
  changed_at TEXT DEFAULT (DATETIME('now'))
);

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

UPDATE products SET price = 79.00 WHERE id = 1;
UPDATE products SET price = 39.50 WHERE id = 2;

SELECT product_id, old_price, new_price FROM price_history;

+------------+-----------+-----------+
| product_id | old_price | new_price |
+------------+-----------+-----------+
|          1 |        89 |        79 |
|          2 |      35.5 |      39.5 |
+------------+-----------+-----------+

• OLD and NEW refer to the row before and after the change.
• The trigger fires however the price is changed — from any application, script, or manual fix — so the history can't be forgotten.

Other common uses: keeping an updated_at column current, maintaining summary counts, and enforcing rules too complex for a CHECK constraint.

Stored procedures and functions

PostgreSQL, MySQL, SQL Server, and Oracle let you write procedures in a procedural language (PL/pgSQL, T-SQL, PL/SQL) stored in the database:

-- PostgreSQL
CREATE FUNCTION order_total(p_order_id INTEGER) RETURNS NUMERIC
LANGUAGE sql STABLE AS $$
  SELECT COALESCE(SUM(quantity * unit_price), 0)
  FROM order_items
  WHERE order_id = p_order_id;
$$;

SELECT id, order_total(id) FROM orders;

(SQLite has no stored procedures; applications register functions from their own language instead.)

Should logic live in the database?

In favour: rules are enforced for every client; data-heavy logic runs next to the data; one round trip instead of many.

Against: harder to version, test, debug, and deploy than application code; ties you to one database's language; hidden triggers can surprise developers.

A balanced approach: use constraints for data validity, triggers for a small number of well-documented cross-cutting jobs (auditing, timestamps), and keep business logic in application code unless there's a clear performance reason.