40.9. 触发器过程 · PostgreSQL 中文文档 9.3

# 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来忽略返回值。不过，任何这种类型的触发器仍然可以通过抛出一个错误来退出整个触发器操作。 [Example 40-3](#calibre_link-1585)显示了一个PL/pgSQL 写的触发器过程的例子。 **Example 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(); ``` 另外一个向表里记录变化的方法涉及创建一个新表，然后为后来发生的每次插入、更新或者删除动作保存一行。这个方法可以当作对一个表的审计。 [Example 40-4](#calibre_link-1586)显示了一个PL/pgSQL写的审计触发器过程的例子。 **Example 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(); ``` 先前例子的一个变化使用连接主表到审计表的视图，显示上次修改的每个项。这个方法还记录了改变表的完整审计追踪，但是也提出了审计追踪的简单视图，显示来源于每项审计追踪的最后修改的时间戳。 [Example 40-5](#calibre_link-1587)显示了PL/pgSQL 中视图上的审计触发器的例子。 **Example 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(); ``` 触发器的一个用途是维持另外一个表的概要。生成的概要可以用于在某些查询中代替原始表(通常可以大大缩小运行时间)。这个技巧经常用于数据仓库，这个时候，需要测量的表(叫事实表)可能会非常巨大。 [Example 40-6](#calibre_link-1588)演示了一个 PL/pgSQL触发器过程的例子，它为某个数据仓库的一个事实表维护一个概要表。 **Example 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`;包含命令标签的变量触发的触发器。 [Example 40-7](#calibre_link-1589)显示PL/pgSQL中的事件触发器程序例子。 **Example 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(); ```