Zenn
Open2

SQLServerのトリガーでupdated_atを自動的に更新する

SQL Server
Nakano as a ServiceNakano as a Service
CREATE TRIGGER [set_user_updated_at]
ON [user] 
AFTER UPDATE
AS BEGIN
    UPDATE 
        [user] 
    SET 
        [updated_at] = sysdatetimeoffset() 
    FROM
        [user] as T
        JOIN [INSERTED] as INS
            ON T.[id] = INS.[id]
END;
Nakano as a ServiceNakano as a Service

以下の方法はIN句を使っているので、1000件以上同時に更新されるとエラーになるため、好ましくない。

CREATE TRIGGER [set_user_updated_at]
ON [user] 
AFTER UPDATE
AS BEGIN
    UPDATE 
        [user] 
    SET 
        [updated_at] = sysdatetimeoffset()
    WHERE [id] IN (SELECT [id] FROM inserted)
END;
```