SQL Serverのトリガーでinsert時に作成日時、update時に更新日時をセットする

Question

SQL Serverのトリガーでinsert時に作成日時、update時に更新日時をセットする

件名の通り、トリガーによって、作成日時、更新日時を自動セットしたいです。

insertトリガーで作成日時をセットした場合、トリガー内のupdateによってupdateトリガーも実行されてしまい、更新日時も自動的にセットされます。
希望する動きとしては、insert時には更新日時をセットしたくありません。
Bingチャットでも聞いてみましたが、結局ダメでした。

CREATE TABLE YourTable
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    SomeData NVARCHAR(100),
    createDateTime DATETIME NULL,
    updateDateTime DATETIME NULL
)

CREATE TRIGGER trg_Insert_YourTable
ON YourTable
AFTER INSERT
AS
BEGIN
    UPDATE YourTable
    SET createDateTime = GETDATE()
    FROM YourTable
    INNER JOIN inserted ON YourTable.ID = inserted.ID
END

CREATE TRIGGER trg_Update_YourTable
ON YourTable
AFTER UPDATE
AS
BEGIN
    UPDATE YourTable
    SET updateDateTime = GETDATE()
    FROM YourTable
    INNER JOIN inserted ON YourTable.ID = inserted.ID
    WHERE YourTable.createDateTime IS NOT NULL
END

この状態で

insert into YourTable (SomeData) values ('test')

を実行すると、CreateDateTime, UpdateDateTimeの両方に日付がセットされます。

updateトリガー時にinsertトリガーが動いた結果であることが判別できれば良いと思うのですが、現在の記述では正しく機能していません。
仕組み上実現は可能でしょうか。

Answer 1

もう少しスマートなやり方がありそうですが、たとえば以下のような方法で実現できるかと思います。

1. INSERTトリガではupdateDateTime をダミー値で更新しておく (以下の例では'9999/12/31')
2. そのあとに動くUPDATEトリガでは、updateDateTimeにダミー値が入っていたらNULLに更新する。NULLもしくはダミー値以外の値が入っていたら、現在時刻に更新する。

CREATE  OR ALTER TRIGGER trg_Insert_YourTable
ON YourTable
AFTER INSERT
AS
BEGIN
    UPDATE YourTable
    SET createDateTime = GETDATE(), updateDateTime = '9999/12/31'
    FROM YourTable
    INNER JOIN inserted ON YourTable.ID = inserted.ID
END;


CREATE OR ALTER TRIGGER trg_Update_YourTable
ON YourTable
AFTER UPDATE
AS
BEGIN
    UPDATE YourTable
    SET updateDateTime = IIF(inserted.updateDateTime = '9999/12/31', NULL, GETDATE())
    FROM YourTable
    JOIN inserted ON YourTable.ID = inserted.ID
END;

Comments

[propg]

提示いただいた方法で対応できそうです。
INSERT直後のUPDATEであることをどこかに示せばいいと思いましたが、提示していただいた方法は分かりやすくてよいと思いました。