pg触发器

触发器

一个触发器声明了当执行一种特定类型的操作时数据库应该自动执行一个特殊的函数。触发器可以被附加到表(分区的或者不分区的)、视图和外部表。 在表和外部表上,触发器可以被定义为在 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();