CLR 触发器

2017-06-13

由于 SQL Server 与 .NET Framework 公共语言运行时（CLR）集成，因此可以使用任何 .NET Framework 语言创建 CLR 触发器。本部分介绍特定于使用 CLR 集成实现的触发器的信息。有关触发器的完整讨论，请参阅 DDL 触发器。

什么是触发器？

触发器是一种特殊的存储过程类型，可在语言事件执行时自动运行。 SQL Server 包括两种常规类型的触发器：数据作语言（DML）和数据定义语言（DDL）触发器。当指定UPDATE表或视图中的数据修改时INSERT，DELETE可以使用 DML 触发器。 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无法公开构造该类，只能通过访问 SqlContext.TriggerContext CLR 触发器正文中的属性来获取该类。 SqlTriggerContext可以通过调用SqlContext.TriggerContext属性从活动SqlContext中获取该类：

SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;

该 SqlTriggerContext 类提供有关触发器的上下文信息。此上下文信息包括导致触发器触发的作类型，在 UPDATE作中修改了哪些列，在 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 枚举”。

使用插入表和已删除表

DML 触发器语句中使用了两个特殊表：插入的表和 已删除 的表。 SQL Server 会自动创建和管理这些表。可以使用这些临时表来测试某些数据修改的效果，并设置 DML 触发器作的条件;但是，不能直接更改表中的数据。

CLR 触发器可以通过 CLR 进程内提供程序访问插入和删除的表。这可以通过从 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"

确定更新的列

可以使用对象的属性确定 UPDATE作 ColumnCount 修改的 SqlTriggerContext 列数。可以使用将 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 语句，这些语句主要是以 CREATE、ALTER 和 DROP 开头的语句。 DDL 触发器可用于管理任务，例如审核和监视数据库作和架构更改。

有关触发 DDL 触发器的事件的信息在类的属性SqlTriggerContext中EventData可用。此属性包含一个 xml 值。 xml 架构包括有关以下内容的信息：

事件时间。
在其中执行触发器的连接的系统进程 ID （SPID）。
触发触发器的事件的类型。

然后，根据事件类型，架构包含其他信息，例如发生事件的数据库、发生事件的对象以及事件的 Transact-SQL 命令。

在以下示例中，以下 DDL 触发器返回原始 EventData 属性。

注释

在此处显示通过 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

以下示例输出是在 EventData 事件触发 CREATE TABLE DDL 触发器后属性值：

<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

假设存在两个表，具有以下定义：

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 块是必需的，以便执行可以继续。尽管引发了两个异常，但事务会回滚，并且更改不会提交到表。 CLR 触发器和 Transact-SQL 触发器之间的一个主要区别在于，在回滚事务后，Transact-SQL 触发器可以继续执行更多工作。

示例：

以下触发器对表执行 INSERT 语句的简单验证。如果插入的整数值等于 1，则会回滚事务，并且该值不会插入到表中。所有其他整数值都插入到表中。请注意方法周围的 Transaction.Rollback try/catch 块。 Transact-SQL 脚本创建测试表、程序集和托管存储过程。请注意，这两个 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;

另请参阅

CREATE TRIGGER （Transact-SQL）
DML 触发器
 DDL 触发器
 尝试。。。CATCH （Transact-SQL）
使用公共语言运行时（CLR）集成生成数据库对象
 EVENTDATA （Transact-SQL）

通过

CLR 触发器

什么是触发器？

CLR 触发器的独特功能

使用 SqlTriggerContext 类

确定触发器作

使用插入表和已删除表

确定更新的列

访问 CLR DDL 触发器的 EventData

示例 CLR 触发器

验证和取消无效事务

示例：

另请参阅

其他资源