PL/pgSQL 可以定义响应数据变化或数据库事件的触发器函数。使用 CREATE FUNCTION 创建函数时，不声明参数，并将返回类型设为 trigger（数据变化触发器）或 event_trigger（数据库事件触发器）。调用时会自动建立以 TG_ 开头的特殊局部变量，描述触发条件。
41.10.1. 数据变化触发器
数据变化触发器函数必须声明为无参数、返回 trigger。即使 CREATE TRIGGER 指定了传给函数的参数，函数声明也仍然不带参数；这些值会通过 TG_ARGV 传入。
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，对应
pg_class.oid。 TG_RELNAME，name- 引发调用的表名。此变量已弃用，未来可能消失，应使用
TG_TABLE_NAME。 TG_TABLE_NAME，name- 引发调用的表名。
TG_TABLE_SCHEMA，name- 引发调用的表所属模式的名称。
TG_NARGS，integerCREATE TRIGGER为触发器函数指定的参数数量。TG_ARGV，text[]CREATE TRIGGER指定的参数数组，下标从 0 开始。无效下标（小于 0 或大于等于tg_nargs）得到 null。
触发器函数必须返回 NULL，或返回与触发表结构完全一致的记录、行值。
行级 BEFORE 触发器可以返回 null，通知触发器管理器跳过这行的后续操作：后续触发器不再运行，该行的插入、更新或删除也不会发生。如果返回非 null 的行值，操作将使用该行继续执行。返回不同于原始 NEW 的行，就会改变实际插入或更新的数据。
因此，要让操作正常进行且不改变行值，应返回 NEW 或与它相等的值。要修改待保存的行，可以直接改变 NEW 的某些字段后返回，也可以构造完整的新记录。对于 DELETE 的 BEFORE 触发器，返回值本身不影响被删除行的内容，但必须非 null 才能让删除继续。因为 DELETE 中 NEW 为 null，通常应返回 OLD。
INSTEAD OF 触发器始终是行级触发器，并且只能用于视图。返回 null 表示它没有执行更新，并应跳过该行的后续操作；后续触发器不执行，该行也不会计入外围插入、更新或删除命令的受影响行数。完成了请求的操作时，应返回非 null。
INSERT 和 UPDATE 应返回 NEW；函数可以修改它，以支持 INSERT RETURNING 和 UPDATE RETURNING。这也会影响传给后续触发器的行值，或者含 ON CONFLICT DO UPDATE 的插入语句中供 EXCLUDED 特殊别名引用的行值。DELETE 应返回 OLD。
行级 AFTER 触发器，以及语句级 BEFORE/AFTER 触发器的返回值始终被忽略，因此可以返回 null。不过，这些触发器仍可通过引发错误来中止整个操作。
示例 41.3：PL/pgSQL 触发器函数
每次向员工表插入或更新一行时，此触发器都记录当前用户名和时间，并检查员工姓名及工资是否提供，以及工资值是否有效。原文描述工资应为正值；示例的实际条件是拒绝负值，因此允许 0。
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
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;
-- Who works for us when they must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- Remember who changed the payroll when
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();另一种记录表变化的方法，是在新表中为每次插入、更新和删除保存一行。这可以视为对表变化进行审计。
示例 41.4：审计触发器函数
这个触发器确保 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
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- making use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), current_user, 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 FUNCTION process_emp_audit();上例的变体将主表与审计表连接成一个视图，显示每个条目最近的修改时间。完整审计轨迹仍被保留，而视图提供经过简化的信息：从每个条目的审计记录中取最后修改时间。
示例 41.5：用于审计的视图触发器函数
此例使用视图上的触发器使视图可更新，同时将视图行的每次插入、更新和删除记录到 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
--
-- Perform the required operation on emp, and create a row in emp_audit
-- to reflect the change made to 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', current_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', current_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', current_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 FUNCTION update_emp_view();触发器还可以维护另一张表的汇总表。对于某些查询，使用汇总结果替代原始表往往能缩短运行时间。数据仓库中常用这种方法，因为保存测量或观察数据的事实表可能非常大。
示例 41.6：维护汇总表的触发器函数
这里展示如何维护数据仓库事实表的汇总表。表结构部分参考 Ralph Kimball 的 The Data Warehouse Toolkit 中的 Grocery Store 示例。
--
-- Main tables - time dimension and sales fact.
--
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);
--
-- Summary table - sales by time.
--
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);
--
-- Function and trigger to amend summarized column(s) on 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
-- Work out the increment/decrement amount(s).
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
-- forbid updates that change the time_key -
-- (probably not too onerous, as DELETE + INSERT is how most
-- changes will be made).
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 or update the summary row with the new values.
<<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 FUNCTION 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;AFTER 触发器还可以利用过渡表，检查触发语句修改的整组行。CREATE TRIGGER 为其中一个或两个过渡表指定名称，函数就可以像引用只读临时表一样引用它们。
示例 41.7：使用过渡表进行审计
此例的结果与示例 41.4 相同，不过先将相关信息收集到过渡表中，再为每条语句触发一次，而非为每一行触发一次。触发语句修改大量行时，这种方式可能明显快于逐行触发器。不同事件所需的 REFERENCING 子句不同，因此每一种事件都要单独声明触发器。
仍然可以让这些触发器使用同一个函数。实际应用中，也可以拆成三个函数，以避免运行时检查 TG_OP。
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
--
-- Create rows in emp_audit to reflect the operations performed on emp,
-- making use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit
SELECT 'D', now(), current_user, o.* FROM old_table o;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit
SELECT 'U', now(), current_user, n.* FROM new_table n;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit
SELECT 'I', now(), current_user, n.* FROM new_table n;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit_ins
AFTER INSERT ON emp
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_upd
AFTER UPDATE ON emp
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_del
AFTER DELETE ON emp
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();41.10.2. 事件触发器
PL/pgSQL 也可以定义事件触发器。PostgreSQL 要求被事件触发器调用的函数不带参数，返回类型为 event_trigger。
此类调用会在顶层块中自动创建以下特殊变量：
TG_EVENT，text- 触发器响应的事件。
TG_TAG，text- 触发调用的命令标签。
示例 41.8：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 FUNCTION snitch();© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
暂无评论内容