SQL Server でテーブルの変更履歴を記録するには？ (ID:146453)

名前

ホームページ(ブログ、Twitterなど)のURL (省略可)

本文

お世話になります。
SQL Server 2005 で、テーブル「table1」の
追加、変更(前・後)、削除の履歴を、テーブル「log_table1」に
記録したいと思い、トリガーを作成しました。

作成したトリガーにて、1件ずつ追加、変更、削除を行った時の
履歴は出力できたのですが、テーブル「table1」の複数レコードを
一括更新するようなUPDATE文を発行したときに、エラーが発生します。

エラー内容：
メッセージ 2627、レベル 14、状態 1、プロシージャ trg_mst_busyo、行 38
制約 'log_mst_busyo_PK' の PRIMARY KEY 違反。オブジェクト 'dbo.log_mst_busyo' には重複したキーを挿入できません。
ステートメントは終了されました。

UPDATEで複数件を更新した場合も、正常にログテーブルが作成されるようにするには、
どうすれば宜しいでしょうか？

以下、トリガー内容です。
長くなってしまい申し訳ありません。

USE [TEST01]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE [dbo].[log_table1]
CREATE TABLE [dbo].[log_table1](
  [log_seq_no]     [numeric](10) NOT NULL ,
  [log_syori_ymd]  [datetime]    NOT NULL ,
  [log_syori_kbn]  [char](2)     NOT NULL ,  -- I:追加, U1:変更前, U2:変更後, D:削除
  [fld1]  [char](7)     NOT NULL ,
  [fld2]   [varchar](50) NOT NULL ,
  CONSTRAINT [log_table1_PK] PRIMARY KEY CLUSTERED (log_seq_no) ON [PRIMARY]
) ON [PRIMARY]
GO

DROP   TRIGGER [trg_table1]
GO

CREATE TRIGGER [trg_table1] ON [table1] AFTER INSERT,DELETE,UPDATE
AS BEGIN
  SET NOCOUNT ON;
  
  DECLARE @type1   AS char(1)       -- 判定用
  DECLARE @type2   AS char(2)       -- 挿入用
  DECLARE @cnt_ins AS numeric(3,0)  -- 挿入件数
  DECLARE @cnt_del AS numeric(3,0)  -- 削除件数
  DECLARE @seq     AS numeric(10,0)
  
  -- タイプを判定(削除数、挿入数により決定)
  SELECT @cnt_ins = COUNT(*) FROM inserted
  SELECT @cnt_del = COUNT(*) FROM deleted
  
  IF @cnt_del = 0
    SET @type1 = 'I'
  ELSE
  BEGIN
    IF @cnt_ins = 0
      SET @type1 = 'D'
    ELSE
      SET @type1 = 'U'
  END
  
  SET @type2 = @type1
  
  SELECT @seq = COALESCE(MAX(log_seq_no),0) + 1 FROM [dbo].[log_table1]
  
  -- 履歴を挿入(削除データ)
  IF 0 < @cnt_del
    BEGIN
      IF @type1 = 'U'
        SET @type2 = 'U1'
      
      INSERT INTO [dbo].[log_table1]
        SELECT
         @seq
        ,current_timestamp
        ,@type2
        ,[fld1]
        ,[fld2]
        FROM deleted
      ;
      SET @seq = @seq + 1;
    END
  
  -- 履歴を挿入(追加データ)
  IF 0 < @cnt_ins
    BEGIN
      IF @type1 = 'U'
        SET @type2 = 'U2'
      
      INSERT INTO [dbo].[log_table1]
        SELECT
         @seq
        ,current_timestamp
        ,@type2
        ,[fld1]
        ,[fld2]
        FROM inserted
      ;
      SET @seq = @seq + 1;
    END
  
END

←解決時は質問者本人がここをチェックしてください。

戻る