SQLServerで特定のテーブルの変更履歴全部残したいときの備忘録

#はじめに
とあるシステムでこんな操作すると変な動きするんだけど。。。
問い合わせが来て、調べてみるとデータ不整合。
でもなんでこうなるかわからなくて対象のテーブルの変更履歴を全部残して調査した時のメモ

#流れをざっくりと
①対象のテーブルの履歴を残すテーブルを作る。
　→「テーブル名+History」という名前で作成

②対象のテーブルにトリガを作成
　→INSERT、UPDATE、DELETEそれぞれを契機に履歴テーブルにレコードをINSERT

③変な動きをするデータをひたすら追う！

#実際にやること
何も考えずに１と２のSQLを流すだけでとりあえずは履歴取れるようになります。

###1.履歴を残すストアドを作成

以下のSQLを流して履歴を残す設定をするストアドを作成します。
中身は履歴を残したいテーブル名を引数に渡すと履歴テーブルを作ってトリガを設定するものです。
詳しいことは後述します。

履歴残すストアド作る.sql

--履歴テーブルを作って履歴を残す対象テーブルにトリガを作成するストアド
CREATE PROCEDURE sp_CreateHistoryTable
    @TargetTable NVARCHAR(30)
AS
BEGIN

--履歴テーブル名(履歴を残したいテーブル名+History)
DECLARE @HistoryTable NVARCHAR(34) = @TargetTable + 'History'

--履歴テーブルを作成（対象テーブルのカラム構造のみコピー）
exec('SELECT * INTO ' + @HistoryTable + ' FROM ' + @TargetTable + ' WHERE 1 = 0')

--履歴テーブルにカラム追加
exec('ALTER TABLE ' + @HistoryTable + ' ADD process_datetime datetime, process_action nvarchar(6), process_order tinyint')


--対象テーブルにトリガを作成

--INSERTのトリガ作成
 exec ('
CREATE TRIGGER AddInsertHistory_' + @TargetTable + '
ON ' + @TargetTable + '
AFTER INSERT
AS
BEGIN

    DECLARE @ProcessDateTime DATETIME = GETDATE(),
            @ProcessAction   NVARCHAR(20) =''INSERT'';

    INSERT INTO ' + @HistoryTable + '
    select *,@ProcessDateTime AS process_datetime,@ProcessAction AS process_action ,NULL AS process_order from inserted

END
')
--UPDATEのトリガ作成
exec('
CREATE TRIGGER AddUpdateHistory_' + @TargetTable + '
ON ' + @TargetTable + '
AFTER UPDATE
AS
BEGIN

    DECLARE @ProcessDateTime DATETIME = GETDATE(),
            @ProcessAction   NVARCHAR(20) =''UPDATE'';

    INSERT INTO ' + @HistoryTable + '

        SELECT *,@ProcessDateTime AS process_datetime,@ProcessAction AS process_action ,1 AS process_order FROM deleted
        UNION ALL
        SELECT *,@ProcessDateTime AS process_datetime,@ProcessAction AS process_action ,2 AS process_order FROM inserted
        ORDER BY process_order;

END
')
--DELETEのトリガを作成
exec('
CREATE TRIGGER AddDeleteHistory_' + @TargetTable + '
ON ' + @TargetTable + '
AFTER DELETE
AS
BEGIN

    DECLARE @ProcessDateTime DATETIME = GETDATE(),
            @ProcessAction   NVARCHAR(20) =''DELETE'';

    INSERT INTO ' + @HistoryTable + '
    select *,@ProcessDateTime AS process_datetime,@ProcessAction AS process_action ,NULL AS process_order from deleted

END
')

END;

###2.履歴を残すストアドを実行
上で作ったストアドに履歴を取りたいテーブル名を渡して履歴を残す設定をします。

履歴残す.sql

EXEC dbo.sp_CreateHistoryTable @TargetTable = 'Table1'
EXEC dbo.sp_CreateHistoryTable @TargetTable = 'Table2'

#やったことの中身詳しく
（上に書いてることを分割しているだけです。）

###1.履歴を貯めるテーブルを作る
履歴を残したい対象のテーブル構造のみコピー。
「WHERE 1 = 0」を消すとデータも丸ごとコピーできるSQL。便利。

履歴を貯めるテーブルを作成.sql

SELECT * INTO @HistoryTable FROM @TargetTable WHERE 1 = 0

###２.履歴を貯めるテーブルに必要なカラムを追加する。

ここではprocess_datetime、process_action、process_orderの３つを追加。

①process_datetime　→　更新日時
②process_action　→　INSERT or UPDATE or DELETE
③process_order　→　UPDATEの場合、更新前'1'、更新後'2'、INSERT、DELETEはnull

履歴を貯めるテーブルにカラム追加.sql

ALTER TABLE @HistoryTable ADD process_datetime datetime, process_action nvarchar(6), process_order tinyint

###対象のテーブルにトリガを作成
INSERT,UPDATE,DELETEそれぞれとトリガにして履歴テーブルにデータを登録するようにします。

--INSERTのトリガ作成
 exec ('
CREATE TRIGGER AddInsertHistory_' + @TargetTable + '
ON ' + @TargetTable + '
AFTER INSERT
AS
BEGIN

    DECLARE @ProcessDateTime DATETIME = GETDATE(),
            @ProcessAction   NVARCHAR(20) =''INSERT'';

    INSERT INTO ' + @HistoryTable + '
    select *,@ProcessDateTime AS process_datetime,@ProcessAction AS process_action ,NULL AS process_order from inserted

END
')
--UPDATEのトリガ作成
exec('
CREATE TRIGGER AddUpdateHistory_' + @TargetTable + '
ON ' + @TargetTable + '
AFTER UPDATE
AS
BEGIN

    DECLARE @ProcessDateTime DATETIME = GETDATE(),
            @ProcessAction   NVARCHAR(20) =''UPDATE'';

    INSERT INTO ' + @HistoryTable + '

        SELECT *,@ProcessDateTime AS process_datetime,@ProcessAction AS process_action ,1 AS process_order FROM deleted
        UNION ALL
        SELECT *,@ProcessDateTime AS process_datetime,@ProcessAction AS process_action ,2 AS process_order FROM inserted
        ORDER BY process_order;

END
')
--DELETEのトリガを作成
exec('
CREATE TRIGGER AddDeleteHistory_' + @TargetTable + '
ON ' + @TargetTable + '
AFTER DELETE
AS
BEGIN

    DECLARE @ProcessDateTime DATETIME = GETDATE(),
            @ProcessAction   NVARCHAR(20) =''DELETE'';

    INSERT INTO ' + @HistoryTable + '
    select *,@ProcessDateTime AS process_datetime,@ProcessAction AS process_action ,NULL AS process_order from deleted

END
')

#おわりに
アプリにデバックログを仕込んで様子を見る方法もありますが、
漏れる可能性もあるので、対象のテーブルが分かっている場合は、
全量データを取るのが良いのかなと思います。
（終わったらトリガと履歴テーブルを削除をしないとデータ量がとんでもないことに。。。）