摘抄自 《posrgreSQL 修煉之道 從小工到專家》

觸發(fā)器（trigger）是一種由事件自動觸發(fā)執(zhí)行的特殊的存儲過程，這些事件可以是對一個表進行 INSERT,UPDATE,DELETE 等操作

觸發(fā)器經(jīng)常用于加強數(shù)據(jù)庫的完整性約束和業(yè)務(wù)規(guī)則上的約束等

創(chuàng)建觸發(fā)器

創(chuàng)建觸發(fā)器的語法如下

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments )

創(chuàng)建觸發(fā)器的步驟

• 創(chuàng)建執(zhí)行函數(shù)

先為觸發(fā)器創(chuàng)建一個執(zhí)行函數(shù)，此函數(shù)的返回類型為觸發(fā)器類型，然后即可創(chuàng)建相應(yīng)的觸發(fā)器

下面使用一個例子來講解觸發(fā)器的使用，假設(shè)有一張學(xué)生表（student），和一張考試成績表（score）

CREATE TABLE student(
    student_no int primary key,
    student_name varchar(40),
    age int
)

CREATE TABLE score(
    student_no int,
    chinese_no int,
    math_score int,
    test_date date
)

如果想刪除學(xué)生表的一條記錄時，把這個學(xué)生在成績表中的成績也刪除掉，這時就可以使用觸發(fā)器。先建觸發(fā)器的執(zhí)行函數(shù)：

CREATE OR REPLACE FUNCTION student_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM score WHERE student_no = OLD.student_no;
    RETURN OLD;
END;
$$
LANGUAGE plpgsql;

• 創(chuàng)建觸發(fā)器

CREATE TRIGGER delete_student_trigger
    AFTER DELETE ON student
    FOR EACH ROW EXECUTE PROCEDURE student_delete_trigger();

測試

按照上面的語句創(chuàng)建好觸發(fā)器后還需要相應(yīng)的測試，先插入一些測試數(shù)據(jù)：

INSERT INTO student VALUES(1, '張三', 14);
INSERT INTO student VALUES(2, '李四', 13);
INSERT INTO student VALUES(3, '王二', 15);

INSERT INTO score VALUES(1, 85, 75, date '2013-05-23');
INSERT INTO score VALUES(1, 89, 73, date '2013-09-18');
INSERT INTO score VALUES(2, 68, 83, date '2013-05-23');
INSERT INTO score VALUES(2, 73, 85, date '2013-09-18');
INSERT INTO score VALUES(3, 72, 79, date '2013-05-23');
INSERT INTO score VALUES(3, 78, 82, date '2013-05-23');

現(xiàn)在把學(xué)好為 3 的學(xué)生 “王二” 從表 "student" 刪掉：

DELETE FROM stduent WHERE student_no = 3;

這時可以查詢成績表 'score' 可以發(fā)現(xiàn)學(xué)號（student_no ） 為 3 的學(xué)生成績記錄也被刪除掉了

語句級觸發(fā)器與行級觸發(fā)器

語句級觸發(fā)器

語句級觸發(fā)器是指執(zhí)行每個 SQL 時，只執(zhí)行一次，行級觸發(fā)器是指每行就會執(zhí)行一次。一個修改 0 行的操作任然會導(dǎo)致合適的語句級觸發(fā)器被執(zhí)行。下面來看看相應(yīng)的示例。

假設(shè)對 student 的更新情況記錄 log?？梢詾?student 建一張 log 表，如下：

CREATE TABLE log_student(
    update_time timetamp, --操作的時間
    db_user varchar(40), --操作的數(shù)據(jù)庫用戶名
    opr_type varchar(6), --操作類型：insert delete udate
);

創(chuàng)建記錄 log 的觸發(fā)器函數(shù)：

CREATE FUNCTION log_student_trigger()
RETURNS trigger AS
$$
BEGIN
    INSERT INTO log_student values(now(), user, TG_OP);
    RETURN NULL;
END;
$$
LANGUAGE "plpgsql";

上面函數(shù)中的 "TG_OP" 是觸發(fā)器中的特殊變量，代表 DML 操作類型。

然后在 student 表上創(chuàng)建一個語句級觸發(fā)器：

CREATE TRIGGER log_student_trigger
    AFTER INSERT OR UPDATE OR DELETE ON student
    FOR STATEMENT
    EXECUTE PROCEDURE log_student_trigger();

• 刪除觸發(fā)器

drop trigger log_student_log on student;

語句級觸發(fā)器即使在沒有更新到數(shù)據(jù)時，也會被觸發(fā)

行級觸發(fā)器

CREATE TRIGGER log_student_trigger_2
    AFTER INSERT OR UPDATE OR DELETE ON student
    FOR ROW
    EXECUTE PROCEDURE log_student_trigger();

行級觸發(fā)器即使在沒有更新到數(shù)據(jù)時，不會被觸發(fā)

BEFORE 觸發(fā)器和 AFTER 觸發(fā)器

通常，語句級別的 "before" 觸發(fā)器是在語句開始做任何事之前被觸發(fā)，而語句級別的"after" 觸發(fā)器是在語句結(jié)束時才觸發(fā)的。行級別的"before" 觸發(fā)器是在對特定行進行操作之前觸發(fā)的，而行級別的 "after" 觸發(fā)器是在語句結(jié)束時才觸發(fā)的，但是它會在任何語句級別的 "after" 觸發(fā)器被觸發(fā)之前觸發(fā)

BEFORE 觸發(fā)器可以直接修改 "NEW" 值以改變實際更新的值，具體例子如下：

先建一個觸發(fā)器函數(shù)：

CREATE FUNCTION student_new_name_trigger()
RETURNS trigger AS '
BEGIN
    NEW.student_name = NEW.student_name || NEW.student_no;
    RETURN NEW;
END;
'
LANGUAGE "plpgsql";

這個函數(shù)的作用是，插入或者更新時，在 "student_name" 后面加上 "student_no" 學(xué)號。也就是直接修改 "NEW.student_name" ,語句如下：

NEW.student_name = NEW.student_name||NEW.student_no

在這中情況下只能使用 BEFORE 觸發(fā)器，因為 BEFORE 觸發(fā)器是在更新數(shù)據(jù)之前觸發(fā)的，所以這時修改了"NEW.student_name", 后面實際更新到數(shù)據(jù)庫中的值就變成了 "student_name||student_no"

如果使用了 AFTER ，則修改 "NEW" 是沒用的

刪除觸發(fā)器

刪除觸發(fā)器的語法如下：

DROP TRIGGER [ IF EXISTS ] name ON table [CASCADE | RESTRICT ];

其中的語法說明如下。

• IF EXISTS: 如果指定的觸發(fā)器不存在，那么發(fā)出一個 notice 而不是跑出一個錯誤
• CASCADE: 級聯(lián)刪除依賴此觸發(fā)器的對象
• RESTRICT: 這是默認值，如果有任何依賴對象存在，那么拒絕刪除

**在 PostgresSQL 中要在刪除觸發(fā)器的語法中指定 "ON table"，而在其他一些數(shù)據(jù)庫的語發(fā)可能直接是 "DROP TRIGGER name" **

刪除觸發(fā)器時，觸發(fā)器的函數(shù)不會被刪除。不過，當表刪除時，表上的觸發(fā)器也會被刪除

觸發(fā)器的行為

觸發(fā)器函數(shù)與返回值。語句級觸發(fā)器總是返回 NULL。 即必須顯式的在觸發(fā)器函數(shù)中寫上 "RETURN NULL", 如果沒有寫，將導(dǎo)致出錯。

對于 "BEFORE" 和 "INSTEAD OF" 這類行級觸發(fā)器函數(shù)來說，如果返回的是 NULL， 則表示忽略對當前行的操作，如果返回的是非 NULL 行，對與 INSERT 和 UPDATE 來說，返回的行將成為被插入的行或?qū)⒁歉碌男小?/p>

對于　 AFTER 這類行級觸發(fā)器來說，其返回值將會被忽略。

如果同一時間上有多個觸發(fā)器，則將按觸發(fā)器名字的順序來觸發(fā)?！∪绻恰?BEFORE" 和　"INTEAD OF" 行級觸發(fā)器，每個觸發(fā)器所返回的行（可能已經(jīng)被修改）將成為下一個觸發(fā)器的輸入，如果"BEFORE" 和　"INSTEAD OF" 行級觸發(fā)器返回的內(nèi)容為空，那么該行上的其他行級觸發(fā)器也不會被觸發(fā)。

觸發(fā)器函數(shù)中的特殊變量

當把一個 PL/pgSQL 函數(shù)當做觸發(fā)器函數(shù)調(diào)用的時候，系統(tǒng)會在頂層生命字段里自動創(chuàng)建幾個特殊變量，比如在之前的幾個例子當中　"NEW", "OLD", "TG_OP", 變量等?？梢允褂玫淖兞咳缦逻@些：

• NEW: 該變量為 INSERT/UPDATE 操作觸發(fā)的行級觸發(fā)器中存儲的新數(shù)據(jù)行，數(shù)據(jù)類型是　"RECORD" ,在語句級別的觸發(fā)器里沒有分配次變量，　 DELETE 操作觸發(fā)的行級觸發(fā)器中也沒有分配此變量

• OLD：數(shù)據(jù)類型是 record。在 update、delete 操作觸發(fā)時存儲舊的數(shù)據(jù)行。

• TG_NAME：數(shù)據(jù)類型是 name。觸發(fā)器名稱。

• TG_WHEN：內(nèi)容為"BEFORE"或“AFTER”，可以用來判斷是 BEFORE 觸發(fā)器還是 AFTER 觸發(fā)器。

• TG_LEVEL：內(nèi)容為“ROW”或“STATEMENT”，可以用來判斷是語句級觸發(fā)器還是行級觸發(fā)器。

• TG_OP：內(nèi)容為“INSERT”、“UPDATE”、“DELETE”、“TRUNCATE”，用于指定 DML 語句類型。

• TG_RELID：觸發(fā)器所在表的 oid。

• TG_TABLE_NAME：觸發(fā)器所在表的表名稱。

• TG_SCHEMA_NAME：觸發(fā)器所在表的模式。

• TG_NARGS：在創(chuàng)建觸發(fā)器語句中賦予觸發(fā)器過程的參數(shù)個數(shù)。

• TG_ARGV[]：text 類型的一個數(shù)組。創(chuàng)建觸發(fā)器語句中指定的參數(shù)。