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`ファイルを実行して出題の環境を整えます。

```sql
\i reset.sql
\i function.sql
```

## 実験

### 1.`BEFORE`と`AFTER`での違い

まずは`BEFORE`の場合です。

```sql
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`の場合です。

```sql
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`にして、トリガー関数の戻り値の影響を確認します。

```sql
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`の値は影響を受けないようなので、この２つの使い分けを考える必要がある時はそんなに無さそうだと思いました。