42.9. 触发程序

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

42 .9.1. 触发数据更改

数据变更触发被声明为不带参数且返回类型为trigger的函数。请注意，即使函数希望接收CREATE TRIGGER中指定的某些参数，也必须声明为不带参数的函数-此类参数通过TG_ARGV传递，如下所述。

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

NEW
- 数据类型RECORD;变量保存行级触发器中INSERT/UPDATE操作的新数据库行。在语句级触发器和DELETE操作中未分配此变量。
OLD
- 数据类型RECORD;变量用于保存行级触发器中UPDATE/DELETE操作的旧数据库行。在语句级触发器和INSERT操作中未分配此变量。
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;引起触发器调用的表的对象 ID。
TG_RELNAME
- 数据类型name;引起触发器调用的表的名称。现在已弃用该功能，并且在将来的版本中可能会消失。请改用TG_TABLE_NAME。
TG_TABLE_NAME
- 数据类型name;引起触发器调用的表的名称。
TG_TABLE_SCHEMA
- 数据类型name;引起触发器调用的表的模式名称。
TG_NARGS
- 数据类型integer; CREATE TRIGGER语句中分配给触发过程的参数数量。
TG_ARGV[]
- text的数据类型数组； CREATE TRIGGER语句中的参数。索引从 0 开始计数。无效索引(小于 0 或大于或等于tg_nargs)将导致空值。

触发器函数必须返回NULL或具有准确触发触发器的表结构的记录/行值。

触发了BEFORE的行级触发器可以返回 null，以指示触发器 Management 器跳过该行的其余操作(即，不触发后续触发器，并且此行不发生INSERT/UPDATE/DELETE)。如果返回非空值，则操作以该行值 continue。返回与NEW的原始值不同的行值将更改将要插入或更新的行。因此，如果触发功能希望触发动作在不改变行值的情况下正常成功执行，则必须返回NEW(或等于它的值)。要更改要存储的行，可以直接在NEW中替换单个值并返回修改后的NEW，或者构建完整的新记录/行以返回。对于DELETE上的前触发，返回的值没有直接作用，但是必须为非 null 才能使触发动作 continue 进行。请注意，在DELETE触发器中NEW为 null，因此返回该值通常不明智。 DELETE触发器中的惯用法是返回OLD。

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

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

Example 42.3显示了 PL/pgSQL 中触发过程的示例。

例 42.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 PROCEDURE emp_stamp();

将更改记录到表的另一种方法涉及创建一个新表，该表在每次插入，更新或删除时都保留一行。可以将这种方法视为审核表的更改。 Example 42.4显示了 PL/pgSQL 中的审计触发过程的示例。

实施例 42.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(), 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 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 PROCEDURE process_emp_audit();

上一个示例的一个变体使用将主表连接到审计表的视图，以显示每个条目的最后修改时间。此方法仍将对表所做更改的完整审核跟踪记录到表中，而且还提供了审核跟踪的简化视图，仅显示了从每个条目的审核跟踪派生的最后修改的时间戳。 Example 42.5显示了在 PL/pgSQL 中的视图上的审计触发器的示例。

实施例 42.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', 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;
$$ LANGUAGE plpgsql;

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

触发器的一种用法是维护另一个表的摘要表。所得的摘要可用于代替某些查询的原始表-通常可以大大减少运行时间。此技术通常用于数据仓库中，其中测量或观察到的数据表(称为事实表)可能非常大。 Example 42.6显示了 PL/pgSQL 中的触发过程的示例，该过程为数据仓库中的事实表维护摘要表。

实施例 42.6. 维护汇总表的 PL/pgSQL 触发过程

此处详述的模式部分基于 Ralph Kimball 的* 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 PROCEDURE 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命令为一个或两个过渡表分配名称，然后函数可以引用这些名称，就好像它们是只读临时表一样。 Example 42.7显示了一个示例。

实施例 42.7. 使用过渡表进行审核

此示例产生的结果与Example 42.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(), 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 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 PROCEDURE 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 PROCEDURE process_emp_audit();
CREATE TRIGGER emp_audit_del
    AFTER DELETE ON emp
    REFERENCING OLD TABLE AS old_table
    FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();

42 .9.2. 事件触发

PL/pgSQL 可用于定义event triggers。 PostgreSQL 要求将被称为事件触发器的过程声明为不带参数且返回类型为event_trigger的函数。

当将 PL/pgSQL 函数称为事件触发器时，会在顶层块中自动创建几个特殊变量。他们是：

TG_EVENT
- 数据类型text;一个字符串，表示触发触发器的事件。
TG_TAG
- 数据类型text;变量，包含为其触发触发器的命令标签。

Example 42.8显示了 PL/pgSQL 中事件触发过程的示例。

实施例 42.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 PROCEDURE snitch();

Docs

Docs4dev

Title here

42.9. 触发程序

42 .9.1. 触发数据更改

42 .9.2. 事件触发