PL/pgSQL 可用于定义数据变更或数据库事件的触发器函数。触发器函数使用 CREATE FUNCTION 命令创建，将其声明为没有参数且返回值类型为 trigger（对于数据变更触发器）或 event_trigger（对于数据库事件触发器）的函数。特殊局部变量名为 TG_，自动定义以描述触发调用的条件。something
数据变更触发器被声明为没有参数且返回值类型为 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（引用 pg_class.oid） #导致触发器调用的表的对象 ID。
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 中的单个值并返回修改后的 NEW，也可以构建一个完整的新的记录/行以返回。在对 DELETE 的前触发器的情况下，返回值没有直接影响，但它必须是非 null 的，以便允许触发操作继续进行。请注意，NEW 在 DELETE 触发器中为 null，因此通常没有意义返回它。在 DELETE 触发器中的常用习惯用法是返回 OLD。
INSTEAD OF 触发器（始终是行级触发器，并且只能用于视图）可以返回 null 来指示它们没有执行任何更新，并且应跳过此行的其余操作（即，后续触发器不会触发，并且该行不会计入周围 INSERT/UPDATE/DELETE 的受影响行状态）。否则，应返回非 null 值，以指示触发器执行了请求的操作。对于 INSERT 和 UPDATE 操作，返回值应为 NEW，触发器函数可以修改它来支持 INSERT RETURNING 和 UPDATE RETURNING（这也会影响传递给任何后续触发器的行值，或传递给带有 ON CONFLICT DO UPDATE 子句的 INSERT 语句中的特殊 EXCLUDED 别名引用）。对于 DELETE 操作，返回值应为 OLD。
在 AFTER 中触发的行级触发器或在 BEFORE 或 AFTER 中触发的语句级触发器的返回值始终被忽略；它可能也是 null。但是，这些类型的触发器中的任何一个仍然可能通过引发错误来中止整个操作。
示例 41.3 显示了 PL/pgSQL 中触发器函数的示例。
示例 41.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
-- 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 显示了 PL/pgSQL 中审计触发器函数的示例。
示例 41.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
--
-- 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 显示了 PL/pgSQL 中视图上的审计触发器的示例。
示例 41.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
--
-- 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 显示了一个在 PL/pgSQL 中维护数据仓库中事实表摘要表的触发器函数示例。
示例 41.6. 用于维护摘要表的 PL/pgSQL 触发器函数
此处详细介绍的模式部分基于 Ralph Kimball 编写的 数据仓库工具包 中的 杂货店 示例。
--
-- 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.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();
PL/pgSQL 可用于定义 事件触发器。 PostgreSQL 要求作为事件触发器调用的函数必须声明为没有参数且返回类型为 event_trigger 的函数。
当 PL/pgSQL 函数作为事件触发器调用时，会在顶层块中自动创建几个特殊变量。它们是
示例 41.8 显示了 PL/pgSQL 中事件触发器函数的示例。
示例 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();
如果您在文档中看到任何不正确的内容、与您对特定功能的体验不符或需要进一步说明，请使用 此表格 报告文档问题。