Triggers & PL/pgSQL

HeliosDB-Lite supports PostgreSQL-compatible triggers and PL/pgSQL procedural language, enabling server-side logic that runs automatically in response to data changes.

Prerequisites

HeliosDB-Lite v3.5 or later
Access to the SQL shell (REPL, PostgreSQL wire protocol, or REST API)

Part A — Triggers

Step 1 — Create Base Tables

CREATE TABLE employees (
    id     INT PRIMARY KEY,
    name   TEXT,
    salary FLOAT8
);

CREATE TABLE audit_log (
    id         INT PRIMARY KEY,
    table_name TEXT,
    action     TEXT,
    old_value  TEXT,
    new_value  TEXT,
    changed_at TEXT
);

Step 2 — Create a Trigger Function

Define a function body using PL/pgSQL that will run when the trigger fires.

CREATE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log VALUES (
        (SELECT COALESCE(MAX(id), 0) + 1 FROM audit_log),
        'employees',
        'UPDATE',
        CAST(OLD.salary AS TEXT),
        CAST(NEW.salary AS TEXT),
        '2025-03-26'
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Step 3 — Create a BEFORE Trigger

A BEFORE trigger runs before the row is modified. It can inspect and alter the NEW row, or prevent the operation by returning NULL.

CREATE TRIGGER salary_floor_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
WHEN (NEW.salary < 30000)
BEGIN
    SET NEW.salary = 30000;
END;

This trigger ensures no salary drops below 30,000.

Step 4 — Create an AFTER Trigger

An AFTER trigger runs after the row change has been applied. It is commonly used for auditing and side effects.

CREATE TRIGGER salary_audit_trigger
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log VALUES (
        (SELECT COALESCE(MAX(id), 0) + 1 FROM audit_log),
        'employees',
        'UPDATE',
        CAST(OLD.salary AS TEXT),
        CAST(NEW.salary AS TEXT),
        '2025-03-26'
    );
END;

Step 5 — Test the Triggers

INSERT INTO employees VALUES (1, 'Alice', 50000.00);
INSERT INTO employees VALUES (2, 'Bob',   40000.00);

-- This update fires both triggers:
-- 1. BEFORE: salary_floor_trigger (no-op since 60000 > 30000)
-- 2. AFTER: salary_audit_trigger (logs the change)
UPDATE employees SET salary = 60000.00 WHERE id = 1;

-- This update fires the BEFORE trigger to enforce the floor:
UPDATE employees SET salary = 20000.00 WHERE id = 2;

Check the audit log:

SELECT * FROM audit_log;

 id | table_name | action | old_value | new_value | changed_at
----+------------+--------+-----------+-----------+------------
  1 | employees  | UPDATE | 50000     | 60000     | 2025-03-26
  2 | employees  | UPDATE | 40000     | 30000     | 2025-03-26

Note that Bob’s salary was clamped to 30,000 by the BEFORE trigger.

Step 6 — Trigger Events

Triggers can fire on INSERT, UPDATE, or DELETE:

CREATE TRIGGER on_insert_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log VALUES (
        (SELECT COALESCE(MAX(id), 0) + 1 FROM audit_log),
        'employees', 'INSERT', NULL, NEW.name, '2025-03-26'
    );
END;

CREATE TRIGGER on_delete_trigger
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log VALUES (
        (SELECT COALESCE(MAX(id), 0) + 1 FROM audit_log),
        'employees', 'DELETE', OLD.name, NULL, '2025-03-26'
    );
END;

Trigger Cascading

Triggers can fire other triggers up to a maximum depth of 16 (PostgreSQL compatible). Exceeding this depth produces an error to prevent infinite loops.

Part B — PL/pgSQL Basics

Step 7 — Variables and Assignment

CREATE FUNCTION calculate_bonus(emp_salary FLOAT8)
RETURNS FLOAT8 AS $$
DECLARE
    bonus FLOAT8;
    rate  FLOAT8 := 0.10;
BEGIN
    bonus := emp_salary * rate;
    RETURN bonus;
END;
$$ LANGUAGE plpgsql;

Step 8 — IF / ELSIF / ELSE

CREATE FUNCTION salary_grade(salary FLOAT8)
RETURNS TEXT AS $$
DECLARE
    grade TEXT;
BEGIN
    IF salary >= 100000 THEN
        grade := 'Senior';
    ELSIF salary >= 60000 THEN
        grade := 'Mid';
    ELSE
        grade := 'Junior';
    END IF;
    RETURN grade;
END;
$$ LANGUAGE plpgsql;

Step 9 — FOR Loop (Numeric Range)

CREATE FUNCTION sum_range(low INT, high INT)
RETURNS INT AS $$
DECLARE
    total INT := 0;
    i     INT;
BEGIN
    FOR i IN low..high LOOP
        total := total + i;
    END LOOP;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

Step 10 — WHILE Loop

CREATE FUNCTION factorial(n INT)
RETURNS INT AS $$
DECLARE
    result INT := 1;
    i      INT := n;
BEGIN
    WHILE i > 1 LOOP
        result := result * i;
        i := i - 1;
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

Step 11 — CASE Statement

CREATE FUNCTION describe_status(code INT)
RETURNS TEXT AS $$
BEGIN
    CASE code
        WHEN 1 THEN RETURN 'Active';
        WHEN 2 THEN RETURN 'Inactive';
        WHEN 3 THEN RETURN 'Suspended';
        ELSE RETURN 'Unknown';
    END CASE;
END;
$$ LANGUAGE plpgsql;

Step 12 — Cursors

CREATE FUNCTION process_high_earners()
RETURNS INT AS $$
DECLARE
    emp RECORD;
    counter INT := 0;
BEGIN
    FOR emp IN SELECT * FROM employees WHERE salary > 50000 LOOP
        counter := counter + 1;
        -- Process each high earner
    END LOOP;
    RETURN counter;
END;
$$ LANGUAGE plpgsql;

The FOR emp IN SELECT ... syntax opens an implicit cursor, iterates through results, and closes the cursor automatically.

Step 13 — RAISE for Debugging and Errors

CREATE FUNCTION safe_divide(a FLOAT8, b FLOAT8)
RETURNS FLOAT8 AS $$
BEGIN
    IF b = 0 THEN
        RAISE EXCEPTION 'Division by zero';
    END IF;
    RAISE NOTICE 'Dividing % by %', a, b;
    RETURN a / b;
END;
$$ LANGUAGE plpgsql;

Tips and Troubleshooting

OLD vs NEW: In UPDATE triggers, OLD refers to the row before the change and NEW refers to the row after. INSERT triggers only have NEW; DELETE triggers only have OLD.
BEFORE vs AFTER: Use BEFORE to validate or modify data before it is written. Use AFTER for side effects like auditing or notifications.
Cascading depth: The maximum trigger cascade depth is 16. Design your triggers to avoid recursive chains.
Dialect support: HeliosDB also recognizes T-SQL, PL/SQL, and DB2 SQL PL syntax. The procedural engine auto-detects the dialect or you can specify it explicitly with LANGUAGE plpgsql, LANGUAGE tsql, etc.
Performance: Triggers add overhead to every DML operation on the target table. Use them judiciously on high-write tables.
Debugging: Use RAISE NOTICE to emit diagnostic messages during function execution. RAISE EXCEPTION aborts the current transaction.