CLR トリガー

[アーティクル]
03/13/2017

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

トリガーとは

トリガーとは、言語イベントの実行時に自動的に実行される、特殊なストアドプロシージャです。 SQL Server には、DML (データ操作言語) トリガーと DDL (データ定義言語) トリガーという&2; 種類の一般的なトリガーがあります。 DML トリガーを指定できる際に使用される挿入、更新、または削除ステートメントが指定されたテーブルまたはビューのデータを変更します。 DDL トリガーは、さまざまなで始まるステートメントを中心の DDL ステートメントに応じてストアドプロシージャを起動作成、 ALTER、およびドロップします。 DDL トリガーは、データベース操作の監査や管理などの管理作業に使用できます。

CLR トリガー独自の機能

記述されたトリガーTransact-SQLを起動するビューやテーブルの列を使用してによって更新されているかを調べる機能がある、 **UPDATE(column)とCOLUMNS_UPDATED()**関数です。

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

データを参照、 INSERTEDとDELETEDテーブル
結果として変更された列の判断、更新操作
DDL ステートメントの実行によって影響を受けたデータベースオブジェクトに関する情報へのアクセス

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

SqlTriggerContext クラスの使用

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

SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;

SqlTriggerContextクラスは、トリガーに関するコンテキスト情報を提供します。このコンテキスト情報には、更新操作、および、DDL トリガー、XML の場合に変更された列に、トリガーを発生させたアクションの種類が含まれています。 EventDataトリガー操作を記述する構造体。詳細については、次を参照してください。 EVENTDATA (TRANSACT-SQL)します。

トリガー動作の判断

入手した場合、 SqlTriggerContext、それを使用するには、トリガーを起動した動作の種類を判断します。この情報は、使用、 TriggerActionのプロパティ、 SqlTriggerContextクラスです。

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

TriggerAction.Update (0x1)
TriggerAction.Insert (0x2)
TriggerAction.Delete(0x3)
DDL トリガーの場合、TriggerAction の有効な値は非常に多くなります。詳細については、.NET Framework SDK の「TriggerAction Enumeration」を参照してください。

Inserted テーブルと Deleted テーブルの使用

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

CLR トリガーにアクセスできる、挿入と削除CLR インプロセスプロバイダーを使用してテーブルです。取得することによってこれは、 SqlCommand SqlContext オブジェクトからのオブジェクト。例:

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 操作によって変更された列の数を決定する、 ColumnCountのプロパティ、 SqlTriggerContextオブジェクトです。使用することができます、 IsUpdatedColumnを列の序数、列が更新されたかどうかを決定する、入力パラメーターとして受け取るメソッドです。 A 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 トリガーが起動されることはありません。代わりに、DDL トリガーは、さまざまな DDL ステートメントに応じて起動されます。このような DDL ステートメントは、主に CREATE、ALTER、DROP で始まるステートメントです。 DDL トリガーは、データベース操作やスキーマの変更の監査や管理などの管理作業に使用できます。

DDL トリガーを起動するイベントに関する情報が記載されて、 EventDataのプロパティ、 SqlTriggerContextクラスです。このプロパティを含む、 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

次のサンプル出力は、 EventDataによって生成される DDL トリガーの後のプロパティ値、 CREATE TABLEイベント。

<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  
)

Transact-SQLでトリガーを作成するステートメントSQL Serverは次のように、アセンブリを前提と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.

この例外はことにも、前後にある、try/catch ブロック、Transact-SQLトリガーを起動するアクションを実行するステートメントは、実行を継続できるようにする必要があります。この&2; つの例外がスローされても、トランザクションはロールバックされ、変更はテーブルにコミットされません。 CLR トリガーと Transact-SQL トリガーの主な違いは、トランザクションがロールバックされた後、Transact-SQL トリガーは、動作を継続してさらに実行を行えるということです。

例

次のトリガーでは、テーブルで INSERT ステートメントの簡単な検証を実行します。挿入された整数値が&1; に等しい場合、トランザクションはロールバックされ、値はテーブルに挿入されません。その他のすべての整数値はテーブルに挿入されます。 Try/catch ブロックの周囲に注意してください、 Transaction.Rollbackメソッドです。 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 クラスの使用

トリガー動作の判断

Inserted テーブルと Deleted テーブルの使用

更新された列の判断

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

サンプル CLR トリガー

無効なトランザクションの検証およびキャンセル

例

参照

その他のリソース