Stored procedures and triggers: Logic in the database

In short: Stored procedures are programs stored in the database and called; triggers run automatically when data is inserted, changed or deleted.

In more detail: Normally an application sends individual SQL commands to the database. But you can also place logic in the database. This has advantages and disadvantages that should be weighed.

In Depth

Stored procedure and function

A named program of SQL and flow control (loops, conditions) that is called with CALL or SELECT. Example in PostgreSQL:

CREATE FUNCTION create_order(customer int, amount numeric)
RETURNS int LANGUAGE plpgsql AS $$
DECLARE new_id int;
BEGIN
  INSERT INTO orders (customer_id, amount) VALUES (customer, amount)
  RETURNING id INTO new_id;
  UPDATE customers SET revenue = revenue + amount WHERE id = customer;
  RETURN new_id;
END $$;

Languages: PL/pgSQL (PostgreSQL), T-SQL (SQL Server), PL/SQL (Oracle), MySQL procedures.

Trigger

A trigger is executed automatically before or after INSERT, UPDATE or DELETE:

CREATE TRIGGER change_time
BEFORE UPDATE ON article
FOR EACH ROW EXECUTE FUNCTION set_change_time();

Typical uses: set change time, write audit logs, keep derived values current, check rules that a simple constraint does not cover.

Advantages

• Less network traffic: one call instead of many.
• Logic in one place, regardless of which application accesses the data.
• The database guarantees rules even if an application forgets them.
• Execution in a transaction close to the data.

Disadvantages and alternatives

Business logic in the database is harder to test, version and debug, ties you to one vendor and can create load peaks on the hard-to-scale database server. Hidden effects of triggers surprise developers. Many teams put the logic in the application and use in the database only constraints, indexes and simple triggers (audit, timestamp). Changes to procedures belong, like code, in migrations and version control.

See also: SQL, Transactions and ACID, Database migration, Audit log