Convert HTML docs to more streamlined TXT

[ai-pg] / full-docs / txt / plpgsql-trigger.txt

41.10. Trigger Functions #

   41.10.1. Triggers on Data Changes
   41.10.2. Triggers on Events

   PL/pgSQL can be used to define trigger functions on data changes or
   database events. A trigger function is created with the CREATE FUNCTION
   command, declaring it as a function with no arguments and a return type
   of trigger (for data change triggers) or event_trigger (for database
   event triggers). Special local variables named TG_something are
   automatically defined to describe the condition that triggered the
   call.

41.10.1. Triggers on Data Changes #

   A data change trigger is declared as a function with no arguments and a
   return type of trigger. Note that the function must be declared with no
   arguments even if it expects to receive some arguments specified in
   CREATE TRIGGER — such arguments are passed via TG_ARGV, as described
   below.

   When a PL/pgSQL function is called as a trigger, several special
   variables are created automatically in the top-level block. They are:

   NEW record #
          new database row for INSERT/UPDATE operations in row-level
          triggers. This variable is null in statement-level triggers and
          for DELETE operations.

   OLD record #
          old database row for UPDATE/DELETE operations in row-level
          triggers. This variable is null in statement-level triggers and
          for INSERT operations.

   TG_NAME name #
          name of the trigger which fired.

   TG_WHEN text #
          BEFORE, AFTER, or INSTEAD OF, depending on the trigger's
          definition.

   TG_LEVEL text #
          ROW or STATEMENT, depending on the trigger's definition.

   TG_OP text #
          operation for which the trigger was fired: INSERT, UPDATE,
          DELETE, or TRUNCATE.

   TG_RELID oid (references pg_class.oid) #
          object ID of the table that caused the trigger invocation.

   TG_RELNAME name #
          table that caused the trigger invocation. This is now
          deprecated, and could disappear in a future release. Use
          TG_TABLE_NAME instead.

   TG_TABLE_NAME name #
          table that caused the trigger invocation.

   TG_TABLE_SCHEMA name #
          schema of the table that caused the trigger invocation.

   TG_NARGS integer #
          number of arguments given to the trigger function in the CREATE
          TRIGGER statement.

   TG_ARGV text[] #
          arguments from the CREATE TRIGGER statement. The index counts
          from 0. Invalid indexes (less than 0 or greater than or equal to
          tg_nargs) result in a null value.

   A trigger function must return either NULL or a record/row value having
   exactly the structure of the table the trigger was fired for.

   Row-level triggers fired BEFORE can return null to signal the trigger
   manager to skip the rest of the operation for this row (i.e.,
   subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does
   not occur for this row). If a nonnull value is returned then the
   operation proceeds with that row value. Returning a row value different
   from the original value of NEW alters the row that will be inserted or
   updated. Thus, if the trigger function wants the triggering action to
   succeed normally without altering the row value, NEW (or a value equal
   thereto) has to be returned. To alter the row to be stored, it is
   possible to replace single values directly in NEW and return the
   modified NEW, or to build a complete new record/row to return. In the
   case of a before-trigger on DELETE, the returned value has no direct
   effect, but it has to be nonnull to allow the trigger action to
   proceed. Note that NEW is null in DELETE triggers, so returning that is
   usually not sensible. The usual idiom in DELETE triggers is to return
   OLD.

   INSTEAD OF triggers (which are always row-level triggers, and may only
   be used on views) can return null to signal that they did not perform
   any updates, and that the rest of the operation for this row should be
   skipped (i.e., subsequent triggers are not fired, and the row is not
   counted in the rows-affected status for the surrounding
   INSERT/UPDATE/DELETE). Otherwise a nonnull value should be returned, to
   signal that the trigger performed the requested operation. For INSERT
   and UPDATE operations, the return value should be NEW, which the
   trigger function may modify to support INSERT RETURNING and UPDATE
   RETURNING (this will also affect the row value passed to any subsequent
   triggers, or passed to a special EXCLUDED alias reference within an
   INSERT statement with an ON CONFLICT DO UPDATE clause). For DELETE
   operations, the return value should be OLD.

   The return value of a row-level trigger fired AFTER or a
   statement-level trigger fired BEFORE or AFTER is always ignored; it
   might as well be null. However, any of these types of triggers might
   still abort the entire operation by raising an error.

   Example 41.3 shows an example of a trigger function in PL/pgSQL.

   Example 41.3. A PL/pgSQL Trigger Function

   This example trigger ensures that any time a row is inserted or updated
   in the table, the current user name and time are stamped into the row.
   And it checks that an employee's name is given and that the salary is a
   positive value.
CREATE TABLE emp (
    empname           text,
    salary            integer,
    last_date         timestamp,
    last_user         text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Check that empname and salary are given
        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;

        -- Who works for us when they must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE FUNCTION emp_stamp();

   Another way to log changes to a table involves creating a new table
   that holds a row for each insert, update, or delete that occurs. This
   approach can be thought of as auditing changes to a table. Example 41.4
   shows an example of an audit trigger function in PL/pgSQL.

   Example 41.4. A PL/pgSQL Trigger Function for Auditing

   This example trigger ensures that any insert, update or delete of a row
   in the emp table is recorded (i.e., audited) in the emp_audit table.
   The current time and user name are stamped into the row, together with
   the type of operation performed on it.
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() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Create a row in emp_audit to reflect the operation performed on emp,
        -- making use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE FUNCTION process_emp_audit();

   A variation of the previous example uses a view joining the main table
   to the audit table, to show when each entry was last modified. This
   approach still records the full audit trail of changes to the table,
   but also presents a simplified view of the audit trail, showing just
   the last modified timestamp derived from the audit trail for each
   entry. Example 41.5 shows an example of an audit trigger on a view in
   PL/pgSQL.

   Example 41.5. A PL/pgSQL View Trigger Function for Auditing

   This example uses a trigger on the view to make it updatable, and
   ensure that any insert, update or delete of a row in the view is
   recorded (i.e., audited) in the emp_audit table. The current time and
   user name are recorded, together with the type of operation performed,
   and the view displays the last modified time of each row.
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 OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
    BEGIN
        --
        -- Perform the required operation on emp, and create a row in emp_audit
        -- to reflect the change made to emp.
        --
        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', current_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', current_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', current_user, NEW.*);
            RETURN NEW;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
    FOR EACH ROW EXECUTE FUNCTION update_emp_view();

   One use of triggers is to maintain a summary table of another table.
   The resulting summary can be used in place of the original table for
   certain queries — often with vastly reduced run times. This technique
   is commonly used in Data Warehousing, where the tables of measured or
   observed data (called fact tables) might be extremely large.
   Example 41.6 shows an example of a trigger function in PL/pgSQL that
   maintains a summary table for a fact table in a data warehouse.

   Example 41.6. A PL/pgSQL Trigger Function for Maintaining a Summary
   Table

   The schema detailed here is partly based on the Grocery Store example
   from The Data Warehouse Toolkit by Ralph Kimball.
--
-- Main tables - time dimension and sales fact.
--
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 - sales by time.
--
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);

--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
    DECLARE
        delta_time_key          integer;
        delta_amount_sold       numeric(15,2);
        delta_units_sold        numeric(12);
        delta_amount_cost       numeric(15,2);
    BEGIN

        -- Work out the increment/decrement amount(s).
        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

            -- forbid updates that change the time_key -
            -- (probably not too onerous, as DELETE + INSERT is how most
            -- changes will be made).
            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;


        -- Insert or update the summary row with the new values.
        <<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
                    -- do nothing
            END;
        END LOOP insert_update;

        RETURN NULL;

    END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
    FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime();

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;

   AFTER triggers can also make use of transition tables to inspect the
   entire set of rows changed by the triggering statement. The CREATE
   TRIGGER command assigns names to one or both transition tables, and
   then the function can refer to those names as though they were
   read-only temporary tables. Example 41.7 shows an example.

   Example 41.7. Auditing with Transition Tables

   This example produces the same results as Example 41.4, but instead of
   using a trigger that fires for every row, it uses a trigger that fires
   once per statement, after collecting the relevant information in a
   transition table. This can be significantly faster than the row-trigger
   approach when the invoking statement has modified many rows. Notice
   that we must make a separate trigger declaration for each kind of
   event, since the REFERENCING clauses must be different for each case.
   But this does not stop us from using a single trigger function if we
   choose. (In practice, it might be better to use three separate
   functions and avoid the run-time tests on TG_OP.)
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() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Create rows in emp_audit to reflect the operations performed on emp,
        -- making use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit
                SELECT 'D', now(), current_user, o.* FROM old_table o;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit
                SELECT 'U', now(), current_user, n.* FROM new_table n;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit
                SELECT 'I', now(), current_user, n.* FROM new_table n;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

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();

41.10.2. Triggers on Events #

   PL/pgSQL can be used to define event triggers. PostgreSQL requires that
   a function that is to be called as an event trigger must be declared as
   a function with no arguments and a return type of event_trigger.

   When a PL/pgSQL function is called as an event trigger, several special
   variables are created automatically in the top-level block. They are:

   TG_EVENT text #
          event the trigger is fired for.

   TG_TAG text #
          command tag for which the trigger is fired.

   Example 41.8 shows an example of an event trigger function in PL/pgSQL.

   Example 41.8. A PL/pgSQL Event Trigger Function

   This example trigger simply raises a NOTICE message each time a
   supported command is executed.
CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
    RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;

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