CLR トリガー

2017-06-13

SQL Server と .NET Framework 共通言語ランタイム (CLR) の統合により、任意の .NET Framework 言語を使用して CLR トリガーを作成できます。このセクションでは、CLR 統合で実装されるトリガーに固有の情報について説明します。トリガーの詳細については、「 DDL トリガー」を参照してください。

トリガーとは

トリガーは、言語イベントの実行時に自動的に実行される特殊なストアドプロシージャです。 SQL Server には、データ操作言語 (DML) トリガーとデータ定義言語 (DDL) トリガーの 2 つの一般的な種類のトリガーが含まれています。 DML トリガーは、 INSERT、 UPDATE、または DELETE ステートメントが指定したテーブルまたはビューのデータを変更するときに使用できます。 DDL トリガーは、さまざまな DDL ステートメント (主に CREATE、 ALTER、および DROPで始まるステートメント) に応答してストアドプロシージャを起動します。 DDL トリガーは、データベース操作の監査や規制などの管理タスクに使用できます。

CLR トリガーの固有の機能

Transact-SQL で記述されたトリガーには、 UPDATE(column) 関数と COLUMNS_UPDATED() 関数を使用して、更新された起動ビューまたはテーブルの列を決定する機能があります。

CLR 言語で記述されたトリガーは、いくつかの重要な点で他の CLR 統合オブジェクトとは異なります。 CLR トリガーでは、次のことができます。

INSERTEDテーブルとDELETED テーブル内の参照データ
UPDATE操作の結果として変更された列を特定する
DDL ステートメントの実行の影響を受けるデータベースオブジェクトに関する情報にアクセスします。

これらの機能は、本質的にクエリ言語で提供されるか、 SqlTriggerContext クラスによって提供されます。 CLR 統合の利点と、マネージドコードと Transact-SQL の選択については、「 CLR 統合の概要」を参照してください。

SqlTriggerContext クラスの使用

SqlTriggerContext クラスはパブリックに構築できず、CLR トリガーの本体内の SqlContext.TriggerContext プロパティにアクセスすることによってのみ取得できます。 SqlTriggerContext クラスは、SqlContext.TriggerContext プロパティを呼び出すことによって、アクティブなSqlContextから取得できます。

SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;

SqlTriggerContext クラスは、トリガーに関するコンテキスト情報を提供します。このコンテキスト情報には、トリガーの起動の原因となったアクションの種類、UPDATE 操作で変更された列、DDL トリガーの場合は、トリガー操作を記述する XML EventData 構造体が含まれます。詳細については、 EVENTDATA (Transact-SQL) を参照してください。

トリガーアクションの決定

SqlTriggerContextを取得したら、それを使用して、トリガーの起動の原因となったアクションの種類を特定できます。この情報は、SqlTriggerContext クラスの TriggerAction プロパティを通じて使用できます。

DML トリガーの場合、 TriggerAction プロパティには次のいずれかの値を指定できます。

TriggerAction.Update (0x1)
TriggerAction.Insert (0x2)
TriggerAction.Delete(0x3)
DDL トリガーの場合、使用可能な TriggerAction 値の一覧が大幅に長くなります。詳細については、.NET Framework SDK の「TriggerAction 列挙型」を参照してください。

挿入テーブルと削除済みテーブルの使用

DML トリガーステートメントでは、挿入されたテーブルと 削除された テーブルという 2 つの特殊なテーブルが使用されます。 SQL Server では、これらのテーブルが自動的に作成および管理されます。これらの一時テーブルを使用して、特定のデータ変更の影響をテストしたり、DML トリガーアクションの条件を設定したりできます。ただし、テーブル内のデータを直接変更することはできません。

CLR トリガーは、CLR インプロセスプロバイダーを介して挿入および 削除された テーブルにアクセスできます。これを行うには、SqlContext オブジェクトから SqlCommand オブジェクトを取得します。例えば次が挙げられます。

SqlConnection connection = new SqlConnection ("context connection = true");  
connection.Open();  
SqlCommand command = connection.CreateCommand();  
command.CommandText = "SELECT * from " + "inserted";

Visual Basic

Dim connection As New SqlConnection("context connection=true")  
Dim command As SqlCommand  
connection.Open()  
command = connection.CreateCommand()  
command.CommandText = "SELECT * FROM " + "inserted"

更新された列の決定

update 操作によって変更された列の数は、SqlTriggerContext オブジェクトの ColumnCount プロパティを使用して確認できます。列の序数を入力パラメーターとして受け取る IsUpdatedColumn メソッドを使用して、列が更新されたかどうかを判断できます。 True値は、列が更新されたことを示します。

たとえば、このコードスニペット (このトピックの後半の EmailAudit トリガーから) は、更新されたすべての列を一覧表示します。

reader = command.ExecuteReader();  
reader.Read();  
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)  
{  
   pipe.Send("Updated column "  
      + reader.GetName(columnNumber) + "? "  
   + triggContext.IsUpdatedColumn(columnNumber).ToString());  
 }  
  
 reader.Close();

Visual Basic

reader = command.ExecuteReader()  
reader.Read()  
Dim columnNumber As Integer  
  
For columnNumber=0 To triggContext.ColumnCount-1  
  
   pipe.Send("Updated column " & reader.GetName(columnNumber) & _  
   "? " & triggContext.IsUpdatedColumn(columnNumber).ToString() )  
  
Next  
  
reader.Close()

CLR DDL トリガーの EventData へのアクセス

DDL トリガーは、通常のトリガーと同様に、イベントに応答してストアドプロシージャを起動します。ただし、DML トリガーとは異なり、テーブルまたはビューの UPDATE、INSERT、または DELETE ステートメントに応答しても起動しません。代わりに、さまざまな DDL ステートメント (主に CREATE、ALTER、DROP で始まるステートメント) に応答して起動します。 DDL トリガーは、データベース操作やスキーマ変更の監査や監視などの管理タスクに使用できます。

DDL トリガーを発生するイベントに関する情報は、SqlTriggerContext クラスの EventData プロパティで使用できます。このプロパティには、 xml 値が含まれています。 xml スキーマには、次に関する情報が含まれています。

イベントの時刻。
トリガーが実行された接続のシステムプロセス ID (SPID)。
トリガーを発生したイベントの種類。

その後、イベントの種類に応じて、スキーマには、イベントが発生したデータベース、イベントが発生したオブジェクト、イベントの Transact-SQL コマンドなどの追加情報が含まれます。

次の例では、次の DDL トリガーは生の EventData プロパティを返します。

注

ここでは、 SqlPipe オブジェクトを介して結果とメッセージを送信する方法を説明するためにのみ示します。CLR トリガーをプログラミングする場合は、通常、実稼働コードでは推奨されません。返される追加のデータが予期せず、アプリケーションエラーにつながる可能性があります。

using System;  
using System.Data;  
using System.Data.Sql;  
using Microsoft.SqlServer.Server;  
using System.Data.SqlClient;  
using System.Data.SqlTypes;  
using System.Xml;  
using System.Text.RegularExpressions;  
  
public class CLRTriggers  
{  
   public static void DropTableTrigger()  
   {  
       SqlTriggerContext triggContext = SqlContext.TriggerContext;             
  
       switch(triggContext.TriggerAction)  
       {  
           case TriggerAction.DropTable:  
           SqlContext.Pipe.Send("Table dropped! Here's the EventData:");  
           SqlContext.Pipe.Send(triggContext.EventData.Value);  
           break;  
  
           default:  
           SqlContext.Pipe.Send("Something happened! Here's the EventData:");  
           SqlContext.Pipe.Send(triggContext.EventData.Value);  
           break;  
       }  
   }  
}

Visual Basic

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class CLRTriggers   
  
    Public Shared Sub DropTableTrigger()  
        Dim triggContext As SqlTriggerContext  
        triggContext = SqlContext.TriggerContext  
  
        Select Case triggContext.TriggerAction  
           Case TriggerAction.DropTable  
              SqlContext.Pipe.Send("Table dropped! Here's the EventData:")  
              SqlContext.Pipe.Send(triggContext.EventData.Value)  
  
           Case Else  
              SqlContext.Pipe.Send("Something else happened! Here's the EventData:")  
              SqlContext.Pipe.Send(triggContext.EventData.Value)  
  
        End Select  
    End Sub  
End Class

次の出力例は、CREATE TABLE イベントによって発生した DDL トリガーの後のEventDataプロパティ値です。

<EVENT_INSTANCE><PostTime>2004-04-16T21:17:16.160</PostTime><SPID>58</SPID><EventType>CREATE_TABLE</EventType><ServerName>MACHINENAME</ServerName><LoginName>MYDOMAIN\myname</LoginName><UserName>MYDOMAIN\myname</UserName><DatabaseName>AdventureWorks</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>UserName</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create table dbo.UserName ( UserName varchar(50), RealName varchar(50) ) </CommandText></TSQLCommand></EVENT_INSTANCE>

SqlTriggerContext クラスからアクセスできる情報に加えて、クエリはCOLUMNS_UPDATEDを参照し、インプロセスで実行されたコマンドのテキスト内で挿入/削除することもできます。

CLR トリガーのサンプル

この例では、ユーザーに任意の ID を選択させるが、電子メールアドレスを ID として具体的に入力したユーザーを知りたいシナリオを考えてみましょう。次のトリガーでは、その情報が検出され、監査テーブルにログが記録されます。

注

ここでは、 SqlPipe オブジェクトを介して結果とメッセージを送信する方法を例示目的でのみ示します。運用環境のコードでは一般的に推奨されません。返される追加のデータが予期せず、アプリケーションエラーにつながる可能性があります

using System;  
using System.Data;  
using System.Data.Sql;  
using Microsoft.SqlServer.Server;  
using System.Data.SqlClient;  
using System.Data.SqlTypes;  
using System.Xml;  
using System.Text.RegularExpressions;  
  
public class CLRTriggers  
{  
   [SqlTrigger(Name = @"EmailAudit", Target = "[dbo].[Users]", Event = "FOR INSERT, UPDATE, DELETE")]  
   public static void EmailAudit()  
   {  
      string userName;  
      string realName;  
      SqlCommand command;  
      SqlTriggerContext triggContext = SqlContext.TriggerContext;  
      SqlPipe pipe = SqlContext.Pipe;  
      SqlDataReader reader;  
  
      switch (triggContext.TriggerAction)  
      {  
         case TriggerAction.Insert:  
         // Retrieve the connection that the trigger is using  
         using (SqlConnection connection  
            = new SqlConnection(@"context connection=true"))  
         {  
            connection.Open();  
            command = new SqlCommand(@"SELECT * FROM INSERTED;",  
               connection);  
            reader = command.ExecuteReader();  
            reader.Read();  
            userName = (string)reader[0];  
            realName = (string)reader[1];  
            reader.Close();  
  
            if (IsValidEMailAddress(userName))  
            {  
               command = new SqlCommand(  
                  @"INSERT [dbo].[UserNameAudit] VALUES ('"  
                  + userName + @"', '" + realName + @"');",  
                  connection);  
               pipe.Send(command.CommandText);  
               command.ExecuteNonQuery();  
               pipe.Send("You inserted: " + userName);  
            }  
         }  
  
         break;  
  
         case TriggerAction.Update:  
         // Retrieve the connection that the trigger is using  
         using (SqlConnection connection  
            = new SqlConnection(@"context connection=true"))  
         {  
            connection.Open();  
            command = new SqlCommand(@"SELECT * FROM INSERTED;",  
               connection);  
            reader = command.ExecuteReader();  
            reader.Read();  
  
            userName = (string)reader[0];  
            realName = (string)reader[1];  
  
            pipe.Send(@"You updated: '" + userName + @"' - '"  
               + realName + @"'");  
  
            for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)  
            {  
               pipe.Send("Updated column "  
                  + reader.GetName(columnNumber) + "? "  
                  + triggContext.IsUpdatedColumn(columnNumber).ToString());  
            }  
  
            reader.Close();  
         }  
  
         break;  
  
         case TriggerAction.Delete:  
            using (SqlConnection connection  
               = new SqlConnection(@"context connection=true"))  
               {  
                  connection.Open();  
                  command = new SqlCommand(@"SELECT * FROM DELETED;",  
                     connection);  
                  reader = command.ExecuteReader();  
  
                  if (reader.HasRows)  
                  {  
                     pipe.Send(@"You deleted the following rows:");  
                     while (reader.Read())  
                     {  
                        pipe.Send(@"'" + reader.GetString(0)  
                        + @"', '" + reader.GetString(1) + @"'");  
                     }  
  
                     reader.Close();  
  
                     //alternately, to just send a tabular resultset back:  
                     //pipe.ExecuteAndSend(command);  
                  }  
                  else  
                  {  
                     pipe.Send("No rows affected.");  
                  }  
               }  
  
               break;  
            }  
        }  
  
     public static bool IsValidEMailAddress(string email)  
     {  
         return Regex.IsMatch(email, @"^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$");  
     }  
}

Visual Basic

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
Imports System.Text.RegularExpressions  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class CLRTriggers   
  
    <SqlTrigger(Name:="EmailAudit", Target:="[dbo].[Users]", Event:="FOR INSERT, UPDATE, DELETE")> _  
    Public Shared Sub EmailAudit()  
        Dim userName As String  
        Dim realName As String  
        Dim command As SqlCommand  
        Dim triggContext As SqlTriggerContext  
        Dim pipe As SqlPipe  
        Dim reader As SqlDataReader    
  
        triggContext = SqlContext.TriggerContext      
        pipe = SqlContext.Pipe    
  
        Select Case triggContext.TriggerAction  
           Case TriggerAction.Insert  
              Using connection As New SqlConnection("context connection=true")  
                 connection.Open()  
                 command = new SqlCommand("SELECT * FROM INSERTED;", connection)  
  
                 reader = command.ExecuteReader()  
                 reader.Read()  
  
                 userName = CType(reader(0), String)  
                 realName = CType(reader(1), String)  
  
                 reader.Close()  
  
                 If IsValidEmailAddress(userName) Then  
                     command = New SqlCommand("INSERT [dbo].[UserNameAudit] VALUES ('" & _  
                       userName & "', '" & realName & "');", connection)  
  
                    pipe.Send(command.CommandText)  
                    command.ExecuteNonQuery()  
                    pipe.Send("You inserted: " & userName)  
  
                 End If  
              End Using  
  
           Case TriggerAction.Update  
              Using connection As New SqlConnection("context connection=true")  
                 connection.Open()  
                 command = new SqlCommand("SELECT * FROM INSERTED;", connection)  
  
                 reader = command.ExecuteReader()  
                 reader.Read()  
  
                 userName = CType(reader(0), String)  
                 realName = CType(reader(1), String)  
  
                 pipe.Send("You updated: " & userName & " - " & realName)  
  
                 Dim columnNumber As Integer  
  
                 For columnNumber=0 To triggContext.ColumnCount-1  
  
                    pipe.Send("Updated column " & reader.GetName(columnNumber) & _  
                      "? " & triggContext.IsUpdatedColumn(columnNumber).ToString() )  
  
                 Next  
  
                 reader.Close()  
              End Using  
  
           Case TriggerAction.Delete  
              Using connection As New SqlConnection("context connection=true")  
                 connection.Open()  
                 command = new SqlCommand("SELECT * FROM DELETED;", connection)  
  
                 reader = command.ExecuteReader()  
  
                 If reader.HasRows Then  
                    pipe.Send("You deleted the following rows:")  
  
                    While reader.Read()  
  
                       pipe.Send( reader.GetString(0) & ", " & reader.GetString(1) )  
  
                    End While   
  
                    reader.Close()  
  
                    ' Alternately, just send a tabular resultset back:  
                    ' pipe.ExecuteAndSend(command)  
  
                 Else  
                   pipe.Send("No rows affected.")  
                 End If  
  
              End Using   
        End Select  
    End Sub  
  
    Public Shared Function IsValidEMailAddress(emailAddress As String) As Boolean  
  
       return Regex.IsMatch(emailAddress, "^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$")  
    End Function      
End Class

次の定義を持つ 2 つのテーブルが存在すると仮定します。

CREATE TABLE Users  
(  
    UserName nvarchar(200) NOT NULL,  
    RealName nvarchar(200) NOT NULL  
);  
GO CREATE TABLE UserNameAudit  
(  
    UserName nvarchar(200) NOT NULL,  
    RealName nvarchar(200) NOT NULL  
)

SQL Server でトリガーを作成する Transact-SQL ステートメントは次のとおりです。アセンブリ SQLCLRTest が現在の SQL Server データベースに既に登録されていることを前提としています。

CREATE TRIGGER EmailAudit  
ON Users  
FOR INSERT, UPDATE, DELETE  
AS  
EXTERNAL NAME SQLCLRTest.CLRTriggers.EmailAudit

無効なトランザクションの検証と取り消し

トリガーを使用して、無効な INSERT、UPDATE、または DELETE トランザクションを検証して取り消したり、データベーススキーマの変更を防いだりするのが一般的です。これは、検証ロジックをトリガーに組み込み、アクションが検証条件を満たしていない場合に現在のトランザクションをロールバックすることで実現できます。

トリガー内で呼び出されると、コマンドテキスト "TRANSACTION ROLLBACK" を指定した Transaction.Rollback メソッドまたは SqlCommand は、あいまいなエラーメッセージを含む例外をスローし、try/catch ブロックにラップする必要があります。表示されるエラーメッセージは次のようになります。

Msg 6549, Level 16, State 1, Procedure trig_InsertValidator, Line 0  
A .NET Framework error occurred during execution of user defined routine or aggregate 'trig_InsertValidator':   
System.Data.SqlClient.SqlException: Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting... User transaction, if any, will be rolled back.

この例外は想定されるものであり、コードの実行を継続するには try/catch ブロックが必要です。トリガーコードの実行が完了すると、別の例外が発生します

Msg 3991, Level 16, State 1, Procedure trig_InsertValidator, Line 1   
The context transaction which was active before entering user defined routine, trigger or aggregate "trig_InsertValidator" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting.  
The statement has been terminated.

この例外も予想されます。実行を続行するには、トリガーを起動するアクションを実行する Transact-SQL ステートメントの try/catch ブロックが必要です。 2 つの例外がスローされたにもかかわらず、トランザクションはロールバックされ、変更はテーブルにコミットされません。 CLR トリガーと Transact-SQL トリガーの主な違いは、トランザクションがロールバックされた後も、Transact-SQL トリガーが引き続きより多くの作業を実行できることです。

例

次のトリガーは、テーブルに対して INSERT ステートメントの単純な検証を実行します。挿入された整数値が 1 の場合、トランザクションはロールバックされ、値はテーブルに挿入されません。他のすべての整数値がテーブルに挿入されます。 Transaction.Rollback メソッドの周囲の try/catch ブロックに注意してください。 Transact-SQL スクリプトは、テストテーブル、アセンブリ、およびマネージドストアドプロシージャを作成します。 2 つの INSERT ステートメントが try/catch ブロックにラップされているため、トリガーの実行が完了したときにスローされた例外がキャッチされることに注意してください。

using System;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;  
using System.Transactions;  
  
public partial class Triggers  
{  
    // Enter existing table or view for the target and uncomment the attribute line  
    // [Microsoft.SqlServer.Server.SqlTrigger (Name="trig_InsertValidator", Target="Table1", Event="FOR INSERT")]  
    public static void trig_InsertValidator()  
    {  
        using (SqlConnection connection = new SqlConnection(@"context connection=true"))  
        {  
            SqlCommand command;  
            SqlDataReader reader;  
            int value;  
  
            // Open the connection.  
            connection.Open();  
  
            // Get the inserted value.  
            command = new SqlCommand(@"SELECT * FROM INSERTED", connection);  
            reader = command.ExecuteReader();  
            reader.Read();  
            value = (int)reader[0];  
            reader.Close();  
  
            // Rollback the transaction if a value of 1 was inserted.  
            if (1 == value)  
            {  
                try  
                {  
                    // Get the current transaction and roll it back.  
                    Transaction trans = Transaction.Current;  
                    trans.Rollback();                      
                }  
                catch (SqlException ex)  
                {  
                    // Catch the expected exception.                      
                }  
            }  
            else  
            {  
                // Perform other actions here.  
            }  
  
            // Close the connection.  
            connection.Close();              
        }  
    }  
}

Visual Basic

Imports System  
Imports System.Data.SqlClient  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Transactions  
  
Partial Public Class Triggers  
' Enter existing table or view for the target and uncomment the attribute line  
' <Microsoft.SqlServer.Server.SqlTrigger(Name:="trig_InsertValidator", Target:="Table1", Event:="FOR INSERT")> _  
Public Shared Sub  trig_InsertValidator ()  
    Using connection As New SqlConnection("context connection=true")  
  
        Dim command As SqlCommand  
        Dim reader As SqlDataReader  
        Dim value As Integer  
  
        ' Open the connection.  
        connection.Open()  
  
        ' Get the inserted value.  
        command = New SqlCommand("SELECT * FROM INSERTED", connection)  
        reader = command.ExecuteReader()  
        reader.Read()  
        value = CType(reader(0), Integer)  
        reader.Close()  
  
        ' Rollback the transaction if a value of 1 was inserted.  
        If value = 1 Then  
  
            Try  
                ' Get the current transaction and roll it back.  
                Dim trans As Transaction  
                trans = Transaction.Current  
                trans.Rollback()  
  
            Catch ex As SqlException  
  
                ' Catch the exception.                      
            End Try  
        Else  
  
            ' Perform other actions here.  
        End If  
  
        ' Close the connection.  
        connection.Close()  
    End Using  
End Sub  
End Class

Transact-SQL

-- Create the test table, assembly, and trigger.  
CREATE TABLE Table1(c1 int);  
go  
  
CREATE ASSEMBLY ValidationTriggers from 'E:\programming\ ValidationTriggers.dll';  
go  
  
CREATE TRIGGER trig_InsertValidator  
ON Table1  
FOR INSERT  
AS EXTERNAL NAME ValidationTriggers.Triggers.trig_InsertValidator;  
go  
  
-- Use a Try/Catch block to catch the expected exception  
BEGIN TRY  
   INSERT INTO Table1 VALUES(42)  
   INSERT INTO Table1 VALUES(1)  
END TRY  
BEGIN CATCH  
  SELECT ERROR_NUMBER() AS ErrorNum, ERROR_MESSAGE() AS ErrorMessage  
END CATCH;  
  
-- Clean up.  
DROP TRIGGER trig_InsertValidator;  
DROP ASSEMBLY ValidationTriggers;  
DROP TABLE Table1;

こちらもご覧ください

CREATE TRIGGER (Transact-SQL)
DML トリガー
 DDL トリガー
 試みる。。。CATCH (Transact-SQL)
共通言語ランタイム (CLR) 統合を使用したデータベースオブジェクトの構築
 EVENTDATA (Transact-SQL)

次の方法で共有

CLR トリガー

トリガーとは

CLR トリガーの固有の機能

SqlTriggerContext クラスの使用

トリガー アクションの決定

挿入テーブルと削除済みテーブルの使用

更新された列の決定

CLR DDL トリガーの EventData へのアクセス

CLR トリガーのサンプル

無効なトランザクションの検証と取り消し

例

こちらもご覧ください

その他のリソース

トリガーアクションの決定