如何:通过使用公共语言运行时集成创建和运行 SQL Server 触发器
通过将**“触发器”**项添加到 SQL Server 公共语言运行时 (SQL CLR) 数据库项目,从而创建 SQL 触发器。 成功部署后,就可以像任何其他 Transact-SQL 触发器一样调用和执行在托管代码中创建的触发器。 用托管语言编写的触发器可以使用 SqlTriggerContext 类获得对相关信息的访问,这些信息与 Transact-SQL 触发器的可用信息相同。
提示
对于在以下说明中使用的某些 Visual Studio 用户界面元素,您的计算机可能会显示不同的名称或位置。这些元素取决于您所使用的 Visual Studio 版本和您所使用的设置。有关更多信息,请参见 Visual Studio 设置。
创建 SQL Server 触发器
创建 SQL Server 触发器
打开一个现有的**“SQL CLR 数据库项目”**,或者创建一个新项目。 有关更多信息,请参见如何:为使用 SQL Server 公共语言运行时集成的数据库对象创建项目。
在**“项目”菜单上选择“添加新项”**。
在**“添加新项”对话框中,选择“触发器”**。
键入新触发器的**“名称”**。
添加触发器执行时要运行的代码。 请参见此过程后面的第一个示例。
在**“解决方案资源管理器”中打开“TestScripts”**文件夹,并双击 Test.sql 文件。
提示
您可以将其他脚本指定为默认调试脚本。 有关更多信息,请参见如何:编辑 Test.sql 脚本以运行使用 SQL Server 公共语言运行时集成的对象。
将代码添加到 Test.sql 文件中以执行触发器。 请参见此过程后面的第二个示例。
按 F5 生成、部署并调试此触发器。 有关如何不进行调试而直接部署的信息,请参见如何:将 SQL CLR 数据库项目项部署到 SQL Server。
重要事项 SQL Server 2005 和 SQL Server 2008 只支持使用 .NET Framework 2.0、3.0 或 3.5 版生成的 SQL Server 项目。 如果您尝试部署SQL Server项目,SQL Server 2005或SQL Server 2008,将显示错误消息: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database(在进行校验是您要部署的程序集的名称)。 有关更多信息,请参见如何:为使用 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]+$");
}
}
向位于项目的 TestScripts 文件夹中的 Test.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
请参见
任务
如何:为使用 SQL Server 公共语言运行时集成的数据库对象创建项目
如何:通过使用公共语言运行时集成创建和运行 SQL Server 存储过程
如何:通过使用公共语言运行时集成创建和运行 SQL Server 聚合
如何:通过使用公共语言运行时集成创建和运行 SQL Server 用户定义的函数
如何:通过使用公共语言运行时集成创建和运行 SQL Server 用户定义的类型