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:

Step -1

Create a library project in Visual Studio as below.

SQL Server

Create one class file as “Trigger_UserEmailAudit.cs”.

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

  1. using Microsoft.SqlServer.Server;  
  2. using System.Data.SqlClient;  
  3.   
  4. namespace CLRProcedures  
  5. {  
  6.     public class Trigger_UserEmailAudit  
  7.     {  
  8.         [SqlTrigger(Name = @"EmailAudit", Target = "[dbo].[Users]", Event = "FOR INSERT, UPDATE, DELETE")]  
  9.         public static void EmailAudit()  
  10.         {  
  11.             string userName;  
  12.             string realName;  
  13.             string emailaddress;  
  14.   
  15.             SqlCommand command;  
  16.             SqlTriggerContext triggContext = SqlContext.TriggerContext;  
  17.             SqlPipe pipe = SqlContext.Pipe;  
  18.             SqlDataReader reader;  
  19.   
  20.             switch (triggContext.TriggerAction)  
  21.             {  
  22.                 case TriggerAction.Insert:  
  23.                     // Retrieve the connection that the trigger is using    
  24.                     using (SqlConnection connection= new SqlConnection(@"context connection=true"))  
  25.                     {  
  26.                         connection.Open();  
  27.                         command = new SqlCommand(@"SELECT * FROM INSERTED;",connection);  
  28.                         reader = command.ExecuteReader();  
  29.                         reader.Read();  
  30.   
  31.                         userName = (string)reader[1];  
  32.                         realName = (string)reader[2];  
  33.                         emailaddress= (string)reader[3];  
  34.   
  35.   
  36.   
  37.                         reader.Close();  
  38.   
  39.                         if (IsValidEMailAddress(emailaddress))  
  40.                         {  
  41.                             command = new SqlCommand(  
  42.                                @"INSERT [dbo].[UserNameAudit] VALUES ('"  
  43.                                + userName + @"', '" + realName + @"');",  
  44.                                connection);  
  45.                             pipe.Send(command.CommandText);  
  46.                             command.ExecuteNonQuery();  
  47.                             pipe.Send("You inserted: " + userName);  
  48.                         }  
  49.                     }  
  50.   
  51.                     break;  
  52.   
  53.                 case TriggerAction.Update:  
  54.                     // Retrieve the connection that the trigger is using    
  55.                     using (SqlConnection connection = new SqlConnection(@"context connection=true"))  
  56.                     {  
  57.                         connection.Open();  
  58.                         command = new SqlCommand(@"SELECT * FROM INSERTED;",connection);  
  59.                         reader = command.ExecuteReader();  
  60.                         reader.Read();  
  61.   
  62.                         userName = (string)reader[1];  
  63.                         realName = (string)reader[2];  
  64.                         emailaddress = (string)reader[3];  
  65.                          
  66.   
  67.                         pipe.Send(@"You updated: '" + userName + @"' - '" + realName + @"'");  
  68.   
  69.                         for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)  
  70.                         {  
  71.                             pipe.Send("Updated column "  
  72.                                + reader.GetName(columnNumber) + "? "  
  73.                                + triggContext.IsUpdatedColumn(columnNumber).ToString());  
  74.                         }  
  75.   
  76.                         reader.Close();  
  77.                     }  
  78.   
  79.                     break;  
  80.   
  81.                 case TriggerAction.Delete:  
  82.                     using (SqlConnection connection= new SqlConnection(@"context connection=true"))  
  83.                     {  
  84.                         connection.Open();  
  85.                         command = new SqlCommand(@"SELECT * FROM DELETED;",connection);  
  86.                         reader = command.ExecuteReader();  
  87.   
  88.                         if (reader.HasRows)  
  89.                         {  
  90.                             pipe.Send(@"You deleted the following rows:");  
  91.                             while (reader.Read())  
  92.                             {  
  93.                                 pipe.Send(@"'" + reader.GetString(0)  
  94.                                 + @"', '" + reader.GetString(1) + @"'");  
  95.                             }  
  96.   
  97.                             reader.Close();  
  98.   
  99.                             //alternately, to just send a tabular resultset back:    
  100.                             //pipe.ExecuteAndSend(command);  
  101.                         }  
  102.                         else  
  103.                         {  
  104.                             pipe.Send("No rows affected.");  
  105.                         }  
  106.                     }  
  107.   
  108.                     break;  
  109.             }  
  110.         }  
  111.   
  112.         public static bool IsValidEMailAddress(string email)  
  113.         {  
  114.             return true;  
  115.         }  
  116.     }  
  117. }  

Step 2

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

SQL Server

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.

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

  1. CREATE TRIGGER trig_InsertValidator    
  2. ON Users    
  3. FOR INSERT    
  4. 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.

  1. BEGIN TRY    
  2.    INSERT INTO Users (UserName,RealName,EmailAddress,ModifiedDate) VALUES('dinesh.singh','Dinesh','a@b.com',getdate())    
  3.    INSERT INTO Users (UserName,RealName,EmailAddress,ModifiedDate) VALUES('ramesh.singh','Ramesh','a@b.com',getdate())    
  4. END TRY    
  5. BEGIN CATCH    
  6.   SELECT ERROR_NUMBER() AS ErrorNum, ERROR_MESSAGE() AS ErrorMessage    
  7. END CATCH;   

UPDATE RECORDS IN USERS TABLE.

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

DELETE RECORDS FROM USERS TABLE.

  1. BEGIN TRY    
  2.    DELETE FROM Users  
  3. END TRY    
  4. BEGIN CATCH    
  5.   SELECT ERROR_NUMBER() AS ErrorNum, ERROR_MESSAGE() AS ErrorMessage    
  6. END CATCH;   

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

SQL Server

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:

  1. sp_configure 'show advanced options', 1;    
  2. GO    
  3. RECONFIGURE;    
  4. GO    
  5. sp_configure 'clr enabled', 1;    
  6. GO    
  7. RECONFIGURE;    
  8. GO    

Now, you can run CLR trigger successfully.

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

SQL Server

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

SQL Server

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.

  1. IF EXISTS (SELECT name FROM sysobjects WHERE name = 'trig_InsertValidator')    
  2.    DROP TRIGGER  trig_InsertValidator    
  3.   
  4. IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'ValidationTriggers')    
  5.    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.