| PostgreSQL 9.0.4 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 39. PL/pgSQL - SQL过程语言 | Fast Forward | Next |
PL/pgSQL可以用于定义触发器过程。 一个触发器过程是用CREATE FUNCTION命令创建的， 创建的形式是一个不接受参数并且返回trigger类型的函数。 请注意该函数即使在CREATE TRIGGER声明里声明为准备接受参数， 它也必需声明为无参数，因为触发器的参数是通过TG_ARGV传递的(下面有描述)。
在一个PL/pgSQL函数当做触发器调用的时候，系统会在顶层的声明段里自动创建几个特殊变量。 有如下这些：
数据类型是RECORD；该变量为行级触发器中的INSERT/UPDATE操作存储新数据行。 在语句级别的触发器里以及对INSERT动作，这个变量是NULL。
数据类型是RECORD；该变量为行级触发器中的UPDATE/DELETE操作存储旧数据行。 在语句级别的触发器里以及对INSERT动作，这个变量是NULL。
数据类型是name；该变量包含实际触发的触发器名。
数据类型是text；是一个由触发器定义决定的字符串(BEFORE或AFTER)。
数据类型是text；是一个由触发器定义决定的字符串(ROW或STATEMENT)。
数据类型是text；是一个说明激活触发器的操作的字符串(INSERT, UPDATE,DELETE,或TRUNCATE)。
数据类型是oid；是激活触发器调用的表的对象标识(OID)。
数据类型是name；是激活触发器调用的表的名称。 反对使用，并会在将来的版本中消失，推荐使用TG_TABLE_NAME。
数据类型是name；是激活触发器调用的表的名称。
数据类型是name；是激活触发器调用的表的模式。
数据类型是integer；是在CREATE TRIGGER语句里面赋予触发器过程的参数的个数。
数据类型是text的数组；是CREATE TRIGGER语句里的参数。 下标从 0 开始记数。非法下标(小于 0 或者大于等于tg_nargs)导致返回一个 NULL 值。
一个触发器函数必须返回NULL或者是一个与激活触发器运行的表的记录/行结构完全相同的数据。
因BEFORE触发的行级别触发器可以返回一个 NULL ， 告诉触发器管理器忽略对该行剩下的操作，也就是说，随后的触发器将不再执行， 并且不会对该行产生INSERT/UPDATE/DELETE动作)。 如果返回了一个非 NULL 的行，那么将继续对该行数值进行处理。 请注意，返回一个和原来的NEW不同的行数值将修改那个将插入或更新的行(不过在DELETE的情况下无效)。 因此，如果想在没有修改行值的同时成功的执行触发器动作，那么需要返回NEW（或等价的）。 可以用一个值直接代替NEW里的某个数值并且返回之，或者也可以构建一个全新的记录/行再返回。 In the case of a before-trigger on DELETE, the returned value has no direct effect, but it has to be nonnull to allow the trigger action to proceed. Note that NEW is null in DELETE triggers, so returning that is usually not sensible. A useful idiom in DELETE triggers might be to return OLD.
BEFORE或AFTER语句级别的触发器，或者一个AFTER行级别的触发器的返回值将总是被忽略； 它们也可以返回 NULL 来忽略返回值。 不过，任何这种类型的触发器仍然可以通过抛出一个错误来退出整个触发器操作。
Example 39-3显示了一个PL/pgSQL写的触发器过程的例子
Example 39-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 39-4显示了一个PL/pgSQL写的审计触发器过程的例子。
Example 39-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 39-5演示了一个PL/pgSQL触发器过程的例子， 它为某个数据仓库的一个事实表维护一个概要表。
Example 39-5. 一个维护概要表的PL/pgSQL触发器过程
下面的模式有一部分是基于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$
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;