none
SQL Serverのトリガの中で、DMLの種類(INSERT/UPDATE/DELETE)を取る方法ってありますか? RRS feed

  • 質問

  • こんにちは。アべです。

    下記のようなトリガを作成して、操作(INSERT/UPDATE/DELETE)の履歴を
    残したいのですが、トリガを3本に分けるのもあれなので、
    操作の種類(INSERT/UPDATE/DELETE)を履歴用のテーブルに入れたいのですが、
    書き方がわかりません。

    取得方法のやり方を教えてもらえないでしょか?

    ↓サンプルのコードです。
    CREATE TRIGGER [mst].[TriggerUserActionHistory] 
    ON [mst].[User] 
    AFTER INSERT, UPDATE, DELETE
    NOT FOR REPLICATION
    AS 
    BEGIN
    SET NOCOUNT ON;
    --ACTION TYPE未設定 ↓今はINSERT固定しかできない。
    insert into [history].[User] select 'INSERT',getdate(),* from inserted;
    END
    
    GO
    
    2011年3月10日 4:17

回答

  • 論理テーブルの件数から判定してはいかがでしょう?

    DECLARE @dml char(6);
    DECLARE @ins long;
    DECLARE @del long;
    
    SET @ins = (SELECT COUNT(*) FROM inserted);
    SET @del = (SELECT COUNT(*) FROM deleted);
    
    IF @ins = 0
     SET @dml = 'DELETE';
    ELSE IF @del = 0
     SET @dml = 'INSERT';
    ELSE
     SET @dml = 'UPDATE';
    
    
    • 回答としてマーク 山本春海 2011年3月22日 5:49
    2011年3月10日 6:51
  • トリガーは実際に更新されなくても実行されます。例えば、updateの結果、1行も更新する行がなくても実行されます。その場合、SELECT COUNT(*) FROM insertedは0を返しますから、DELETEと判断されてしまいそうです。
    existsを使った方がよさそうに思います。

    if exists(select * from insertd) and
       exists(select * from deleted)
       --Update
    else if exists (select * from inserted)
       --Insert
    else if exists (select * from deleted)
       --Delete
    else      
       --どれでもない。

    最後の「どれでもない」がちょっと不正確で、上記にも書いた通り、updateの結果、1行も更新する行がなくても「どれでもない」が表示されてしまいます。これが問題になる場合は、上記だけでは厳しいでしょう。

     


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 回答としてマーク 山本春海 2011年3月22日 5:54
    2011年3月11日 2:31
  • > updateの結果、1行も更新する行がなくても「どれでもない」が表示されてしまいます。

    DELETE もかな?

    もう1つ、別の問題として、inserted と deleted の論理テーブルでは、LOB オブジェクトや LOB として扱われる長い varchar/nvarchar にアクセスできないので、

    insert into [history].[User] select 'INSERT',getdate(),* from inserted;
    
    

    のような乱暴なコピーは問題になるかもしれません。

    • 回答としてマーク 山本春海 2011年3月22日 5:54
    2011年3月11日 3:26

すべての返信

  • 論理テーブルの件数から判定してはいかがでしょう?

    DECLARE @dml char(6);
    DECLARE @ins long;
    DECLARE @del long;
    
    SET @ins = (SELECT COUNT(*) FROM inserted);
    SET @del = (SELECT COUNT(*) FROM deleted);
    
    IF @ins = 0
     SET @dml = 'DELETE';
    ELSE IF @del = 0
     SET @dml = 'INSERT';
    ELSE
     SET @dml = 'UPDATE';
    
    
    • 回答としてマーク 山本春海 2011年3月22日 5:49
    2011年3月10日 6:51
  • <del>

    下記のページにEVENTDATA関数を使ってどのコマンドで動いたトリガであるかの履歴を取る例が記載されています。

    EVENTDATA (Transact-SQL)
    http://msdn.microsoft.com/ja-jp/library/ms173781(v=SQL.105).aspx

    </del>

    ↑大変申し訳ありません。ページを見て勝手にできるようになってたんだと早とちりしてしまいました。誤情報を記載してしまったことお詫び申し上げます。

    K.Takaokaさま>
    ご指摘感謝いたします

    • 編集済み ChukiMVP 2011年3月11日 0:44 誤情報
    2011年3月10日 11:08
  • EVETNDATA は INSERT/DELETE/UPDATE には使用できない仕様ですが、現状は INSERT/DELETE/UPDATE でも利用できる、という話でしょうか?

    2011年3月10日 23:40
  • EVETNDATA は INSERT/DELETE/UPDATE には使用できない仕様ですが、現状は INSERT/DELETE/UPDATE でも利用できる、という話でしょうか?

    ご指摘の通りでした。何をどう見間違ったのか、DMLトリガと読んでしまったため、できるようになったんだと早合点してしまいました。再度見てみたら、「DDLトリガ」と明記してありました。

    ご指摘ありがとうございました。

    アベさま>

    誤情報を記載してしまい申し訳ございませんでした。
    K.Takaokaさまの回答通り、SQL Serverに用意された特殊な2つのテーブル「inserted テーブルとdeleted テーブル」をご参照ください

    inserted テーブルと deleted テーブルの使用
    http://msdn.microsoft.com/ja-jp/library/ms191300.aspx

    2011年3月11日 0:56
  • K.Takaokaさん

    有難うございます。

    目的の処理が実装出来ました。

    Chukiさん

    EVENTDATA()は使ってみたのですが、NULLが返ってきたので、こちらは使用しませんでした。

     

    2011年3月11日 1:00
  • Chukiさん

    EVENTDATA()は使ってみたのですが、NULLが返ってきたので、こちらは使用しませんでした。

    結果情報ありがとうございます。

    誤情報を回答してしまい誠に申し訳ございませんでした。

     

    2011年3月11日 1:04
  • トリガーは実際に更新されなくても実行されます。例えば、updateの結果、1行も更新する行がなくても実行されます。その場合、SELECT COUNT(*) FROM insertedは0を返しますから、DELETEと判断されてしまいそうです。
    existsを使った方がよさそうに思います。

    if exists(select * from insertd) and
       exists(select * from deleted)
       --Update
    else if exists (select * from inserted)
       --Insert
    else if exists (select * from deleted)
       --Delete
    else      
       --どれでもない。

    最後の「どれでもない」がちょっと不正確で、上記にも書いた通り、updateの結果、1行も更新する行がなくても「どれでもない」が表示されてしまいます。これが問題になる場合は、上記だけでは厳しいでしょう。

     


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 回答としてマーク 山本春海 2011年3月22日 5:54
    2011年3月11日 2:31
  • > updateの結果、1行も更新する行がなくても「どれでもない」が表示されてしまいます。

    DELETE もかな?

    もう1つ、別の問題として、inserted と deleted の論理テーブルでは、LOB オブジェクトや LOB として扱われる長い varchar/nvarchar にアクセスできないので、

    insert into [history].[User] select 'INSERT',getdate(),* from inserted;
    
    

    のような乱暴なコピーは問題になるかもしれません。

    • 回答としてマーク 山本春海 2011年3月22日 5:54
    2011年3月11日 3:26
  • 皆様、アドバイスをありがとうございます。

    下記のように実装できました。

    CREATE TRIGGER [pla].[TriggerUserActionHistory] 
      ON [pla].[User] 
      AFTER INSERT, UPDATE, DELETE
     NOT FOR REPLICATION
    AS 
    BEGIN
     -- Memo:EventData()は、DDL用なので、INSERT/UPDATE/DELETEでは使えない。
     --   select eventdata();
     -- Memo:inserted, Deletedの論理テーブルは、LOBオブジェクトやLOBとして扱われる長い項目はアクセスできない。
     SET NOCOUNT ON; 
     --Action Typeの取得
     DECLARE @dml nvarchar(6);
     DECLARE @ins bigint;
     DECLARE @del bigint;
     SET @ins = (SELECT COUNT(*) FROM inserted);
     SET @del = (SELECT COUNT(*) FROM deleted);
     if @ins > 0 and @del > 0
     begin
     SET @dml = 'UPDATE';
     --ACTION TYPE
     insert into [his].[User] select getdate(), @dml, inserted.* from inserted;
     end
     else if @ins > 0
     begin
     SET @dml = 'INSERT';
     --ACTION TYPE
     insert into [his].[User] select getdate(), @dml, inserted.* from inserted;
     end 
     else if @del > 0
     begin
     SET @dml = 'DELETE';
     --ACTION TYPE
     insert into [his].[User] select getdate(), @dml, deleted.* from deleted;
     end
     --else
     --どれでもない。例)UPDATE=0件など
    END
    GO
    
    2011年3月11日 5:05