如何：通过使用公共语言运行时集成创建和运行 SQL Server 触发器

项目
08/12/2011

通过将**“触发器”**项添加到 SQL Server 公共语言运行时 (SQL CLR) 数据库项目，从而创建 SQL 触发器。成功部署后，就可以像任何其他 Transact-SQL 触发器一样调用和执行在托管代码中创建的触发器。用托管语言编写的触发器可以使用 SqlTriggerContext 类获得对相关信息的访问，这些信息与 Transact-SQL 触发器的可用信息相同。

提示

对于在以下说明中使用的某些 Visual Studio 用户界面元素，您的计算机可能会显示不同的名称或位置。这些元素取决于您所使用的 Visual Studio 版本和您所使用的设置。有关更多信息，请参见 Visual Studio 设置。

创建 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 公共语言运行时集成的数据库对象创建项目。

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

请参见

Share via

如何：通过使用公共语言运行时集成创建和运行 SQL Server 触发器

创建 SQL Server 触发器

创建 SQL Server 触发器

示例

请参见

任务

参考

概念

其他资源

其他资源