Triggerを使用して履歴テーブルを作ってみる

備忘録として後世の自分に託す

はじめに

SQL Server データベースプロジェクトで作成する
履歴を残すテーブルを作成する

例：Departmentテーブル

CREATE TABLE [dbo].[Department]
(
  [Id] INT NOT NULL PRIMARY KEY,
  [DepartmentName] NVARCHAR(10) NULL
)

今回はDepartmentテーブルに対してinserted, update, deleteなどの操作が行われたときに履歴を残すためにTriggerを使ってみる

履歴テーブルを作成する

まずは履歴を保存しておくテーブルを作っておく

例：DepartmentHistoryテーブル

CREATE TABLE [dbo].[DepartmentHistory]
(
  [Id] INT NOT NULL PRIMARY KEY,
  [DepartmentName] NVARCHAR(10) NULL,
  [Action] NVARCHAR(10) NULL,
  [UpdateDate] DATETIME2 NULL,
)

• [Action]：どんな操作が行われたかを保管する列
• [UpdateDate]：更新が行われ時間を保管する列

Triggerを作成して履歴を残す処理を実装する

Triggerを追加する
今回は一例としてDepartmentテーブルに対してUPDATEが走ったときに履歴テーブルへとデータをINSERTするような処理を書いてみます。

例：DepartmentUpdateTrigger

CREATE TRIGGER [DepartmentUpdateTrigger]
  ON [dbo].[Department] // どのテーブルに対して？
  FOR UPDATE // どんな操作が行われたら？
  AS
  BEGIN
    INSERT INTO
        [dbo].[DepartmentHistory](
          [Id],
          [DepartmentName],
          [Action],
          [UpdateDate]
      )
        SELECT
          [Id],
          [DepartmentName],
          'UPDATE',
          GETUTCDATE()
        FROM
          inserted
  END

• FOR: 今回の例ではUPDATEだが、INSERT、DELETEなどでも同じように描ける。
• inserted: insertedテーブルからSELECTして履歴テーブルへと登録している。
  • もしDELETE文がトリガーならば、ここはdeletedで実際にdeletedしたデータがテーブルとして表されている。

以上によりDepartmentテーブルに対してデータが更新されるとDepartmentHistoryテーブルに更新が走るので試してみてください。

まとめ

爆速で履歴作れたのでいい感じだと思いました。
データベースの知識が乏しく感じるのでもっと勉強せねばならない。