How to: Create and Run a CLR SQL Server Trigger

Article
11/16/2012

Create a SQL trigger by adding a Trigger item to a SQL server project. After successful deployment, triggers created in managed code are called and executed like any other T-SQL trigger. Triggers written in a managed language can use the SqlTriggerContext class to obtain access to the same information that is available to T-SQL triggers.

Note

By default, the common language runtime (CLR) integration feature is off in Microsoft SQL Server and must be enabled in order to use SQL Server project items. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure. For more information, see Enabling CLR Integration.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Creating the SQL Server Trigger

To create a SQL Server Trigger

Open an existing SQL Server Project, or create a new one. For more information, see How to: Create a SQL Server Project.
From the Project menu, select Add New Item.
Select Trigger in the Add New Item Dialog Box.
Type a Name for the new trigger.
Add code to run when the trigger is executed. See the first example that follows this procedure.

Note

C++ examples must be compiled with the /clr:safe compiler option.
For Visual Basic and Visual C#, in Solution Explorer, open the TestScripts folder and double-click the Test.sql file.

For Visual C++, in Solution Explorer, double-click the debug.sql file.
Add code to the Test.sql (debug.sql in Visual C+) file to execute the trigger. See the second example that follows this procedure.
Press F5 to build, deploy, and debug the trigger. For information about deploying without debugging, see How to: Deploy SQL Server Project Items to a SQL Server.
View the results that are shown in the Output Window and select Show output from: Database Output.

Example

This example demonstrates the scenario where the users choose any user name they want, but you want to know which users entered an e-mail address as a user name. This trigger detects that information and logs it to an audit table.

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.Value.ToString) Then
                    sqlComm.Parameters.Add(userName)
                    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.Value.ToString()))
                {
                    sqlComm.Parameters.Add(userName);
                    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]+$");
    }
};

Add code to execute and test your trigger to the Test.sql (debug.sql in Visual C+) file in the TestScripts folder in your project. For example, if you deployed a trigger, you can test it by running a script that inserts a new row into the table on which the trigger is set causing the trigger to fire. The following debugging code assumes that two tables exist with the following definitions:

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

How to: Create and Run a CLR SQL Server Trigger

Creating the SQL Server Trigger

To create a SQL Server Trigger

Example

See Also

Tasks

Concepts

Reference

Other Resources

Additional resources