如何：创建和运行 CLR SQL Server 触发器

通过向 SQL Server 项目添加“触发器”创建 SQL 触发器。成功部署后，就可以像其他任何 T-SQL 触发器一样调用和执行在托管代码中创建的触发器。使用托管语言编写的触发器可以使用 SqlTriggerContext 类获得对相关信息的访问，这些信息与 T-SQL 触发器的可用信息相同。

注意; 在默认情况下，Microsoft SQL Server 中关闭了公共语言运行库 (CLR) 集成功能。必须启用该功能才能使用 SQL Server 项目项。若要启用 CLR 集成，请使用 sp_configure 存储过程的“启用 clr”选项。有关更多信息，请参见 启用 CLR 集成。注意; 显示的对话框和菜单命令可能会与帮助中的描述不同，具体取决于您现用的设置或版本。若要更改设置，请在“工具”菜单上选择“导入和导出设置”。有关更多信息，请参见 Visual Studio 设置。

创建 SQL Server 触发器创建 SQL Server 触发器打开一个现有的“SQL Server 项目”，或者创建一个新项目。有关更多信息，请参见 如何：创建 SQL Server 项目。

从“项目”菜单中选择“添加新项”。

在 “添加新项”对话框 中选择“触发器”。

键入新触发器的“名称”。

添加触发器执行时要运行的代码。请参见下面的第一个示例。

注意; C++ 示例在编译时必须使用 /clr:safe 编译器选项。

对于 Visual Basic 和 Visual C#，在“解决方案资源管理器”中，打开“TestScripts”文件夹，再双击“Test.sql”文件。

对于 Visual C++，在“解决方案资源管理器”中，双击“debug.sql”文件。

将代码添加到“Test.sql”（Visual C++ 中为“debug.sql”）文件中以执行触发器。请参见下面的第二个示例。

按 F5 生成、部署并调试此触发器。有关不进行调试直接部署的信息，请参见 如何：将 SQL Server 项目项部署到 SQL Server 中。

在 “输出”窗口 中查看结果，然后选择“从此处显示输出：数据库输出”。

示例此示例演示以下这种情况：用户选择他们需要的任何用户名，但是您希望知道哪些用户输入了电子邮件地址作为用户名。此触发器检测该信息并将它记录到审核表。

Visual Basic 复制代码Imports System.Data.SqlClientImports System.Text.RegularExpressionsImports Microsoft.SqlServer.Server

Partial Public Class Triggers

<SqlTrigger(Name:='UserNameAudit', Target:='Users', Event:='FOR INSERT')> _ Public Shared Sub UserNameAudit()

Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext() Dim userName As New SqlParameter('@username', SqlDbType.NVarChar)

If triggContext.TriggerAction = TriggerAction.Insert Then

Using conn As New SqlConnection('context connection=true')

conn.Open() Dim sqlComm As New SqlCommand Dim sqlP As SqlPipe = SqlContext.Pipe()

sqlComm.Connection = conn sqlComm.CommandText = 'SELECT UserName from INSERTED'

userName.Value = sqlComm.ExecuteScalar.ToString()

If IsEMailAddress(userName.ToString) Then sqlComm.CommandText = 'INSERT UsersAudit(UserName) VALUES(username)' sqlP.Send(sqlComm.CommandText) sqlP.ExecuteAndSend(sqlComm) End If End Using End If End Sub

Public Shared Function IsEMailAddress(ByVal s As String) As Boolean

Return Regex.IsMatch(s, '^([w-]+.)*?[w-]+@[w-]+.([w-]+.)*?[w]+$') End FunctionEnd ClassC# 复制代码using System.Data.SqlClient;using System.Text.RegularExpressions;using Microsoft.SqlServer.Server;

public partial class Triggers{ [SqlTrigger(Name='UserNameAudit', Target='Users', Event='FOR INSERT')] public static void UserNameAudit() { SqlTriggerContext triggContext = SqlContext.TriggerContext; SqlParameter userName = new SqlParameter('@username', System.Data.SqlDbType.NVarChar);

if (triggContext.TriggerAction == TriggerAction.Insert) { using (SqlConnection conn = new SqlConnection('context connection=true')) { conn.Open(); SqlCommand sqlComm = new SqlCommand(); SqlPipe sqlP = SqlContext.Pipe;

sqlComm.Connection = conn; sqlComm.CommandText = 'SELECT UserName from INSERTED';

userName.Value = sqlComm.ExecuteScalar().ToString();

if (IsEMailAddress(userName.ToString())) { sqlComm.CommandText = 'INSERT UsersAudit(UserName) VALUES(userName)'; sqlP.Send(sqlComm.CommandText); sqlP.ExecuteAndSend(sqlComm); } } } }

public static bool IsEMailAddress(string s) { return Regex.IsMatch(s, '^([w-]+.)*?[w-]+@[w-]+.([w-]+.)*?[w]+$'); }}C++ 复制代码#include 'stdafx.h'

#using <System.dll>#using <System.Data.dll>#using <System.Xml.dll>

using namespace System;using namespace System::Data;using namespace System::Data::Sql;using namespace System::Data::SqlClient;using namespace System::Data::SqlTypes;using namespace System::Text::RegularExpressions;using namespace Microsoft::SqlServer::Server;

// In order to debug your Trigger, add the following to your debug.sql file://// -- Insert one user name that is not an e-mail address and one that is// INSERT INTO Users(UserName, Pass) VALUES(N'someone', N'cnffjbeq')// INSERT INTO Users(UserName, Pass) VALUES(N'someone@example.com', N'cnffjbeq')//// -- check the Users and UsersAudit tables to see the results of the trigger// SELECT * FROM Users// SELECT * FROM UsersAudit//

public ref class AddNewTrigger{public: [SqlTrigger(Name='UserNameAudit', Target='Users', Event='FOR INSERT')] static void UserNameAudit() { SqlTriggerContext ^triggContext = SqlContext::TriggerContext; SqlParameter ^userName = gcnew SqlParameter('@username', System::Data::SqlDbType::NVarChar);

if (triggContext->TriggerAction == TriggerAction::Insert) { SqlConnection ^conn = gcnew SqlConnection('context connection=true'); conn->Open(); SqlCommand ^sqlComm = gcnew SqlCommand(); SqlPipe ^sqlP = SqlContext::Pipe;

sqlComm->Connection = conn; sqlComm->CommandText = 'SELECT UserName from INSERTED';

userName->Value = sqlComm->ExecuteScalar()->ToString();

if (IsEMailAddress(userName->ToString())) { sqlComm->CommandText = 'INSERT UsersAudit(UserName) VALUES(userName)'; sqlP->Send(sqlComm->CommandText); sqlP->ExecuteAndSend(sqlComm); }

conn->Close(); } }

static bool IsEMailAddress(String ^s) { return Regex::IsMatch(s, '^([w-]+.)*?[w-]+@[w-]+.([w-]+.)*?[w]+$'); }};

向位于项目的 TestScripts 文件夹中的 Test.sql 文件（Visual C++ 中为 debug.sql）添加代码以执行和测试您的触发器。例如，如果已部署了触发器，您可以通过运行脚本对其进行测试，该脚本向设置了此触发器的表中插入新行，从而可激发此触发器。以下调试代码假定存在具有以下定义的两个表：

CREATE TABLE Users

(

UserName NVARCHAR(200) NOT NULL,

Pass NVARCHAR(200) NOT NULL

)

CREATE TABLE UsersAudit

(

UserName NVARCHAR(200) NOT NULL

)

复制代码-- Insert one user name that is not an e-mail address and one that isINSERT INTO Users(UserName, Pass) VALUES(N'someone', N'cnffjbeq')INSERT INTO Users(UserName, Pass) VALUES(N'someone@example.com', N'cnffjbeq')

-- check the Users and UsersAudit tables to see the results of the triggerselect * from Usersselect * from UsersAudit