PolarDB:Trigger functions

Return values

A trigger function must return NULL or a record or row value that exactly matches the schema of the trigger table.

BEFORE row-level trigger

• Return NULL to skip the row — subsequent triggers are not fired, and the INSERT, UPDATE, or DELETE operation is not performed on that row.

• Return a non-NULL value to proceed. If the returned row differs from the original NEW value, the row to be inserted or updated is modified accordingly.

• To pass the row through unchanged, return the original NEW value or an equivalent.

• For DELETE operations, the return value has no direct effect but must be non-NULL for the trigger action to proceed. Returning OLD is the standard practice.

INSTEAD OF trigger

INSTEAD OF triggers are always row-level and can only be defined on views.

• Return NULL to indicate no update was performed. The row is skipped, subsequent triggers are not fired, and the row is not counted as affected by the surrounding INSERT, UPDATE, or DELETE operation.

• Return a non-NULL value to indicate the operation was performed. Return NEW for INSERT and UPDATE operations, and OLD for DELETE operations.

• Modifying NEW before returning it affects INSERT RETURNING, UPDATE RETURNING, and the EXCLUDED alias in INSERT ... ON CONFLICT DO UPDATE statements, as well as subsequent triggers.

AFTER row-level, BEFORE statement-level, and AFTER statement-level triggers

The return value is always ignored. Any of these triggers can abort the entire operation by raising an error.

Example: validate data and stamp timestamps

This trigger checks that empname and salary are not NULL, that salary is non-negative, and then stamps the current timestamp and username into the row on every INSERT or UPDATE.

CREATE TABLE emp (
    empname   text,
    salary    integer,
    last_date timestamp,
    last_user text
);

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW
BEGIN
    -- Verify that empname and salary are not NULL.
    IF NEW.empname IS NULL THEN
        RAISE EXCEPTION 'empname cannot be null';
    END IF;
    IF NEW.salary IS NULL THEN
        RAISE EXCEPTION '% cannot have null salary', NEW.empname;
    END IF;

    -- Reject negative salaries.
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
    END IF;

    -- Stamp the modification time and user.
    NEW.last_date := current_timestamp;
    NEW.last_user := current_user;
    RETURN NEW;
END;

Example: audit table changes with a row-level trigger

This trigger records every INSERT, UPDATE, and DELETE on the emp table by writing an audit row to emp_audit, including the operation type, timestamp, and username.

CREATE TABLE emp (
    empname text NOT NULL,
    salary  integer
);

CREATE TABLE emp_audit (
    operation char(1)   NOT NULL,
    stamp     timestamp NOT NULL,
    userid    text      NOT NULL,
    empname   text      NOT NULL,
    salary    integer
);

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW
BEGIN
    --
    -- Write a row to emp_audit for each operation on emp.
    -- Use TG_OP to determine which operation fired the trigger.
    --
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
    END IF;
    RETURN NULL; -- Result is ignored for AFTER triggers.
END;

Example: make a view updatable with an INSTEAD OF trigger

This example joins emp and emp_audit into a view that shows each employee's salary and the timestamp of their most recent change. An INSTEAD OF trigger handles INSERT, UPDATE, and DELETE operations on the view by writing to both underlying tables.

CREATE TABLE emp (
    empname text PRIMARY KEY,
    salary  integer
);

CREATE TABLE emp_audit (
    operation char(1)   NOT NULL,
    userid    text      NOT NULL,
    empname   text      NOT NULL,
    salary    integer,
    stamp     timestamp NOT NULL
);

CREATE VIEW emp_view AS
    SELECT e.empname,
           e.salary,
           max(ea.stamp) AS last_updated
      FROM emp e
      LEFT JOIN emp_audit ea ON ea.empname = e.empname
     GROUP BY 1, 2;

CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
    FOR EACH ROW
BEGIN
    --
    -- Route each operation to the emp table and record it in emp_audit.
    --
    IF (TG_OP = 'DELETE') THEN
        DELETE FROM emp WHERE empname = OLD.empname;
        IF NOT FOUND THEN RETURN NULL; END IF;

        OLD.last_updated = now();
        INSERT INTO emp_audit VALUES('D', user, OLD.*);
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
        IF NOT FOUND THEN RETURN NULL; END IF;

        NEW.last_updated = now();
        INSERT INTO emp_audit VALUES('U', user, NEW.*);
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO emp VALUES(NEW.empname, NEW.salary);

        NEW.last_updated = now();
        INSERT INTO emp_audit VALUES('I', user, NEW.*);
        RETURN NEW;
    END IF;
END;

Example: maintain a summary table

This example (adapted from the Grocery Store example in *The Data Warehouse Toolkit* by Ralph Kimball) maintains a sales_summary_bytime table that aggregates rows from sales_fact. The trigger keeps the summary table in sync with every INSERT, UPDATE, or DELETE on sales_fact, avoiding a full table scan on every query.

-- Main dimension and fact tables.
CREATE TABLE time_dimension (
    time_key      integer NOT NULL,
    day_of_week   integer NOT NULL,
    day_of_month  integer NOT NULL,
    month         integer NOT NULL,
    quarter       integer NOT NULL,
    year          integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (
    time_key    integer        NOT NULL,
    product_key integer        NOT NULL,
    store_key   integer        NOT NULL,
    amount_sold numeric(12,2)  NOT NULL,
    units_sold  integer        NOT NULL,
    amount_cost numeric(12,2)  NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);

-- Summary table.
CREATE TABLE sales_summary_bytime (
    time_key    integer        NOT NULL,
    amount_sold numeric(15,2)  NOT NULL,
    units_sold  numeric(12)    NOT NULL,
    amount_cost numeric(15,2)  NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

-- Trigger to keep the summary table in sync.
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
    FOR EACH ROW
DECLARE
    delta_time_key    integer;
    delta_amount_sold numeric(15,2);
    delta_units_sold  numeric(12);
    delta_amount_cost numeric(15,2);
BEGIN
    -- Calculate the delta to apply to the summary table.
    IF (TG_OP = 'DELETE') THEN
        delta_time_key    = OLD.time_key;
        delta_amount_sold = -1 * OLD.amount_sold;
        delta_units_sold  = -1 * OLD.units_sold;
        delta_amount_cost = -1 * OLD.amount_cost;

    ELSIF (TG_OP = 'UPDATE') THEN
        -- Prevent updates that change time_key.
        -- Most changes are performed using DELETE and INSERT instead.
        IF (OLD.time_key != NEW.time_key) THEN
            RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
                            OLD.time_key, NEW.time_key;
        END IF;

        delta_time_key    = OLD.time_key;
        delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
        delta_units_sold  = NEW.units_sold  - OLD.units_sold;
        delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

    ELSIF (TG_OP = 'INSERT') THEN
        delta_time_key    = NEW.time_key;
        delta_amount_sold = NEW.amount_sold;
        delta_units_sold  = NEW.units_sold;
        delta_amount_cost = NEW.amount_cost;
    END IF;

    -- Update the existing summary row, or insert a new one.
    <<insert_update>>
    LOOP
        UPDATE sales_summary_bytime
            SET amount_sold = amount_sold + delta_amount_sold,
                units_sold  = units_sold  + delta_units_sold,
                amount_cost = amount_cost + delta_amount_cost
            WHERE time_key = delta_time_key;

        EXIT insert_update WHEN found;

        BEGIN
            INSERT INTO sales_summary_bytime (
                        time_key,
                        amount_sold,
                        units_sold,
                        amount_cost)
                VALUES (
                        delta_time_key,
                        delta_amount_sold,
                        delta_units_sold,
                        delta_amount_cost);

            EXIT insert_update;

        EXCEPTION
            WHEN UNIQUE_VIOLATION THEN
                -- Another concurrent session inserted the row. Retry the UPDATE.
        END;
    END LOOP insert_update;

    RETURN NULL;
END;

-- Test the trigger.
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;

Example: audit table changes with transition tables

AFTER triggers can use transition tables to access the complete set of rows affected by a single statement. This is significantly faster than a row-level trigger when a single statement modifies many rows, because the trigger fires once per statement rather than once per row.

The REFERENCING clause assigns names to transition tables. The trigger function then queries those names as read-only temporary tables.

The REFERENCING clause syntax differs for each event type (INSERT, UPDATE, DELETE). Define a separate trigger for each event type. A single shared function works, but using three separate functions avoids testing TG_OP inside the function body.

CREATE TABLE emp (
    empname text NOT NULL,
    salary  integer
);

CREATE TABLE emp_audit (
    operation char(1)   NOT NULL,
    stamp     timestamp NOT NULL,
    userid    text      NOT NULL,
    empname   text      NOT NULL,
    salary    integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURN TRIGGER IS
BEGIN
    --
    -- Write rows to emp_audit for all rows affected by the statement.
    -- Use TG_OP to determine which operation fired the trigger.
    --
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO emp_audit
            SELECT 'D', now(), user, o.* FROM old_table o;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO emp_audit
            SELECT 'U', now(), user, n.* FROM new_table n;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO emp_audit
            SELECT 'I', now(), user, n.* FROM new_table n;
    END IF;
    RETURN NULL; -- Result is ignored for AFTER triggers.
END;

CREATE TRIGGER emp_audit_ins
    AFTER INSERT ON emp
    REFERENCING NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

CREATE TRIGGER emp_audit_upd
    AFTER UPDATE ON emp
    REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

CREATE TRIGGER emp_audit_del
    AFTER DELETE ON emp
    REFERENCING OLD TABLE AS old_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

Example: log DDL commands with an event trigger

This trigger raises a NOTICE message each time a supported DDL command runs, logging the event name and command tag.

CREATE OR REPLACE FUNCTION snitch() RETURN event_trigger IS
BEGIN
    RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;

CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();