如何：创建和运行 CLR SQL Server 触发器
更新：2007 年 11 月
通过向 SQL Server 项目添加“触发器”创建 SQL 触发器。成功部署后，就可以像其他任何 T-SQL 触发器一样调用和执行在托管代码中创建的触发器。使用托管语言编写的触发器可以使用 SqlTriggerContext 类获得对相关信息的访问，这些信息与 T-SQL 触发器的可用信息相同。
.gif "说明") 说明： |
|---|
默认情况下，公共语言运行时 (CLR) 集成功能在 Microsoft SQL Server 中处于关闭状态，必须启用该功能才能使用 SQL Server 项目项。若要启用 CLR 集成，请使用 sp_configure 存储过程的 clr enabled 选项。有关更多信息，请参见启用 CLR 集成。 |
| 说明： |
|---|
对于在以下说明中使用的某些 Visual Studio 用户界面元素，您的计算机可能会显示不同的名称或位置。这些元素取决于您使用的 Visual Studio 版本及设置。有关更多信息，请参见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 中。
查看“输出”窗口中显示的结果，然后选择“显示以下输出: 数据库输出”。
示例
此示例演示以下这种情况：用户选择他们需要的任何用户名，但是您希望知道哪些用户输入了电子邮件地址作为用户名。此触发器检测该信息并将它记录到审核表。
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports 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 Function
End Class
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]+$");
}
}
#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 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
请参见
任务
如何：创建和运行 CLR SQL Server 用户定义的函数
如何：创建和运行 CLR SQL Server 用户定义的类型