CLR トリガ
SQL Server と .NET Framework CLR (共通言語ランタイム) との統合により、任意の .NET Framework 言語を使用して CLR トリガを作成できるようになりました。ここでは、CLR 統合によって実装されたトリガ固有の情報について説明します。トリガの詳細については、「DML トリガについて」および「DDL トリガについて」を参照してください。
トリガとは
トリガとは、言語イベントの実行時に自動的に実行される、特殊なストアド プロシージャです。SQL Server には、DML (データ操作言語) トリガと DDL (データ定義言語) トリガという 2 種類の一般的なトリガがあります。DML トリガは、INSERT ステートメント、UPDATE ステートメント、または DELETE ステートメントにより、指定されたテーブルやビューのデータが変更されるときに使用できます。DDL トリガは、主に CREATE、ALTER、および DROP で始まるさまざまな DDL ステートメントに応じてストアド プロシージャを起動します。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 Enumeration」を参照してください。
Inserted テーブルと Deleted テーブルの使用
DML トリガ ステートメントでは、inserted テーブルおよび deleted テーブルという 2 つの特殊なテーブルが使用されます。SQL Server では、これらのテーブルが自動的に作成および管理されます。これらの一時テーブルを使用して、あるデータ変更の影響を調べたり、DML トリガ動作の条件を設定することができます。ただし、このテーブル内のデータを直接変更することはできません。
CLR トリガは、CLR インプロセス プロバイダを使用して inserted テーブルと deleted テーブルにアクセスできます。この操作は、SqlContext オブジェクトから SqlCommand オブジェクトを取得することによって行います。次に例を示します。
C#
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"
更新された列の判断
SqlTriggerContext オブジェクトの ColumnCount プロパティを使用して、UPDATE 操作によって変更された列の数を判断できます。入力パラメータとして列序数を受け取る IsUpdatedColumn メソッドを使用すると、列が更新されたかどうかを判断できます。値 True は、列が更新されたことを示します。
たとえば、後半で示す EmailAudit トリガからの次のコードでは、更新されたすべての列が一覧されます。
C#
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 トリガを起動するイベントに関する情報は、SqlTriggerContext クラスの EventData プロパティで入手できます。このプロパティには、xml 値が含まれます。xml スキーマには、次の項目に関する情報が含まれています。
イベントの時刻。
トリガが実行されている間の接続のシステム プロセス ID (SPID)。
トリガを起動したイベントの種類。
イベントの種類に応じて、イベントが発生したデータベース、イベントが発生したオブジェクト、イベントの Transact-SQL コマンドなどの追加情報がスキーマに含まれます。
次の例では、DDL トリガは EventData プロパティをそのまま返します。
.gif "注意") 注意 |
|---|
SqlPipe オブジェクトを使用して結果やメッセージを送信する例は、説明をわかりやすくするために記載しているものです。通常、CLR トリガをプログラミングするときに、この処理を実稼働コードに実装することはお勧めしません。予期しない追加データが返され、アプリケーション エラーの原因となる場合があります。 |
C#
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、COLUMNS_INSERTED、および COLUMNS_DELETED を引き続き参照できます。
サンプル 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
| 注意 |
|---|
SQL Server 2005 以降、互換性レベルが "80" の SQL Server データベースでは、マネージ コードでユーザー定義型、ストアド プロシージャ、関数、集計、またはトリガを作成することはできません。SQL Server のこれらの CLR 統合機能を使用するには、sp_dbcmptlevel (Transact-SQL) ストアド プロシージャを使用してデータベースの互換性レベルを "100" に設定する必要があります。 |
無効なトランザクションの検証およびキャンセル
無効な 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 ブロックにラップされることに注意してください。
C#
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;