PolarDB：CREATE TRIGGER

簡介

CREATE TRIGGER建立一個新觸發器。該觸發器將被關聯到指定的表、視圖或者外部表格並且在表上發生特定操作時將執行指定的函數 function_name。

該觸發器可以被指定為在一行上嘗試該操作之前觸發（在約束被檢查並且INSERT、UPDATE或者DELETE被嘗試之前）；也可以在該操作完成之後觸發（在約束被檢查並且INSERT、UPDATE或者DELETE完成之後）；或者取代該操作（在對一個視圖插入、更新或刪除的情況中）。如果該觸發器在事件之前觸發或者取代事件，該觸發器可以跳過對當前行的操作或者改變正在被插入的行（只對INSERT以及UPDATE操作）。如果該觸發器在事件之後觸發，所有更改（包括其他觸發器的效果）對該觸發器“可見”。

一個被標記為FOR EACH ROW的觸發器會對該操作修改的每一行都調用一次。例如，一個影響 10 行的DELETE將導致在目標關係上的任何ON DELETE觸發器被獨立調用 10 次，也就是為每一個被刪除的行調用一次。與此相反，一個被標記為FOR EACH STATEMENT的觸發器只會為任何給定的操作執行一次，不管該操作修改多少行（特別地，一個修改零行的操作將仍會導致任何可用的FOR EACH STATEMENT觸發器被執行）

被指定為要觸發INSTEAD OF觸發事件的觸發器必須被標記為FOR EACH ROW，並且只能被定義在視圖上。一個視圖上的BEFORE和AFTER觸發器必須被標記為FOR EACH STATEMENT。

此外，觸發器可以被定義成為TRUNCATE觸發，但只能是FOR EACH STATEMENT。

下面的表格總結了哪些觸發器類型可以被用在表、視圖和外部表格上：

一個觸發器定義可以指定一個布爾的WHEN條件，它將被測試來看看該觸發器是否應該被觸發。在行級觸發器中，WHEN條件可以檢查該行的列的新舊值。語句級觸發器也可以有WHEN條件，儘管該特性對於它們不是很有用（因為條件不能參考資料表中的任何值）。

如果有多個同種觸發器被定義為相同事件觸發，它們將按照名稱的字母表順序被觸發。

當CONSTRAINT選項被指定，這個命令會建立一個約束觸發器。這和一個常規觸發器相同，不過觸發該觸發器的時機可以使用 SET CONSTRAINTS 調整。約束觸發器必須是表上的AFTER ROW觸發器。它們可以在導致觸發事件的語句末尾被引發或者在包含該語句的事務末尾被引發。在後一種情況中，它們被稱作是被延遲。一個待處理的延遲觸發器的引發也可以使用SET CONSTRAINTS立即強制發生。當約束觸發器實現的約束被違背時，約束觸發器應該拋出一個異常。

REFERENCING選項啟用對傳遞關係的收集，傳遞關係是包括被當前 SQL 陳述式插入、刪除或者修改的行的行集合。這個特性讓觸發器能看到該語句做的事情的全域視圖，而不是一次只看到一行。僅對非約束觸發器的AFTER觸發器允許這個選項。此外，如果觸發器是一個UPDATE觸發器，則它不能指定 column_name列表。OLD TABLE僅可以被指定一次，並且只能為在UPDATE或DELETE事件上引發的觸發器指定，它建立的傳遞關係包含有該語句更新或刪除的所有行的前映像。類似地，NEW TABLE僅可以被指定一次，並且只能為在UPDATE或INSERT事件上引發的觸發器指定，它建立的傳遞關係包含有該語句更新或插入的所有行的後映像。

SELECT不修改任何行，因此你無法建立SELECT觸發器。規則和視圖可以為需要SELECT觸發器的問題提供可行的解決方案。

文法

    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 { FUNCTION | PROCEDURE } function_name ( arguments )

    這裡的event可以是下列之一：

        INSERT
        UPDATE [ OF column_name [, ... ] ]
        DELETE
        TRUNCATE

參數

name給新觸發器的名稱。這必須與同一個表上的任何其他觸發器相區別。名稱不能是模式限定的，該觸發器會繼承它所在表的模式。對於一個約束觸發器，這也是使用SET CONSTRAINTS修改觸發器行為時要用到的名字。

BEFOREAFTERINSTEAD OF決定該函數是要在事件之前、之後被調用還是會取代該事件。一個約束觸發器也能被指定為AFTER。

eventINSERT、UPDATE、DELETE或者TRUNCATE之一，這指定了將要引發該觸發器的事件。多個事件可以用OR指定，要求傳遞關係的時候除外。

對於UPDATE事件，可以使用下面的文法指定一個列的列表：

UPDATE OF column_name1 [, column_name2 ... ]

只有當至少一個被列出的列出現在UPDATE命令的更新目標中時，或者如果列出的列之一是產生的列，而且依賴的列是UPDATE的目標，該觸發器才會觸發。

INSTEAD OF UPDATE事件不允許列的列表。在請求傳遞關係時，也不能指定列的列表。

table_name要使用該觸發器的表、視圖或外部表格的名稱（可能是模式限定的）。

referenced_table_name約束引用的另一個表的名稱（可能是模式限定的）。這個選項被用於外鍵約束並且不推薦用於一般的目的。這隻能為約束觸發器指定。

DEFERRABLENOT DEFERRABLEINITIALLY IMMEDIATEINITIALLY DEFERRED該觸發器的預設時機。這些約束選項的細節可參考 CREATE TABLE 文檔。這隻能為約束觸發器指定。

REFERENCING這個關鍵詞緊接在一個或者兩個關係名的聲明之前，這些關係提供對觸發語句的傳遞關係的訪問。

OLD TABLENEW TABLE這個子句指示接下來的關係名是用於前映像傳遞關係還是後映像傳遞關係。

transition_relation_name在該觸發器中這個傳遞關係要使用的（未限定）名稱。

FOR EACH ROWFOR EACH STATEMENT這指定該觸發器函數是應該為該觸發事件影響的每一行被引發一次，還是只為每個 SQL 陳述式被引發一次。如果都沒有被指定，FOR EACH STATEMENT會是預設值。約束觸發器只能被指定為FOR EACH ROW。

condition一個決定該觸發器函數是否將被實際執行的布林運算式。如果指定了WHEN，只有 condition返回true時才會調用該函數。在FOR EACH ROW觸發器中，WHEN條件可以分別寫OLD. column_name或者NEW. column_name來引用列的新舊行值。當然，INSERT觸發器不能引用OLD並且DELETE觸發器不能引用NEW。

INSTEAD OF觸發器不支援WHEN條件。

當前，WHEN運算式不能包含子查詢。

對於約束觸發器，對於WHEN條件的計算不會被延遲，而是直接在行更新操作被執行之後立刻發生。如果該條件計算得不到真，那麼該觸發器就不會被放在順延強制的隊列中。

function_name一個使用者提供的函數，它被聲明為不用參數並且傳回型別trigger，當觸發器引發時會執行該函數。

在CREATE TRIGGER的文法中，關鍵詞FUNCTION和PROCEDURE是等效的，但是任何情況下被引用的函數必須是一個函數而不是過程。這裡，關鍵詞PROCEDURE的使用是有歷史原因的並且已經被廢棄。

arguments一個可選的逗號分隔的參數列表，它在該觸發器被執行時會被提供給該函數。參數是字串常量。簡單的名稱和數字常量也可以被寫在這裡，但是它們將全部被轉換成字串。請檢查該觸發器函數的實現語言的描述來找出在函數內部如何訪問這些參數，這可能與普通函數參數不同。

說明

要在一個表上建立一個觸發器，使用者必須具有該表上的TRIGGER特權。使用者還必須具有在觸發器函數上的EXECUTE特權。

使用 DROP TRIGGER 移除一個觸發器。

當一個列相關的觸發器（使用UPDATE OF column_name文法定義的觸發器）的列被列為UPDATE命令的SET列表目標時，它會被觸發。即便該觸發器沒有被引發，一個列的值也可能改變，因為BEFORE UPDATE觸發器對行內容所作的改變不會被考慮。相反，一個諸如UPDATE ... SET x = x ...的命令將引發一個位於列x上的觸發器，即便該列的值沒有改變。

有一些內建的觸發函數可用於解決常見問題，而無需編寫您自己的觸發代碼。

在一個BEFORE觸發器中，WHEN條件正好在函數被或者將被執行之前被計算，因此使用WHEN與在觸發器函數的開始測試同一個條件沒有實質上的區別。特別注意該條件看到的NEW行是當前值，雖然可能已被早前的觸發器所修改。還有，一個BEFORE觸發器的WHEN條件不允許檢查NEW行的系統列（例如ctid），因為那些列還沒有被設定。

在一個AFTER觸發器中，WHEN條件正好在行更新發生之後被計算，並且它決定一個事件是否要被放入隊列以便在語句的末尾引發該觸發器。因此當一個AFTER觸發器的WHEN條件不返回真時，沒有必要把一個事件放入隊列或者在語句末尾重新取得該行。如果觸發器只需要為一些行被引發，就能夠顯著地加快修改很多行的語句的速度。

在一些情況下，單一的 SQL 命令可能會引發多種觸發器。例如，一個帶有ON CONFLICT DO UPDATE子句的INSERT可能同時導致插入和更新操作，因此它將根據需要引發這兩種觸發器。提供給觸發器的傳遞關係與它們的事件類型有關，因此INSERT觸發器將只看到被插入的行，而UPDATE觸發器將只看到被更新的行。

由外鍵強制動作導致的行更新或刪除（例如ON UPDATE CASCADE或ON DELETE SET NULL）被當做導致它們的 SQL 命令的一部分。受影響的表上的相關觸發器將被引發，這樣就提供了另一種方法讓 SQL 命令引發不直接匹配其類型的觸發器。在簡單的情況中，請求傳遞關係的觸發器將在一個傳遞關係中看到由原始 SQL 命令在其表中做出的所有改變。不過，有些情況中一個請求傳遞關係的AFTER ROW觸發器的存在將導致由單個 SQL 命令觸發的外鍵強制動作被分成多步，每一步都有其自己的傳遞關係。在這種情況下，沒建立一個傳遞關係集合都會引發存在的所有語句級觸發器，確保那些觸發器能夠在一個傳遞關係中看到每個受影響的行一次，並且只看到一次。

只有當視圖上的動作被一個行級INSTEAD OF觸發器處理時才會引發視圖上的語句級觸發器。如果動作被一個INSTEAD規則處理，那麼該語句發出的任何語句都會代替提及該視圖的原始語句執行，這樣將被引發的觸發器是替換語句中提及的表上的那些觸發器。類似地，如果視圖是自動可更新的，則該動作將被處理為把該語句自動重寫成在視圖基表上的一個動作，這樣基表的語句級觸發器就是要被引發的。

在分區表上建立一個行級觸發器將導致在它所有的現有分區上建立相同的觸發器，並且以後建立或者掛接的任何分區也將包含一個相同的觸發器。 如果該分區與其父分割分離，則觸發器將被刪除。分區表上的觸發器只能是AFTER。

修改分區表或者帶有繼承子表的表會引發掛接到顯式提及表的語句級觸發器，但不會引發其分區或子表的語句級觸發器。相反，行級觸發器會在受影響的分區或子表上引發，即便它們在查詢中沒有被明確提及。如果一個語句級觸發器用REFERENCING子句定義有傳遞關係，則來自所有受影響分區或子表中的行的前後映像都是可見的。在繼承子表的情況中，行映像僅包括該觸發器所附屬的表中存在的列。當前，不能在分區或繼承子表上定義帶有傳遞關係的行級觸發器。

樣本

只要表accounts的一行即將要被更新時會執行函數check_account_update：

    CREATE TRIGGER check_update
        BEFORE UPDATE ON accounts
        FOR EACH ROW
        EXECUTE FUNCTION check_account_update();

下面的例子與上面一個例子相同，但是只在UPDATE命令指定要更新balance列時才執行該函數：

    CREATE TRIGGER check_update
        BEFORE UPDATE OF balance ON accounts
        FOR EACH ROW
        EXECUTE FUNCTION check_account_update();

這種形式只有列balance具有真正被改變的值時才執行該函數：

    CREATE TRIGGER check_update
        BEFORE UPDATE ON accounts
        FOR EACH ROW
        WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
        EXECUTE FUNCTION check_account_update();

調用一個函數來記錄accounts的更新，但是只在有東西被改變時才調用：

    CREATE TRIGGER log_update
        AFTER UPDATE ON accounts
        FOR EACH ROW
        WHEN (OLD.* IS DISTINCT FROM NEW.*)
        EXECUTE FUNCTION log_account_update();

為每一個要插入到視圖底層表中的行執行函數view_insert_row：

    CREATE TRIGGER view_insert
        INSTEAD OF INSERT ON my_view
        FOR EACH ROW
        EXECUTE FUNCTION view_insert_row();

為每個語句執行函數check_transfer_balances_to_zero以確認transfer的行不會有淨值增加：

    CREATE TRIGGER transfer_insert
        AFTER INSERT ON transfer
        REFERENCING NEW TABLE AS inserted
        FOR EACH STATEMENT
        EXECUTE FUNCTION check_transfer_balances_to_zero();

為每一行執行函數check_matching_pairs以確認（同一個語句）同時對匹配對做了更改 ：

    CREATE TRIGGER paired_items_update
        AFTER UPDATE ON paired_items
        REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
        FOR EACH ROW
        EXECUTE FUNCTION check_matching_pairs();