pg触发器

2021/07/06

触发器

一个触发器声明了当执行一种特定类型的操作时数据库应该自动执行一个特殊的函数。触发器可以被附加到表（分区的或者不分区的）、视图和外部表。在表和外部表上，触发器可以被定义为在 INSERT、UPDATE 或 DELETE 操作之前或之后被执行，可以为每个 SQL 语句被执行一次或者为每个修改的行被执行一次。UPDATE 触发器可以进一步地设置为只针对 UPDATE 语句的 SET 子句的特定列出发。触发器也可以被 TRUNCATE 语句触发。如果一个触发器事件发生，触发器函数会在适当的事件被调用来处理该事件。

触发器函数必须在触发器本身被创建之前被定义好。触发器函数必须被定义成一个没有参数的函数，并且返回类型为 trigger（触发器函数通过一个特殊传递的 TriggerData 结构作为其输入，而不是以普通函数参数的形式）。一旦一个合适的触发器函数被创建，就可以使用 CREATE TRIGGER 建立触发器。同一个触发器函数可以被用于多个触发器。

触发器函数规则

当一个 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；是导致触发器调用的表的对象 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 或者是一个与触发器为之引发的表结构完全相同的记录/行值。

一个 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
        -- 检查给出了 empname 以及 salary
        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;

        -- 谁会倒贴钱为我们工作？
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- 记住谁在什么时候改变了工资单
        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();

这个例子触发器保证了在 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
        --
        -- 在 emp_audit 中创建一行来反映 emp 上执行的动作，
        -- 使用特殊变量 TG_OP 来得到操作。
        --
        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; -- 因为这是一个 AFTER 触发器，结果被忽略
    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();