Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 79 ยท SQL for Database Courses

Triggers, Stored Procedures and Cursors

Understand event-condition-action rules, row versus statement triggers, live SQLite triggers, and how stored procedures and cursors work in server databases.

So far the database has been passive: it answers queries and enforces constraints, but only when a program asks. Active database features let logic live inside the database itself. A trigger runs automatically when something changes. A stored procedure is a named program stored in the database that applications can call. A cursor lets a procedure walk through a query result one row at a time. Together they are called stored logic.

Triggers can run live in your browser because SQLite supports them. Stored procedures and cursors exist only in server engines (PostgreSQL, MySQL, SQL Server, Oracle), so those parts are read-only code, labelled as such. For the practical syntax, see Triggers and Stored Procedures in the SQL tutorial.

A trigger is an ECA rule

Event INSERT, UPDATE or DELETE on a table.
Condition An optional WHEN test.
Action SQL statements that run.

Event, Condition, Action. The database fires it for you.

1 / 2

Event, condition, action

A trigger is defined by three parts. The event is the change that wakes it up (INSERT, UPDATE or DELETE on a table). The condition is an optional test (WHEN) that decides whether to continue. The action is the body that runs. You also choose the timing:

  • BEFORE: runs before the change, useful to validate or reject it.
  • AFTER: runs after the change, useful for audit logs and derived data.
  • INSTEAD OF: runs in place of the change, used on views.

Inside the body you can see the old and new versions of the row: NEW.column for an inserted or updated row and OLD.column for a deleted or updated row.

Row-level versus statement-level

A row-level trigger runs once for each affected row; a statement-level trigger runs once for the whole statement, however many rows it touches. The standard syntax is FOR EACH ROW or FOR EACH STATEMENT. PostgreSQL and Oracle support both; MySQL has only row-level triggers; SQLite also has only row-level triggers, and the clause FOR EACH STATEMENT is a syntax error there. If you update 500 rows in SQLite, the trigger body runs 500 times.

SQLite triggers in action

A classic use is an audit trail: whenever a salary changes, record who, what and when. The next example also uses a WHEN condition (only log real changes) and a BEFORE trigger that rejects invalid data with RAISE(ABORT, ...).

SQL Audit log and validation

Press Run (or Ctrl + Enter) to see the result here.

The audit table ends with three entries: Ana from 5,000 to 5,500, then Ana 5,500 to 5,600 and Boris 4,000 to 4,100 from the last statement, which shows the row-level behaviour. Boris's no-op update from 4,000 to 4,000 was skipped by the WHEN condition. The big-cut trigger did not fire, because nobody's salary dropped by more than a fifth.

Now let the rejecting trigger do its job. This statement fails at its last line, and the lesson shows it on purpose:

SQL A trigger rejects a change (ends with an error)

Press Run (or Ctrl + Enter) to see the result here.

The result is the error salary cut too large, and the row keeps its old value because ABORT undoes the statement.

Derived data and cascading effects

Triggers also keep redundant data in sync, for example a stored order total. They can fire other triggers (cascading), and a careless design can loop. Triggers are hidden logic: someone reading the application code will not see them, which makes debugging harder. Use them for rules that must hold no matter which program writes, such as audit trails, and prefer declarative constraints (Enforcing Integrity: Constraints, Assertions and Triggers) when a constraint can do the job.

Stored procedures

A stored procedure is a named routine kept in the database. Clients call it with parameters, and it runs next to the data, which saves network round trips and centralises business rules. The languages differ per vendor: PL/pgSQL in PostgreSQL, T-SQL in SQL Server, PL/SQL in Oracle, and a SQL/PSM dialect in MySQL. SQLite has no stored procedures, so the code below is read-only and will not run on this site.

SQL
-- Read-only: PostgreSQL (PL/pgSQL). Not supported by SQLite.
CREATE PROCEDURE transfer(from_id INT, to_id INT, amount NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE account SET balance = balance - amount WHERE id = from_id;
  UPDATE account SET balance = balance + amount WHERE id = to_id;
END;
$$;

CALL transfer(1, 2, 100);

Procedures differ from functions, which return a value and can be used inside a query. Advantages are speed, reuse and tighter security (applications get the right to call a procedure instead of access to tables). Costs are vendor lock-in, harder version control and testing, and business logic split between the application and the database.

Cursors

SQL works on whole sets, but sometimes procedural code needs to process a result row by row. A cursor is a pointer into a query result: you DECLARE it, OPEN it, repeatedly FETCH the next row into variables and finally CLOSE it. Again this is server-side only:

SQL
-- Read-only: PostgreSQL (PL/pgSQL)
DO $$
DECLARE
  r RECORD;
  cur CURSOR FOR SELECT id, salary FROM employee ORDER BY id;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO r;
    EXIT WHEN NOT FOUND;
    RAISE NOTICE 'employee % earns %', r.id, r.salary;
  END LOOP;
  CLOSE cur;
END $$;

Cursors are slower than a set-based statement that does the same job in one pass, so use them only when the logic truly cannot be written as a query. In application code, your database library's result iterator plays the same role.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What do the letters in ECA stand for?

    A trigger fires on an event, checks an optional condition and then performs an action.
  2. 2. How often does a row-level trigger run when one UPDATE changes 50 rows?

    Row-level triggers run once for each affected row. A statement-level trigger would run once. SQLite supports only the row-level kind.
  3. 3. Which trigger timing is best for rejecting an invalid change before it happens?

    A BEFORE trigger sees the change before it is applied and can abort it, for example with RAISE(ABORT, message) in SQLite.
  4. 4. Which of these does SQLite NOT support?

    SQLite is an embedded engine without a procedural language, so stored procedures and cursors belong to server databases. Triggers and views work.
  5. 5. Why are triggers described as hidden logic?

    Code that changes the table gives no sign that a trigger will run, so overuse makes behaviour hard to understand and debug.

Summary

  • A trigger is an event-condition-action rule: it fires on INSERT, UPDATE or DELETE, checks an optional WHEN condition and runs an action.
  • Timing can be BEFORE, AFTER or INSTEAD OF; NEW and OLD expose the row versions.
  • Standard SQL has row-level and statement-level triggers; SQLite and MySQL only have row-level ones.
  • RAISE(ABORT, ...) in a SQLite trigger rejects a change; audit logs are the classic safe use.
  • Stored procedures and cursors exist in server engines only; they bring speed and central rules at the price of lock-in.
  • Prefer declarative constraints first; triggers are hidden logic.

Next: Redundancy and Update Anomalies, the first step into database design theory.