Postgresql

如何從 PostgreSQL 中的觸發器訪問其他插入/更新的行?

  • July 17, 2018

MS SQL 中的解決方案

MS SQL 觸發器函式具有儲存受操作影響的所有行的系統表deletedinserted您可以計算更新的行數:

set @updatedCount = (select count(*) from deleted)

或找出最小值:

set @updatedMinimumCol1 = (select min(col1) from deleted)

PostgreSQL 的問題

對於FOR EACH ROW觸發器,我可以使用 OLD 和 NEW 系統記錄,但每次呼叫觸發器時它們只儲存 1 行。觸發器的呼叫是分開的,所以如果使用者更新 10 行,觸發器將被呼叫 10 次,但每次我只能知道目前的 1 行,而不是全部 10 行。

因為FOR EACH STATEMENT我根本不知道任何訪問更新行的機制。我使用 PostgreSQL v9.6OLD TABLENEW TABLE在 v10 中引入。

PostgreSQL 不允許在語句級觸發器中引用舊表和新表,即包含所有舊和/或新行的表,這些表由 SQL 標準中的 OLD TABLE 和 NEW TABLE 子句引用。


嘗試使用 transaction_timestamp() 附加列

我可以在主表中添加特殊列,DEFAULT transaction_timestamp()然後使用它來區分剛剛更新的行與其他行,但這不是解決方案,因為多個INSERTs/UPDATEs可以在一個事務中並且它們將具有相同的事務時間戳。可能我可以在每個語句之後清除觸發器中的這個時間戳列以避免這個問題,但是如果這樣的清除將再次發出更新觸發器,如何做到這一點 - 將是無限更新觸發器呼叫。

所以,這次嘗試失敗了。


PostgreSQL 中的錯誤解決方案

我知道的唯一方法是:

首先,使用FOR EACH ROW觸發器來收集目前統計資訊(最小值和計數),例如聚合函式。我使用臨時表在呼叫之間儲存它(此觸發器每行呼叫 1 次)。但是我們不會知道哪一行是最後一行(何時使用此統計資訊)。

CREATE TEMP TABLE IF NOT EXISTS _stats (
 _current_min   int,
 _current_count int
) ON COMMIT DROP;

IF EXISTS(SELECT 1 FROM _stats LIMIT 1) THEN
 --Current row is not first, there is statistics for previous rows.
 UPDATE _stats
 SET _current_min   = (CASE WHEN NEW.col1 < _current_min THEN NEW.col1
                       ELSE _current_min END)
   , _current_count = _current_count + 1;
ELSE
 --There is no stats because current row is first for this INSERT/UPDATE
 INSERT INTO _stats (_current_min, _current_count)
 VALUES (NEW.col1, 1);
END IF;

其次,使用FOR EACH STATEMENT觸發器來使用收集的統計資訊。不要忘記清除臨時表(如果使用者將在一個事務中執行多個插入/更新,舊的統計資訊將保留在臨時表中並破壞所有接下來的計算!)。

對於更複雜的任務,我們可以創建臨時表inserteddeleted方法與_stats.


解決方法

在 PostgreSQL 中,我們可以對 INSERT/UPDATE/DELETE 使用 RETURNING 子句來獲取受操作影響的所有行的新值。然後我們可以使用它們進行操作,但是每個帶有 INSERT/UPDATE 的函式都必須實現這項技術 ===> 1. 帶有這種 INSERT/UPDATE 的函式中的附加程式碼 - RETURNING 的重複;2.我們可以忘記將此類技術應用於新功能;3. 數據將被破壞,因為不會自動呼叫所需的操作(就像觸發器一樣)。


問題

也許,您知道訪問受 INSERT/UPDATE 影響的所有行的更好方法?

請參閱文件,您應該能夠從語句觸發器訪問舊記錄和新記錄:

CREATE TRIGGER some_table_update_trigger
 AFTER UPDATE ON some_table
 REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
 FOR EACH STATEMENT
 EXECUTE PROCEDURE do_something_with_newtab_and_oldtab();

對於 PostgreSQL 10+,請參閱@ewramner 的回答

對於較低版本,我找到了 2 個解決方案。僅當您希望在觸發器中使用inserteddeleted表時,兩者都有效。AFTER


解決方案 1. 臨時表 _inserted 和 _deleted。

首先,BEFORE FOR EACH ROW觸發器中創建臨時表並填充它們:

CREATE TRIGGER trigger_fill_sys_tables
 BEFORE INSERT OR UPDATE OR DELETE
 ON public.ttest2
 FOR EACH ROW
EXECUTE PROCEDURE public.tr_fill_sys_tables();


CREATE OR REPLACE FUNCTION public.tr_fill_sys_tables()
 RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
 EXECUTE 'CREATE TEMP TABLE IF NOT EXISTS _deleted (LIKE ' || tg_table_schema || '.' || tg_relname || ');';
 IF tg_op <> 'INSERT' THEN
   INSERT INTO _deleted
     SELECT old.*;
 END IF;

 EXECUTE 'CREATE TEMP TABLE IF NOT EXISTS _inserted (LIKE ' || tg_table_schema || '.' || tg_relname || ');';
 IF tg_op <> 'DELETE' THEN
   INSERT INTO _inserted
     SELECT new.*;
 END IF;

 IF tg_op <> 'DELETE' THEN
   RETURN new;
 ELSE
   RETURN old;
 END IF;
END;
$$;

每次呼叫觸發器時,通過new和系統記錄我們都可以訪問目前記錄。old但是當它被呼叫為第一行時,我們不知道第二行。我們不知道是否存在更多行。這就是為什麼

**第二。**在AFTER EACH STATEMENT觸發器中已經收集了所有行。您可以使用此表:

CREATE TRIGGER trigger_use_sys_tables
 AFTER INSERT OR UPDATE OR DELETE
 ON ttest2
 FOR EACH STATEMENT
EXECUTE PROCEDURE public.tr_use_sys_tables();


CREATE OR REPLACE FUNCTION public.tr_use_sys_tables()
 RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
 _row record;
BEGIN
 --If 0 rows was affected by statement, tr_fill_sys_tables() will NOT be called, _inserted will NOT be created. To avoid a crash, check it:
 IF NOT EXISTS(SELECT 1
               FROM pg_class
               WHERE relname = '_inserted') THEN
   RETURN NULL;
 END IF;

 --Work with sys tables.
 --Note: changing data in them will not affect to main table!
 --Note: changing data in main table can fire this trigger again and fall into infinity loop. CREATE TEMP TABLE _lock() before UPDATE and DROP it after one to check if trigger was called recursively.
 FOR _row IN
 SELECT
     COALESCE(n.id, o.id) AS id
   , o.data               AS old_data
   , n.data               AS new_data
 FROM _inserted n
   FULL OUTER JOIN _deleted o ON n.id = o.id
 LOOP
   RAISE NOTICE 'id = %, old data = %, new data = %', _row.id, _row.old_data, _row.new_data;
 END LOOP;

 --DO NOT FORGET to drop the tables!
 --Just clear is not a solution, since next INSERT/UPDATE/DELETE can work with another table with different structure
 DROP TABLE _deleted;
 DROP TABLE _inserted;

 RETURN NULL;
END;
$$;

解決方案 2. 表中的附加列。

如果您想在觸發器中再次更新插入/更新的字元串,那很好。不適用於DELETE觸發器,請參閱解決方案 1。

**首先,**將列添加trans_timest timestamp到主表。

其次,transaction_timestamp()通過BEFORE FOR EACH ROW觸發器寫入:

CREATE TRIGGER trigger_trans_mark
 BEFORE INSERT OR UPDATE
 ON public.ttest
 FOR EACH ROW
EXECUTE PROCEDURE public.tr_ttest_trans_mark();

CREATE OR REPLACE FUNCTION public.tr_ttest_trans_mark()
 RETURNS trigger AS $$
BEGIN
 IF tg_op = 'INSERT' THEN --to not crash when checking "old" record
   new.trans_timest = transaction_timestamp();
 ELSE
   IF old.trans_timest IS NULL THEN --if we are clearing marks, do not set them again
     new.trans_timest = transaction_timestamp();
   END IF;
 END IF;
 RETURN new;
END;
$$
LANGUAGE 'plpgsql';

**第三,**您AFTER FOR EACH STATEMENT可以使用此標記將受此影響的行INSERT/UPDATE與其他行區分開來。不要忘記清除此觸發器中的標記(如果使用者在一個事務中執行多個 INSERT/UPDATE,則它們都將具有相同的 trans_timest 並且將被混合)。但是只有當它們還沒有被清除時,你才能清除這些標記(如果你在 UPDATE 觸發器中呼叫 UPDATE,它會呼叫自己 - 如果沒有這個檢查,你將陷入無限循環):

CREATE TRIGGER trigger_use_mark
 AFTER INSERT OR UPDATE
 ON public.ttest
 FOR EACH STATEMENT
EXECUTE PROCEDURE public.tr_ttest_use_mark();

CREATE OR REPLACE FUNCTION public.tr_ttest_use_mark()
 RETURNS trigger AS $$
BEGIN
 IF NOT EXISTS(SELECT 1
           FROM public.ttest t
           WHERE t.trans_timest = transaction_timestamp()
           LIMIT 1) THEN --To avoid infinity loop
   RETURN NULL;
 END IF;

   --Work with marked rows.
   ...

 --DO NOT FORGET to clear marks!
 UPDATE public.ttest
 SET trans_timest = NULL --update this rows again only simultaniously with clearing of marks!
 WHERE trans_timest = transaction_timestamp();

 RETURN NULL;
END;
$$
LANGUAGE 'plpgsql';

引用自:https://dba.stackexchange.com/questions/212135