Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Important This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here.

How to: Create and Run a CLR SQL Server Trigger

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.

NoteNote:

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.

NoteNote:

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.

To create a SQL Server Trigger

  1. Open an existing SQL Server Project, or create a new one. For more information, see How to: Create a SQL Server Project.

  2. From the Project menu, select Add New Item.

  3. Select Trigger in the Add New Item Dialog Box.

  4. Type a Name for the new trigger.

  5. Add code to run when the trigger is executed. See the first example that follows this procedure.

    NoteNote:

    C++ examples must be compiled with the /clr:safe compiler option.

  6. 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.

  7. Add code to the Test.sql (debug.sql in Visual C++) file to execute the trigger. See the second example that follows this procedure.

  8. 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.

  9. View the results that are shown in the Output Window and select Show output from: Database Output.

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.

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

Community Additions

ADD
Show:
© 2015 Microsoft