PostgreSQL 8.0.0 中文文件（轉譯自 PostgreSQL 中國製作的簡體中文版本）
Prev	Fast Backward	Chapter 35. PL/pgSQL - SQL 過程語言	Fast Forward	Next

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；是一個說明觸發觸發器的操作的字元串，可以是 INSERT，UPDATE 或者 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();

Prev	Home	Next
錯誤和消息	Up	從Oracle的 PL/SQL 移植