Export (0) Print
Expand All

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

The common language runtime (CLR) integration feature is off by default 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. From more information, see Enabling CLR Integration.

NoteNote

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

Creating the SQL Server Trigger

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

NoteNote

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

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

  2. Add code to the Test.sql (debug.sql in Visual C++) file to execute the trigger. See the second example below.

  3. Press F5 to build, deploy, and debug the trigger. For information on deploying without debugging, see How to: Deploy SQL Server Project Items to a SQL Server.

  4. View the results 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.

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]+$");
    }
}

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

See Also

Community Additions

ADD
Show:
© 2014 Microsoft