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.

7 Comments

Join the conversation! Your thoughts help the community grow.

J Dunnewijk6y ago
All works fine but now ... where are the the 'pipe.send' strings going to and how can I get the 'pipe.send' strings in my C# project code?
Dharmendra Kumar Pandit7y ago
Very useful things. Thank you for sharing this type of articles.
Dharmendra Kumar Pandit7y ago
Nice Articles
Hadshana Kamalanathan8y ago
Thank you for sharing
Rahul Borate8y ago
Very useful Article.
- Dinesh Kushwaha8y ago
  Thanks Rahul, It is nice to hear from you this article is very useful it motivate me create new useful articles.
- Viknaraj Manogararajah8y ago
  Nice explanation, thank you for sharing.