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(); ```