How To Create CLR Triggers In C# And Install And Uninstall CLR Assembly In MSSQL Server

Introduction

In this article, we will learn how to create CLR Triggers in C# and install & uninstall CLR assembly in MSSQL Server. I have created an article on the basics of CLR procedure. If you want to know more about the basics of CLR procedure, visit this link:

• Basic of CLR procedure

Step -1

Create a library project in Visual Studio as below.

Create one class file as “Trigger_UserEmailAudit.cs”.

In the file “Trigger_UserEmailAudit.cs”, add the below line of code.

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

namespace CLRProcedures
{
    public class Trigger_UserEmailAudit
    {
        [SqlTrigger(Name = @"EmailAudit", Target = "[dbo].[Users]", Event = "FOR INSERT, UPDATE, DELETE")]
        public static void EmailAudit()
        {
            string userName;
            string realName;
            string emailaddress;

            SqlCommand command;
            SqlTriggerContext triggContext = SqlContext.TriggerContext;
            SqlPipe pipe = SqlContext.Pipe;
            SqlDataReader reader;

            switch (triggContext.TriggerAction)
            {
                case TriggerAction.Insert:
                    // Retrieve the connection that the trigger is using
                    using (SqlConnection connection= new SqlConnection(@"context connection=true"))
                    {
                        connection.Open();
                        command = new SqlCommand(@"SELECT * FROM INSERTED;",connection);
                        reader = command.ExecuteReader();
                        reader.Read();

                        userName = (string)reader[1];
                        realName = (string)reader[2];
                        emailaddress= (string)reader[3];



                        reader.Close();

                        if (IsValidEMailAddress(emailaddress))
                        {
                            command = new SqlCommand(
                               @"INSERT [dbo].[UserNameAudit] VALUES ('"
                               + userName + @"', '" + realName + @"');",
                               connection);
                            pipe.Send(command.CommandText);
                            command.ExecuteNonQuery();
                            pipe.Send("You inserted: " + userName);
                        }
                    }

                    break;

                case TriggerAction.Update:
                    // Retrieve the connection that the trigger is using
                    using (SqlConnection connection = new SqlConnection(@"context connection=true"))
                    {
                        connection.Open();
                        command = new SqlCommand(@"SELECT * FROM INSERTED;",connection);
                        reader = command.ExecuteReader();
                        reader.Read();

                        userName = (string)reader[1];
                        realName = (string)reader[2];
                        emailaddress = (string)reader[3];


                        pipe.Send(@"You updated: '" + userName + @"' - '" + realName + @"'");

                        for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
                        {
                            pipe.Send("Updated column "
                               + reader.GetName(columnNumber) + "? "
                               + triggContext.IsUpdatedColumn(columnNumber).ToString());
                        }

                        reader.Close();
                    }

                    break;

                case TriggerAction.Delete:
                    using (SqlConnection connection= new SqlConnection(@"context connection=true"))
                    {
                        connection.Open();
                        command = new SqlCommand(@"SELECT * FROM DELETED;",connection);
                        reader = command.ExecuteReader();

                        if (reader.HasRows)
                        {
                            pipe.Send(@"You deleted the following rows:");
                            while (reader.Read())
                            {
                                pipe.Send(@"'" + reader.GetString(0)
                                + @"', '" + reader.GetString(1) + @"'");
                            }

                            reader.Close();

                            //alternately, to just send a tabular resultset back:
                            //pipe.ExecuteAndSend(command);
                        }
                        else
                        {
                            pipe.Send("No rows affected.");
                        }
                    }

                    break;
            }
        }

        public static bool IsValidEMailAddress(string email)
        {
            return true;
        }
    }
}

Step 2

Open the project property and set output path= C:\dlls, as below.

Step 3

Build or compile the library project and verify the dll that was copied in output folder (C:\dlls).

Step 4

To install CLR trigger in MSSQL Server, open a new query window for the targeted database in MSSQL Server Management Studio and add the below SQL statement in the query window.

To create CLR trigger, you must create an assembly in MSSQL server by running the below SQL statement.

CREATE ASSEMBLY ValidationTriggers from 'c:\dlls\CLRProcedures.dll' WITH PERMISSION_SET = SAFE

“Trigger_UserEmailAudit.cs” class is with namespace in file “Trigger_UserEmailAudit.cs”. To create CLR trigger with namespace, run the below SQL statement.

CREATE TRIGGER trig_InsertValidator
ON Users
FOR INSERT
AS EXTERNAL NAME ValidationTriggers.[CLRProcedures.Trigger_UserEmailAudit].EmailAudit;

You can invoke clr trigger by inserting, updating, and deleting records in user table as below:

INSERT RECORDS IN USERS TABLE.

BEGIN TRY
   INSERT INTO Users (UserName,RealName,EmailAddress,ModifiedDate) VALUES('dinesh.singh','Dinesh','[email protected]',getdate())
   INSERT INTO Users (UserName,RealName,EmailAddress,ModifiedDate) VALUES('ramesh.singh','Ramesh','[email protected]',getdate())
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS ErrorNum, ERROR_MESSAGE() AS ErrorMessage
END CATCH;

UPDATE RECORDS IN USERS TABLE.

BEGIN TRY
   UPDATE Users SET UserName='DINESH.SINGH_UPDATED' WHERE UserName='dinesh.singh'
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS ErrorNum, ERROR_MESSAGE() AS ErrorMessage
END CATCH;

DELETE RECORDS FROM USERS TABLE.

BEGIN TRY
   DELETE FROM Users
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS ErrorNum, ERROR_MESSAGE() AS ErrorMessage
END CATCH;

While running above SQL DML statement, if you get the below error,

It means clr is disabled. The ability to execute common language runtime (CLR) code is set to OFF by default in SQL Server. The CLR code can be enabled by using the sp_configure system stored procedure.

To enable CLR integration, use the clr enabled option of the sp_configure stored procedure in SQL Server Management Studio:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Now, you can run CLR trigger successfully.

Before running Clr trigger, let us see both the tables.

Let’s invoke the CLR trigger by inserting a few records in user table.

From the above screenshot, you can see two rows inserted in “Users” table and two rows inserted in “UserNameAudit” table.

Similarly, you can invoke clr trigger by updating & deleting records from the Users table.

Step -5

To uninstall CLR trigger in MSSQL Server, open the new query window for the targeted database in MSSQL Server Management Studio and add below sql statement in the query window.

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'trig_InsertValidator')
   DROP TRIGGER  trig_InsertValidator

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'ValidationTriggers')
   drop assembly ValidationTriggers

Now, clr trigger “trig_InsertValidator ” and assembly “ValidationTriggers” has been removed from MSSQL Server Management Studio object explorer as below.

Congratulations! You have successfully created, installed, and uninstalled the CLR trigger. If you have any query or concern, just do let me know or just put it in the comment box and I will respond as soon as possible. I am open to discussing anything even silly questions as well. If you have any suggestions related to this article, please let me know so that I can improve this article to a maximum level. 

Summary

In this article, we have learned how to create CLR Trigger in C#, & install & uninstall CLR assembly in MSSQL Server.