PostgreSQL 9.4.4 中文手册
上一页	上一级	章 40. PL/pgSQL - SQL过程语言	下一页

40.9. 触发器过程

40.9.1. 对数据变化的触发

PL/pgSQL可以用于定义触发器过程。一个触发器过程是用CREATE FUNCTION命令创建的，创建的形式是一个不接受参数并且返回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；是激活触发器调用的表的对象标识(OID)。
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值。

一个触发器函数必须返回NULL或者是一个与激活触发器运行的表的记录/行结构完全相同的数据。

因BEFORE触发的行级别触发器可以返回一个NULL，告诉触发器管理器忽略对该行剩下的操作，也就是说，随后的触发器将不再执行，并且不会对该行产生INSERT/UPDATE/DELETE动作)。如果返回了一个非NULL的行，那么将继续对该行数值进行处理。请注意，返回一个和原来的NEW不同的行数值将修改那个将插入或更新的行因此，如果想在没有修改行值的同时成功的执行触发器动作，那么需要返回NEW（或等价的）。为了修改行存储，可以用一个值直接代替NEW里的某个数值并且返回之，或者也可以构建一个全新的记录/行再返回。在DELETE上的before触发器的情况下，返回值没有直接的影响，但是它不得不是非null以允许触发器操作继续执行。请注意DELETE触发器中NEW是null，因此返回往往是不明智的。DELETE触发器通常情况返回OLD。

INSTEAD OF触发器（总是行级触发器，并且只能用于视图）可以返回null标记他们不执行任何更新，并且应该忽略这些行操作的剩余部分（比如，随后的触发器不会被触发，并且为了周围的INSERT/UPDATE/DELETE在受影响的行状态下不计算行）。另外应该返回一个空值，用来标记触发器执行所需要的操作。为了 INSERT和UPDATE操作，返回值应是NEW，这个触发器函数可以修改以支持INSERT RETURNING和UPDATE RETURNING （这也将影响传递到任何随后触发器的行值）。为了DELETE操作，返回值应是OLD。

一个AFTER行级别的触发器或者 BEFORE或者AFTER语句级别的触发器返回值将总是被忽略；它们也可以返回NULL来忽略返回值。不过，任何这种类型的触发器仍然可以通过抛出一个错误来退出整个触发器操作。

例 40-3显示了一个PL/pgSQL 写的触发器过程的例子。

例 40-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
    
-- 检查是否给出了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 PROCEDURE emp_stamp();

另外一个向表里记录变化的方法涉及创建一个新表，然后为后来发生的每次插入、更新或者删除动作保存一行。这个方法可以当作对一个表的审计。例 40-4显示了一个PL/pgSQL写的审计触发器过程的例子。

例 40-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
    
--
        -- 在emp_audit里创建一行，反映对emp的操作，
        -- 使用特殊变量TG_OP获取操作类型。
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
            RETURN 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();

先前例子的一个变化使用连接主表到审计表的视图，显示上次修改的每个项。这个方法还记录了改变表的完整审计追踪，但是也提出了审计追踪的简单视图，显示来源于每项审计追踪的最后修改的时间戳。例 40-5显示了PL/pgSQL 中视图上的审计触发器的例子。

例 40-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
    
--
        -- 在emp上执行所需操作，并且在emp_audit中创建一行以反映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();

触发器的一个用途是维持另外一个表的概要。生成的概要可以用于在某些查询中代替原始表(通常可以大大缩小运行时间)。这个技巧经常用于数据仓库，这个时候，需要测量的表(叫事实表)可能会非常巨大。例 40-6演示了一个 PL/pgSQL触发器过程的例子，它为某个数据仓库的一个事实表维护一个概要表。

例 40-6. 一个维护概要表的PL/pgSQL触发器过程

下面的模式有一部分是基于数据仓库工具里面的Grocery Store例子。

--
-- 主表 - 时间维以及销售事实。 
--
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);
--
-- 摘要表-根据时间的销售。
--
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);
--
-- 在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
        
 -- 计算增/减量
        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
            
-- 禁止改变 time_key 的更新
            -- (可能并不是很强制，因为 DELETE + INSERT 是大多数可能产生的修改)。
            
            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_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;

40.9.2. 事件触发器

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

当PL/pgSQL函数作为事件触发器调用时，在顶层自动创建一些特殊变量，他们是：

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

例 40-7显示PL/pgSQL中的事件触发器程序例子。

例 40-7. 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();

上一页	起始页	下一页
错误和消息	上一级	在后台下的PL/pgSQL