触发器过程
触发器过程
PL/pgSQL 可以用于定义触发器过程。 一个触发器过程是用 CREATE FUNCTION 命令创建的， 创建的形式是一个不接受参数并且返回 trigger 类型的函数。 请注意该函数即使在 CREATE TRIGGER 声明里声明为准备接受参数， 它也必需声明为无参数 — 触发器的参数是通过 TG_ARGV 传递的，下面有描述。
在一个 PL/pgSQL 函数当做触发器调用的时候， 系统会在顶层的声明段里自动创建几个特殊变量。有如下这些：
?
?
NEW数据类型是 RECORD； 该变量为INSERT/UPDATE 操作时保存行（ROW）一级的触发器新的数据库行。 在语句级别的触发器里，这个变量是 NULL。
OLD数据类型是 RECORD； 该变量为 INSERT/UPDATE 操作时保存行（ROW）一级的触发器新的数据库行。 在语句级别的触发器里，这个变量是 NULL。
TG_NAME数据类型是 name；该变量包含实际触发的触发器名。 fired.
TG_WHEN数据类型是 text；是一个由触发器定义决定的字符串， 要么是 BEFORE 要么是 AFTER。
TG_LEVEL数据类型是 text；是一个由触发器定义决定的字符串， 要么是 ROW 要么是 STATEMENT。
TG_OP数据类型是 text；是一个说明触发触发器的操作的字符串， 可以是 INSERT，UPDATE 或者 DELETE。
TG_RELID数据类型是 oid；是导致触发器调用的表的对象标识（OID）。
TG_RELNAME数据类型是 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里的某个数值并且返回之，或者我们也可以构建一个完全新的记录/行再返回。
BEFORE 或者 AFTER语句级别的触发器， 或者一个AFTER 行级别的触发器的返回值将总是被忽略； 它们也可以返回 NULL 来忽略返回值。不过，任何这种类型的触发器仍然可以 通过抛出一个错误来退出整个触发器操作。
Example 36-2 显示了一个 PL/pgSQL 写的触发器过程的例子。
Example 36-2. 一个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 和 salaryIF NEW.empname ISNULL THENRAISE EXCEPTION 'empname cannot be null';END IF;IF NEW.salary ISNULL THENRAISE EXCEPTION '% cannot have null salary', NEW.empname;END IF;-- 我们必须付帐给谁？IF NEW.salary < 0 THENRAISE 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 empFOR EACH ROW EXECUTE PROCEDURE emp_stamp();
另外一个向表里记录变化的方法涉及创建一个新表，然后为后来发生的每次插入、更新或者删除动作保存一行。 这个方法可以当作对一个表的审计。 Example 36-3 显示了一个 PL/pgSQL 写的审计触发器过程的例子。
Example 36-3. 一个用于审计的 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') THENINSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;RETURN OLD;ELSIF (TG_OP = 'UPDATE') THENINSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;RETURN NEW;END IF;RETURN NULL; -- 忽略结果，因为它是个 AFTER 触发器END;$emp_audit$ LANGUAGE plpgsql;CREATE TRIGGER emp_auditAFTER INSERT OR UPDATE OR DELETE ON empFOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
触发器的一个用途是维持另外一个表的概要。生成的概要可以用于在某些查询中代替原始表 — 通常可以大大缩小运行时间。 这个技巧经常用于数据仓库，这个时候，需要测量的表（叫事实表）可能会非常巨大。 Example 36-4 演示了一个 PL/pgSQL 触发器过程的例子， 它为某个数据仓库的一个事实表维护一个概要表。
Example 36-4. 一个维护概要表的 PL/pgSQL 触发器过程
下面的模式有一部分是基于 Ralph Kimball 的The Data Warehouse Toolkit 里面的 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$DECLAREdelta_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') THENdelta_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) THENRAISE 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') THENdelta_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;-- 用新数值更新概要行。UPDATE sales_summary_bytimeSET amount_sold = amount_sold + delta_amount_sold,units_sold = units_sold + delta_units_sold,amount_cost = amount_cost + delta_amount_costWHERE time_key = delta_time_key;-- There might have been no row with this time_key (e.g new data!).IF (NOT FOUND) THENBEGININSERT 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);EXCEPTION---- 捕获两个事务维一个新 time_key 增加数据的冲突条件--WHEN UNIQUE_VIOLATION THENUPDATE sales_summary_bytimeSET amount_sold = amount_sold + delta_amount_sold,units_sold = units_sold + delta_units_sold,amount_cost = amount_cost + delta_amount_costWHERE time_key = delta_time_key;END;END IF;RETURN NULL;END;$maint_sales_summary_bytime$ LANGUAGE plpgsql;CREATE TRIGGER maint_sales_summary_bytimeAFTER INSERT OR UPDATE OR DELETE ON sales_factFOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
