PostgreSQLのトリガー関数のBEFORE・AFTERの細かな挙動の違い

OSS-DB Silverの資格勉強の際、トリガーのBEFOREとAFTERに関する問題があり、いまいち違いがわからなかったため、トリガーのBEFORE・AFTERに関して試した内容をまとめます。

気になった点

productテーブルの変更をトリガーとして、product_log()関数(後述)が呼び出されるように設定した時、「トリガーの定義がBEFOREの場合、テーブルproductのカラムidの値は新しい値から-10されたものになる」という選択肢が正しいとされており、以下の点が気になりました。

• AFTERの場合にはproductのidの値はどうなるのか
• product_logに挿入される値にBEFORE・AFTERは影響を与えるか(OLD・NEWの値に影響を与えるか)
• product_log()関数の戻り値はどのように扱われるのか

よってこれらを以下で確認していきます。

実験準備

問題の状態を再現していきます。
まず以下のように、いくつかのデータが入ったproductテーブルとproduct_logテーブルが存在する状態を作るための、reset.sqlファイルを用意します。
状態をリセットするため、結果を確認する度にこのファイルを実行します。

reset.sql

DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS product_log;

CREATE TABLE product (id int, name varchar)
CREATE TABLE product_log (id int, updated_at timestamp);

INSERT INTO product VALUES (1, 'rec1');

product_log()関数をfunction.sqlファイルに記述します。(OLD・NEWの値を確認するため、出題された関数に、RAISEを追加しています)

function1.sql

CREATE OR REPLACE FUNCTION product_log() RETURNS TRIGGER AS $$
  BEGIN
    INSERT INTO product_log VALUES (NEW.id, CURRENT_TIMESTAMP);
    RAISE INFO 'OLD: %', OLD;
    RAISE INFO 'NEW: %', NEW;
    NEW.id := NEW.id - 10;
    RETURN NEW;
  END; $$
  LANGUAGE plpgsql;

以下のコマンドでreset.sqlとfunction.sqlファイルを実行して出題の環境を整えます。

\i reset.sql
\i function.sql

実験

1.BEFOREとAFTERでの違い

まずはBEFOREの場合です。

CREATE TRIGGER before_trigger BEFORE UPDATE OR INSERT ON product
FOR EACH ROW EXECUTE FUNCTION product_log();

UPDATE product SET id=2 WHERE id=1;
-- INFO:  OLD: (1,rec1)
-- INFO:  NEW: (2,rec1)
-- UPDATE 1

SELECT * FROM product;
--  id | name 
-- ----+------
--  -8 | rec1
-- (1 row)

SELECT * FROM product_log;
--  id |         updated_at         
-- ----+----------------------------
--   2 | 2024-04-16 02:01:16.348161
-- (1 row)

問題にあった通り、更新後のproductのidの値が、更新値2から10を引いた-8になっていることが確認できます。
また、NEWにはUPDATEで指定した値(id=2)が入っており、結果product_logテーブルにもUPDATEで指定した値が入っていることも確認できます。

次にAFTERの場合です。

CREATE TRIGGER after_trigger AFTER UPDATE OR INSERT ON product
FOR EACH ROW EXECUTE FUNCTION product_log();

UPDATE product SET id=2 WHERE id=1;
-- INFO:  OLD: (1,rec1)
-- INFO:  NEW: (2,rec1)
-- UPDATE 1

SELECT * FROM product;
--  id | name 
-- ----+------
--   2 | rec1
-- (1 rows)

SELECT * FROM product_log;
--  id |         updated_at         
-- ----+----------------------------
--   2 | 2024-04-16 02:00:11.066446
-- (1 row)

BEFOREと違い、更新後のproductのidの値が-10されることなく、更新値として指定した2になっていることが確認できます。
また、AFTERの場合もBEFOREと同様に、NEWにはUPDATEで指定した値(id=2)が入っており、OLDには元の値が入っていることが確認できました。

これで、OLDとNEWの値はそれぞれ元の値とUPDATEで指定した値を持っており、BEFOREかAFTERかに左右されないこと、AFTERを指定した場合にはトリガー関数での変更が反映されないことがわかりました。

2.トリガー関数の戻り値

続いてproduct_log()関数を変更して、BEFOREの時にRETURNするものをOLDにして、トリガー関数の戻り値の影響を確認します。

UPDATE product SET id=2 WHERE id=1;
-- INFO:  OLD: (1,rec1)
-- INFO:  NEW: (2,rec1)
-- UPDATE 1

SELECT * FROM product;
--  id | name 
-- ----+------
--   1 | rec1
-- (1 rows)

SELECT * FROM product_log;
--  id |        updated_at         
-- ----+---------------------------
--   2 | 2024-04-16 05:13:14.49556
-- (1 row)

結果は上の通りで、productの値は変化しておらず、BEFOREでトリガー関数を使用する場合、トリガー関数の戻り値が変更対象とした行の値となることがわかりました。

まとめ

以上のことから、変更対象行の値として、BEFOREの場合にはトリガー関数の戻り値が利用され、AFTERの場合には利用されないこと、UPDATEをトリガーとしている場合、NEWとOLDの値はBEFOREかAFTERかに関わらず、それぞれ更新対象行の元の値とUPDATEで指定した値となっていることがわかりました。

BEFOREかAFTERかに影響を受けるのが、操作対象とした行の値だけで、NEWとOLDの値は影響を受けないようなので、この２つの使い分けを考える必要がある時はそんなに無さそうだと思いました。