35.10. 觸發器過程

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;是一個說明觸發觸發器的操作的字元串, 可以是 INSERTUPDATE 或者 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 35-1 顯示了一個 PL/pgSQL 寫的觸發器過程的例子。

Example 35-1. 一個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 ISNULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary ISNULL 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 := 'now';
        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 35-2 顯示了一個 PL/pgSQL 寫的審計觸發器過程的例子。

Example 35-2. 一個用於審計的 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;
        END IF;
        RETURN NULL; -- 忽略結果,因為它是個 AFTER 觸發器
    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 35-3 演示了一個 PL/pgSQL 觸發器過程的例子, 它為某個資料倉庫的一個事實資料表維護一個概要資料表。

Example 35-3. 一個維護概要資料表的 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$
    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;


        -- 用新數值更新概要行。
        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;


        -- There might have been no row with this time_key (e.g new data!).
        IF (NOT FOUND) THEN
            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
                           );
            EXCEPTION
                --
                -- 捕獲兩個交易維一個新 time_key 增加資料的衝突條件
                --
                WHEN UNIQUE_VIOLATION THEN
                    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;

            END;
        END IF;
        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();