PostgreSQL 16 文档: 43.10. 触发器函数

43.10. 触发器函数
上一个	向上	第 43 章 PL/pgSQL — SQL 过程语言	主页	下一个

43.10. 触发器函数 #

43.10.1. 数据更改触发器
43.10.2. 事件触发器

PL/pgSQL 可用于定义数据更改或数据库事件触发器函数。使用 CREATE FUNCTION 命令创建触发器函数，将其声明为无参数函数，返回类型为 trigger（对于数据更改触发器）或 event_trigger（对于数据库事件触发器）。自动定义名为 TG_something 的特殊局部变量，以描述触发调用的条件。

43.10.1. 数据更改触发器 #

数据更改触发器声明为无参数函数，返回类型为 trigger。请注意，即使函数期望接收 CREATE TRIGGER 中指定的一些参数，也必须将函数声明为无参数——此类参数通过 TG_ARGV 传递，如下所述。

当 PL/pgSQL 函数作为触发器调用时，在顶级块中自动创建几个特殊变量。它们是

NEW record #: 行级触发器中 INSERT/UPDATE 操作的新数据库行。在语句级触发器和 DELETE 操作中，此变量为 null。
OLD record #: 行级触发器中 UPDATE/DELETE 操作的旧数据库行。在语句级触发器和 INSERT 操作中，此变量为 null。
TG_NAME name #: 触发的名称。
TG_WHEN text #: BEFORE、AFTER 或 INSTEAD OF，具体取决于触发器的定义。
TG_LEVEL text #: ROW 或 STATEMENT，具体取决于触发器的定义。
TG_OP text #: 触发器触发的操作：INSERT、UPDATE、DELETE 或 TRUNCATE。
TG_RELID oid（引用 pg_class.oid） #: 导致触发器调用的表的对象 ID。
TG_RELNAME name #: 导致触发器调用的表。此项现已弃用，可能会在未来版本中消失。请改用 TG_TABLE_NAME。
TG_TABLE_NAME 名称 #: 触发器调用的表。
TG_TABLE_SCHEMA 名称 #: 触发器调用的表的架构。
TG_NARGS 整数 #: CREATE TRIGGER 语句中给触发器函数的参数数量。
TG_ARGV 文本[] #: CREATE TRIGGER 语句中的参数。索引从 0 开始。无效索引（小于 0 或大于或等于 tg_nargs）导致空值。

触发器函数必须返回 NULL 或与触发器触发的表结构完全相同的记录/行值。

在 BEFORE 触发的行级触发器可以返回空值，以向触发器管理器发出信号，跳过此行的其余操作（即，后续触发器不会触发，并且此行的 INSERT/UPDATE/DELETE 不会发生）。如果返回非空值，则操作将使用该行值继续进行。返回与 NEW 的原始值不同的行值会更改将要插入或更新的行。因此，如果触发器函数希望触发操作正常成功而不会更改行值，则必须返回 NEW（或等于它的值）。要更改要存储的行，可以在 NEW 中直接替换单个值并返回修改后的 NEW，或构建一个完整的新记录/行以返回。对于 DELETE 上的 before 触发器，返回的值没有直接影响，但它必须是非空值才能允许触发器操作继续进行。请注意，NEW 在 DELETE 触发器中为 null，因此返回它通常不合理。在 DELETE 触发器中通常的做法是返回 OLD。

INSTEAD OF 触发器（始终是行级触发器，且只能用于视图）可以返回 null 以表示它们未执行任何更新，并且应跳过此行的其余操作（即，不会触发后续触发器，并且该行不会计入周围 INSERT/UPDATE/DELETE 的受影响行状态）。否则，应返回非空值，以表示触发器执行了请求的操作。对于 INSERT 和 UPDATE 操作，返回值应为 NEW，触发器函数可以修改它以支持 INSERT RETURNING 和 UPDATE RETURNING（这也将影响传递给任何后续触发器的行值，或传递给 INSERT 语句中带有 ON CONFLICT DO UPDATE 子句的特殊 EXCLUDED 别名引用）。对于 DELETE 操作，返回值应为 OLD。

在 AFTER 之后触发的行级触发器或在 BEFORE 或 AFTER 之后触发的语句级触发器的返回值始终被忽略；它也可能是 null。但是，任何这些类型的触发器都可能通过引发错误来中止整个操作。

示例 43.3 展示了 PL/pgSQL 中的触发器函数示例。

示例 43.3. PL/pgSQL 触发器函数

此示例触发器确保每次在表中插入或更新行时，都会将当前用户名和时间戳记到该行中。它还会检查是否提供了员工姓名，以及薪水是否为正值。

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

记录表中更改的另一种方法是创建一个新表，其中包含发生每次插入、更新或删除的行。这种方法可以被认为是对表中更改的审计。示例 43.4 展示了 PL/pgSQL 中的审计触发器函数示例。

示例 43.4. 用于审计的 PL/pgSQL 触发器函数

此示例触发器确保 emp 表中行的任何插入、更新或删除都会在 emp_audit 表中记录（即审计）。当前时间和用户名将被戳记到该行中，以及对该行执行的操作类型。

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

前一个示例的一个变体使用将主表连接到审计表的视图，以显示每个条目上次修改的时间。此方法仍然记录对表的更改的完整审计跟踪，但还显示了审计跟踪的简化视图，仅显示从审计跟踪中派生的每个条目的最后修改时间戳。示例 43.5 展示了 PL/pgSQL 中视图上的审计触发器示例。

示例 43.5。用于审计的 PL/pgSQL 视图触发器函数

此示例使用视图上的触发器使其可更新，并确保视图中行的任何插入、更新或删除都记录在 emp_audit 表中（即审计）。当前时间和用户名与执行的操作类型一起记录，视图显示每行的上次修改时间。

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

触发器的一种用途是维护另一个表的汇总表。生成的汇总可用于某些查询来代替原始表——通常可以大幅减少运行时间。此技术通常用于数据仓库中，其中度量或观察数据（称为事实表）的表可能非常大。示例 43.6 展示了 PL/pgSQL 中的触发器函数示例，该函数维护数据仓库中事实表的汇总表。

示例 43.6。用于维护汇总表的 PL/pgSQL 触发器函数

此处详细介绍的架构部分基于 Ralph Kimball 的 The Data Warehouse Toolkit 中的 Grocery Store 示例。

--
-- 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 触发器还可以利用过渡表来检查触发语句更改的整行集。 CREATE TRIGGER 命令为一个或两个过渡表分配名称，然后函数可以引用这些名称，就好像它们是只读临时表一样。示例 43.7 展示了一个示例。

示例 43.7。使用过渡表进行审计

此示例生成的结果与示例 43.4 相同，但它没有使用针对每行触发的触发器，而是使用在过渡表中收集相关信息后针对每个语句触发一次的触发器。当调用语句修改了许多行时，这可能比行触发器方法快得多。请注意，我们必须为每种事件类型进行单独的触发器声明，因为 REFERENCING 子句在每种情况下都必须不同。但这并不会阻止我们在选择时使用单个触发器函数。（实际上，最好使用三个单独的函数并避免对 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();

43.10.2. 事件触发器 #

PL/pgSQL 可用于定义事件触发器。 PostgreSQL 要求作为事件触发器调用的函数必须声明为无参数且返回类型为 event_trigger 的函数。

当 PL/pgSQL 函数作为事件触发器调用时，将在顶级块中自动创建几个特殊变量。它们是

TG_EVENT text #: 触发器触发的事件。
TG_TAG text #: 触发器触发的命令标记。

示例 43.8 显示了 PL/pgSQL 中事件触发器函数的示例。

示例 43.8. PL/pgSQL 事件触发器函数

此示例触发器每次执行受支持的命令时都会简单地引发 NOTICE 消息。

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