Triggers and Stored Procedures

This section documents standard SQL (ISO/IEC 9075-2 (SQL/Foundation)) functionality only. Vendor-specific extensions or behavior of any particular database management system are intentionally left out. In the US, the standard is also distributed via ANSI’s webstore (search "ISO/IEC 9075"). This content was generated with the assistance of AI and should be verified against your target DBMS’s own documentation before relying on it in production.

This page covers the standard SQL facilities for reacting to data changes and for packaging procedural logic inside the database: CREATE TRIGGER, CREATE PROCEDURE, CREATE FUNCTION, and the basic control-flow statements defined by the ISO/IEC 9075-4 (SQL/PSM) (Persistent Stored Modules) standard for use inside procedure and function bodies.

Triggers

A trigger is a named database object that automatically executes a block of SQL statements — the trigger action — whenever a specified event occurs on a specified table.

CREATE TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON table_name
    [ FOR EACH { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    trigger_action

Parameters/options:

  • trigger_name — the name given to the trigger.

  • BEFORE / AFTER — whether the trigger action runs before or after the triggering event is applied to the table.

  • INSERT / UPDATE / DELETE — the triggering event: the kind of data-modification statement that causes the trigger to fire.

  • table_name — the table the trigger is attached to.

  • FOR EACH ROW — the trigger action runs once for every row affected by the triggering statement. FOR EACH STATEMENT (the default) runs the trigger action once per triggering statement, regardless of how many rows it affects.

  • WHEN ( condition ) — an optional predicate. When present, the trigger action only runs for rows (or statements) for which condition evaluates to true.

  • trigger_action — the SQL statement, or BEGIN …​ END compound statement, executed when the trigger fires.

BEFORE triggers

A BEFORE trigger runs before the triggering event is applied, which allows it to validate or adjust data ahead of the change.

CREATE TRIGGER check_salary_before_insert
    BEFORE INSERT ON employees
    FOR EACH ROW
    WHEN ( NEW.salary < 0 )
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Salary must not be negative'

AFTER triggers

An AFTER trigger runs once the triggering event has already been applied, which is typical for auditing or cascading effects into other tables.

CREATE TRIGGER log_salary_update
    AFTER UPDATE ON employees
    FOR EACH ROW
    WHEN ( NEW.salary <> OLD.salary )
    INSERT INTO employees_salary_audit (employee_id, old_salary, new_salary)
    VALUES (OLD.employee_id, OLD.salary, NEW.salary)

FOR EACH ROW vs. the triggering event

The triggering event determines which of the OLD and NEW transition values are available inside the trigger action for a FOR EACH ROW trigger:

  • INSERT — only NEW is available, referring to the row being inserted.

  • UPDATE — both OLD (the row’s values before the update) and NEW (the row’s values after the update) are available.

  • DELETE — only OLD is available, referring to the row being removed.

CREATE TRIGGER prevent_department_change
    BEFORE UPDATE ON employees
    FOR EACH ROW
    WHEN ( NEW.department <> OLD.department )
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Department changes must go through the transfer procedure'

Stored Procedures

The SQL/PSM standard (ISO/IEC 9075-4) defines CREATE PROCEDURE for packaging a sequence of SQL and procedural statements under a name that can later be invoked with CALL.

CREATE PROCEDURE procedure_name ( [ parameter_mode ] parameter_name data_type, ... )
    procedure_body

Parameters/options:

  • procedure_name — the name given to the procedure.

  • parameter_mode — how each parameter is passed:

    • IN (the default) — the caller passes a value in; the procedure cannot change the caller’s argument.

    • OUT — the procedure assigns a value that is passed back to the caller; the caller’s initial argument value is ignored.

    • INOUT — the caller passes a value in, the procedure may reassign it, and the new value is passed back to the caller.

  • parameter_name data_type — the parameter’s name and SQL data type.

  • procedure_body — a single SQL statement, or a BEGIN …​ END compound statement containing a sequence of SQL/PSM statements, executed when the procedure is called.

A procedure with IN parameters

CREATE PROCEDURE give_raise (IN p_employee_id INTEGER, IN p_percentage DECIMAL(5,2))
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_percentage / 100)
    WHERE employee_id = p_employee_id;
END

Invoking it:

CALL give_raise(101, 5.00)

A procedure with OUT and INOUT parameters

CREATE PROCEDURE department_headcount (IN p_department VARCHAR(50), OUT p_count INTEGER)
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM employees
    WHERE department = p_department;
END
CREATE PROCEDURE apply_bonus (INOUT p_salary DECIMAL(10,2), IN p_bonus DECIMAL(10,2))
BEGIN
    SET p_salary = p_salary + p_bonus;
END

Functions

The SQL/PSM standard also defines CREATE FUNCTION for packaging procedural logic that computes and returns a single value, which can then be used anywhere a scalar expression is valid.

CREATE FUNCTION function_name ( [ parameter_name data_type, ... ] )
    RETURNS data_type
    function_body

Parameters/options:

  • function_name — the name given to the function.

  • parameter_name data_type — the function’s input parameters and their SQL data types. Function parameters are always input-only, so no IN/OUT/INOUT mode is written.

  • RETURNS data_type — the SQL data type of the value the function returns.

  • function_body — a BEGIN …​ END compound statement that computes the result and ends with a RETURN statement.

RETURNS and RETURN

The RETURNS clause on CREATE FUNCTION declares the type of value the function produces; the RETURN statement inside the function body supplies that value and immediately ends execution of the function.

CREATE FUNCTION annual_salary (p_monthly_salary DECIMAL(10,2))
    RETURNS DECIMAL(12,2)
BEGIN
    RETURN p_monthly_salary * 12;
END

Using the function in a query, like any other scalar expression:

SELECT employee_id, annual_salary(salary) AS yearly_salary
FROM employees

A function with conditional logic

CREATE FUNCTION salary_band (p_salary DECIMAL(10,2))
    RETURNS VARCHAR(10)
BEGIN
    IF p_salary < 50000.00 THEN
        RETURN 'LOW';
    ELSE
        RETURN 'HIGH';
    END IF;
END

Control Flow

SQL/PSM defines a small set of procedural control-flow statements that may be used inside the body of a CREATE PROCEDURE, CREATE FUNCTION, or FOR EACH ROW trigger action.

IF …​ THEN …​ ELSE …​ END IF

IF conditionally executes one of two (or more, via ELSEIF) statement lists depending on whether a condition evaluates to true.

IF condition THEN
    statements
[ ELSEIF condition THEN
    statements ]
[ ELSE
    statements ]
END IF
CREATE PROCEDURE classify_salary (IN p_salary DECIMAL(10,2), OUT p_band VARCHAR(10))
BEGIN
    IF p_salary < 40000.00 THEN
        SET p_band = 'LOW';
    ELSEIF p_salary < 80000.00 THEN
        SET p_band = 'MEDIUM';
    ELSE
        SET p_band = 'HIGH';
    END IF;
END

WHILE …​ DO …​ END WHILE

WHILE repeats a statement list for as long as its condition evaluates to true, testing the condition before each iteration.

[ label: ] WHILE condition DO
    statements
END WHILE [ label ]
CREATE PROCEDURE apply_raises_until_target (INOUT p_salary DECIMAL(10,2), IN p_target DECIMAL(10,2))
BEGIN
    WHILE p_salary < p_target DO
        SET p_salary = p_salary * 1.02;
    END WHILE;
END

LOOP …​ END LOOP

LOOP repeats a statement list unconditionally; a LEAVE statement, typically guarded by an IF, is used to exit the loop.

[ label: ] LOOP
    statements
END LOOP [ label ]
CREATE PROCEDURE double_until_target (INOUT p_value DECIMAL(10,2), IN p_target DECIMAL(10,2))
BEGIN
    growth_loop: LOOP
        IF p_value >= p_target THEN
            LEAVE growth_loop;
        END IF;
        SET p_value = p_value * 2;
    END LOOP growth_loop;
END