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

解決

kanako 2009-11-03 01:59:28 No: 146453

お世話になります。
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

YuO 2009-11-03 02:47:38 No: 146454

これのどこにVBが関わるのか不明ですが……。

>   [log_seq_no]     [numeric](10) NOT NULL ,
これが主キーですよね。
ところが，
>       INSERT INTO [dbo].[log_table1]
>         SELECT
>          @seq
のように，一定の値をそのままINSERTしているためにその違反が発生しています。
定義を
[log_seq_no] INT IDENTITY PRIMARY KEY
のように，IDENTITY型にしてしまってはどうでしょうか。
# この項目，連続していれば問題ないですよね。

kanako 2009-11-03 03:41:20 No: 146455

YuO様、ご回答有難うございました。
IDENTITY型を指定し、@seqの部分を削除する事により、目的の動作になりました。

> これのどこにVBが関わるのか不明ですが……。

申し訳ありませんでした、SQL Serverの掲示板を探したのですが、
いい所が見つからず、こちらで質問させて頂きました。

kanako 2009-11-03 03:43:06 No: 146456

失礼しました。解決です。