Build SQL Server CLR Triggers

By: Daniel Farina   |   Updated: 2022-02-04   |   Comments (1)   |   Related: > Triggers

Problem

You are a SQL developer working on a project that uses SQL Server as its database. You need to validate the data that is inserted into a table with a complex math algorithm. You find T-SQL inappropriate for that matter, so you try to find an alternative. In that search you see that in SQL Server you can create triggers using .NET code, but you want to see a real-world example to consolidate ideas. In this tutorial, I will introduce you to CLR triggers and provide an example.

Solution

SQL Server CLR integration allows SQL Server to use .NET framework to create stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. It is one of those features of SQL Server that is usually overlooked by DBAs and developers. We, as humans, have a tendency to develop biases. In such a way that a developer who usually works with .NET framework doesn't consider the idea of using .NET code to be executed in SQL Server. Instead, they prefer to keep .NET code in one bucket and "database stuff" (A.K.A. SQL code) in another.

On the other side, DBAs do the same thing and try to stay away from CLR integration. This is mostly because DBAs don't have the same number of tools to troubleshoot CLR code as they have for T-SQL.

Why use CLR?

There is a scenario were CLR triggers fit quite well. When you need to perform validations that require complex math calculations you will find it more suitable to use a CLR trigger instead of a traditional one. This is because coding complex calculations is easier in C# or Visual Basic .NET and also, it should be more performant to use those languages over T-SQL.

Creating our First CLR Trigger

Before starting with our triggers, we need to create the tables on which the triggers will fire and execute.

CREATE TABLE DummyTable
(
    ID INT IDENTITY(1, 1) PRIMARY KEY,
    Dummy VARCHAR(10) NULL
);
 
 
CREATE TABLE PhoneBlacklist
(
    IMEI VARCHAR(16) PRIMARY KEY,
    Created DATE NULL,
    Status VARCHAR(10) NULL,
    Model VARCHAR(50) NULL,
    Country VARCHAR(50) NULL
);

In order to proceed you will need to obtain a copy of Visual Studio which you can download from here. After downloading and installing your copy of Visual Studio, open the application and create a new "SQL Server Database Project" as shown in the image below.

After pressing the Next button, the New Project Wizard will prompt you for a project name and a location to save it. I named the project "CLR_Trigger", but you can use a name of your preference.

Finally, with the project created, go to the Solution Explorer window and right click on any part of it. You will see a context menu pop up in which you must select the "Add" menu entry. Then another context menu will emerge and in this one you have to click "New Item…". A picture is worth a thousand words, so in the next screen capture you will see the previously stated graphically.

After pressing the "Add New Item…" entry of the context menu, you will see a new window open. This window allows you to choose amongst different kind of items to select the appropriate template that suits your needs. In this case we will select "SQL CLR C#" on the tree view at the leftmost side of the window and then we must select "SQL CLR C# Trigger" and we will name it "TrivialTrigger". Take a look at the next screen capture to see it more graphically.

Now repeat the process and add another SQLCLR C# Trigger, but this time with the name "IMEITrigger".

Finally, we will see both files TrivialTrigger.cs and IMEITrigger.cs created with some prebuilt code as can be seen in the following screen capture.

Analyzing the code, we can see that it creates a partial class named Triggers. Something that you may not be aware of is that making a partial class allows to define the class in multiple source files. So, in our project we will have a partial class named Triggers with one method named TrivialTrigger in one file (TrivialTrigger.cs) and another method named IMEITrigger in the other file (IMEITrigger.cs).

Before the definition of the TrivialTrigger method we see two commented lines asking us to complete the following properties of the Microsoft.SqlServer.Server.SqlTrigger attribute.

Something important to note is that there is no obligation to uncomment those lines and set a value for those properties. In fact, if we do it, we will see that the build of the project fails saying that the table we set in the Target attribute does not exist. That is because in the Visual Studio project we won't add a source file with the tables definition and therefore the build process won't be able to solve dependencies.

Inside the TrivialTrigger method template we can see that there is a call to a function named Send which is part of SqlPipe object that is a member of the SqlContext class. I know, it may sound a little confusing, but let's break it apart and go class by class.

The SqlContext Class

This is the main class that encloses and provides access to the SqlPipe and SqlTriggerContext objects. According to the documentation it "Represents an abstraction of the caller's context". In layman's terms, it allows us the interaction between our .NET code and the SQL Server session that executes our code.

The SqlPipe Class

This class allows us to send data to the client.

The SqlTriggerContext Class

As its name says, it provides information about the context that fired the trigger. The next table lists the properties of this class.

Additionally, the following table shows the methods (in this case it is only one).

Trivial Trigger

Here is the full code for our first and most basic trigger. It only contains a switch case block with the trigger actions insert, update and delete. Depending on the action that fires the trigger we use the SqlContext.Pipe.Send method to send a message to the client connection about the action performed.

In order to test this code, you have to replace the content of the file TrivialTrigger.cs with the code contained in the following code section.

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
 
public partial class Triggers
{
    // Enter existing table or view for the target and uncomment the attribute line 
    //[Microsoft.SqlServer.Server.SqlTrigger (Name="TrivialTrigger", Target= "[dbo].[DummyTable]", Event="FOR INSERT, UPDATE, DELETE")]
    public static void TrivialTrigger ()
    {
        SqlTriggerContext TrContext = SqlContext.TriggerContext;
        
        switch (TrContext.TriggerAction)
        {
            case TriggerAction.Insert:
                SqlContext.Pipe.Send("You inserted data.");
                break;
            case TriggerAction.Update:
                SqlContext.Pipe.Send("You updated data.");
                break;
            case TriggerAction.Delete:
                SqlContext.Pipe.Send("You deleted data.");
                break;
        }        
 
    }
}

Now let's build this project. Don't worry about the other file (IMEITrigger.cs), by leaving the default code template it won't interfere in the building process. In order to build this project, you have to go to the Build menu and select Rebuild Solution as can be seen in the next screen capture.

After building the solution you will have the DLL file in the ".\bin\Debug" folder of your solution. With this DLL we can create our assembly named CLR_Triggers with the following Transact SQL code. Remember to change the path to point to your DLL file.

CREATE ASSEMBLY [CLR_Triggers] FROM
'C:\Users\source\repos\CLR_Triggers\bin\Debug\CLR_Triggers.dll' WITH PERMISSION_SET=SAFE
GO

Finally, we can create the TR_DummyTable trigger on our dummy table.

CREATE TRIGGER TR_DummyTable    
ON dbo.DummyTable    
FOR INSERT, UPDATE, DELETE    
AS EXTERNAL NAME [CLR_Triggers].[Triggers].TrivialTrigger;
GO

The next image shows the execution of the previous statements.

If you never implemented a CLR module before you may be asking yourself about the origin of the name of the method specifier, i.e., why it is [CLR_Triggers].[Triggers].TrivialTrigger and not [something].[else].TrivialTrigger. Well, the first part [CLR_Triggers] is the name we used for the assembly in the CREATE ASSEMBLY statement. The second part [Triggers] is the name of the class that holds the TrivialTrigger function in the C# code. You can change it by modifying the name of the class.

Now its time to test the trigger and for that purpose we can run the following script.

INSERT INTO dbo.DummyTable  (Dummy) VALUES  ('Hello');
SELECT * FROM dbo.DummyTable;
 
UPDATE dbo.DummyTable SET Dummy += ' World' WHERE id = 1;
SELECT * FROM dbo.DummyTable
 
DELETE FROM dbo.DummyTable WHERE id = 1
SELECT * FROM dbo.DummyTable

As you can see in the next image, each DML operation printed a message as expected.

Trigger to Validate IMEI

Now let´s make a more realistic example. Suppose you have a table that holds a blacklist of stolen or missing phones. This table must keep the IMEI number of those devices, which is a unique number that identifies each cellphone. To check for a valid IMEI number we must use the Luhn algorithm.

Basically, the Luhn algorithm works like this:

• Take a string of numbers and starting from the right multiply each number by 2 if its position in the string is odd, or multiply by 1 otherwise.
• If the previous multiplication result is a two-digit number (for example 8 x 2 = 16), then sum the two digits (in our example 1 + 6 = 7).
• Sum the results of all of the previous multiplications and call it M.
• The check digit is 10 – (M mod 10). If the result of this formula is 10 (i.e. M mod 10 = 0) then the check digit is 0.

Let's take for example the IMEI number 334954835329660 that I made using an online tool that creates random IMEI numbers. On the next image you can see steps on how this is computed. The M value equals 80 which is all of the digits added together in the Intermediate Result.

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
 
public partial class Triggers
{
    // Enter existing table or view for the target and uncomment the attribute line
    // [Microsoft.SqlServer.Server.SqlTrigger (Name="IMEITrigger", Target="Table1", Event="INSTEAD OF INSERT")]
    public static void IMEITrigger()
    {
        SqlCommand cmdRead;
        SqlCommand cmdInsert;
 
        SqlDataAdapter adapter = new SqlDataAdapter();
        DataSet dst = new DataSet();
 
 
        SqlTriggerContext TrContext = SqlContext.TriggerContext;
        using (SqlConnection sqlCnn = new SqlConnection("context connection=true")  )
        {
            sqlCnn.Open();
            cmdRead = new SqlCommand("SELECT IMEI, Created, Status, Model, Country FROM INSERTED;", sqlCnn);
           
            adapter.SelectCommand = cmdRead;
            adapter.Fill(dst);
 
            if (!ValidateRows(dst))
            {
                InvalidCastException invalidCastException = new InvalidCastException("The value is not a valid IMEI number.");
                throw invalidCastException;
            }
 
            switch (TrContext.TriggerAction)
            {
                case TriggerAction.Insert:
                    cmdInsert = new SqlCommand(@"INSERT INTO dbo.PhoneBlacklist
                                                    (
                                                        IMEI,
                                                        Created,
                                                        Status,
                                                        Model,
                                                        Country
                                                    )
                                                    SELECT * FROM Inserted;", sqlCnn);
                    cmdInsert.ExecuteNonQuery();
                    break;
            }
        }
 
    }
 
    private static bool ValidateRows(DataSet dst)
    {
        foreach (DataRow dr in dst.Tables[0].Rows)
        {
            if (!CheckLuhn((string)dr["IMEI"]))
                  return false;
        }            
        return true;
    }
 
    private static bool CheckLuhn(string str)
    {
        int iDigit = 0;
        int iSum = 0;
 
        bool bIsOdd = false;
 
        for (int i = str.Length - 1; i >= 0; i--)
        {
            iDigit = (int)Char.GetNumericValue(str[i]);
            if (bIsOdd == true)
                iDigit *= 2;
 
            iSum += iDigit / 10;
            iSum += iDigit % 10;
 
            bIsOdd = !bIsOdd;
        }
 
        return (iSum % 10 == 0);
 
    }
 
}

The code contains three functions.

Analyzing the code and comparing it to the more basic trigger we did earlier, it creates a connection to SQL Server using a context connection. The context connection lets us use the same connection as the session that fired the trigger.

SqlConnection sqlCnn = new SqlConnection("context connection=true")  

After opening the context connection, we read all the rows in the INSERTED pseudo table to fill a DataSet. Then we pass this DataSet to the ValidateRows function which tests each row with the CheckLuhn function. All rows must comply with the Luhn algorithm, so if we find a row that doesn't, we can stop checking and return false.

If the validation was unsuccessful then we throw an exception to abort execution and give an error message to the client connection. You can choose other approaches for this like for example sending a message, like the one we sent in the previous trigger.

When all the rows are checked as valid, we can insert them into the PhoneBlacklist table. You will see that in the code I also included the switch case for the trigger action even when this is a trigger that only handles insert events. This is to make it easier to understand for the reader, you can simplify the code if you want.

Now its time to deploy and test this CLR trigger. Of course, you have to build the project again and then clean the environment as follows.

DROP TRIGGER IF EXISTS dbo.TR_DummyTable 
 
DROP ASSEMBLY IF EXISTS [CLR_Triggers]

Then we recreate the assembly and both triggers.

CREATE ASSEMBLY [CLR_Triggers] FROM
'C:\Users\Daniel\source\repos\CLR_Triggers\bin\Debug\CLR_Triggers.dll'
WITH PERMISSION_SET=SAFE
GO
 
CREATE TRIGGER TR_DummyTable    
ON dbo.DummyTable    
FOR INSERT, UPDATE, DELETE    
AS EXTERNAL NAME [CLR_Triggers].[Triggers].TrivialTrigger;
GO
 
CREATE TRIGGER TR_I_PhoneBlacklist
ON dbo.PhoneBlacklist
INSTEAD OF INSERT 
AS EXTERNAL NAME [CLR_Triggers].[Triggers].IMEITrigger;
GO

In the next image you can see the execution of the previous code.

Let's insert one row in the PhoneBlackList table and see what happens.

SELECT * FROM PhoneBlacklist
GO
 
INSERT [dbo].[PhoneBlacklist] ([IMEI], [Created], [Status], [Model], [Country])
VALUES (N'358981879372208', CAST(N'2021-02-12' AS Date), N'Lost', N'APPLE IPHONE 11', N'Thailand')
GO
 
SELECT * FROM PhoneBlacklist
GO

As you can see in the next screen capture the row has been inserted successfully.

Now let's try to insert a bogus IMEI number.

SELECT * FROM PhoneBlacklist
GO
 
INSERT [dbo].[PhoneBlacklist] ([IMEI], [Created], [Status], [Model], [Country])
VALUES (N'358981879372999', CAST(N'2021-02-12' AS Date), N'Lost', N'APPLE IPHONE 11', N'Thailand')
GO
 
SELECT * FROM PhoneBlacklist
GO

As you can see in the next screen capture, the row was not inserted and we received an error message from the trigger.

Also, I want to test how this trigger behaves when we insert a set of rows instead of a single row. For this purpose, I will create an auxiliary table in which I will include valid IMEI numbers plus the bogus one of the previous test.

CREATE TABLE [dbo].[PhoneBlacklist_MASTER](   [IMEI] [VARCHAR](16) NOT NULL,
   [Created] [DATE] NULL,
   [Status] [VARCHAR](10) NULL,
   [Model] [VARCHAR](50) NULL,
   [Country] [VARCHAR](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[PhoneBlacklist_MASTER] ([IMEI], [Created], [Status], [Model], [Country])
VALUES (N'351360760380520', CAST(N'2021-02-12' AS Date), N'Stolen', N'SAMSUNG SM-A325F', N'South Africa')
GO
 
INSERT [dbo].[PhoneBlacklist_MASTER] ([IMEI], [Created], [Status], [Model], [Country])
VALUES (N'351360760420631', CAST(N'2021-02-12' AS Date), N'Stolen', N'SAMSUNG SM-A325F', N'South Africa')
GO
 
INSERT [dbo].[PhoneBlacklist_MASTER] ([IMEI], [Created], [Status], [Model], [Country])
VALUES (N'351811073190212', CAST(N'2021-11-29' AS Date), N'Stolen', N'Samsung J5', N'Iran')
GO
 
INSERT [dbo].[PhoneBlacklist_MASTER] ([IMEI], [Created], [Status], [Model], [Country])
VALUES (N'353325104466473', CAST(N'2021-02-12' AS Date), N'Stolen', N'SAMSUNG GALAXY S9', N'Hungary')
GO
 
INSERT [dbo].[PhoneBlacklist_MASTER] ([IMEI], [Created], [Status], [Model], [Country])
VALUES (N'355940591796774', CAST(N'2021-02-12' AS Date), N'Stolen', N'SAMSUNG SM-A125F', N'South Africa')
GO
 
INSERT [dbo].[PhoneBlacklist_MASTER] ([IMEI], [Created], [Status], [Model], [Country])
VALUES (N'356768085234732', CAST(N'2021-11-29' AS Date), N'Stolen', N'APPLE IPHONE 8 PLUS', N'United States')
GO
 
INSERT [dbo].[PhoneBlacklist_MASTER] ([IMEI], [Created], [Status], [Model], [Country])
VALUES (N'865312044615287', CAST(N'2021-02-12' AS Date), N'Stolen', N'XIAOMI REDMI NOTE 9 PRO', N'Hungary')
GO

-- bogus IMEI
INSERT [dbo].[PhoneBlacklist_MASTER] ([IMEI], [Created], [Status], [Model], [Country])
VALUES (N'358981879372999', CAST(N'2021-02-12' AS Date), N'Lost', N'APPLE IPHONE 11', N'Thailand')
GO

Now let's try to insert the data of PhoneBlackList_Master with the bogus row into the PhoneBlacklist table and see how the trigger behaves.

SELECT * FROM PhoneBlacklistGO
 
INSERT [dbo].[PhoneBlacklist] ([IMEI], [Created], [Status], [Model], [Country])
SELECT IMEI,
       Created,
       Status,
       Model,
       Country FROM dbo.PhoneBlacklist_MASTER
GO
 
SELECT * FROM PhoneBlacklist
GO

As you can see in the next screen capture, the insert statement failed as expected.

Let's remove the row with the invalid IMEI.

DELETE FROM dbo.PhoneBlacklist_MASTER WHERE IMEI = '358981879372999'

Now we can insert the content of the PhoneBlacklist_MASTER table into the table with the trigger.

SELECT * FROM PhoneBlacklist
 
INSERT [dbo].[PhoneBlacklist] ([IMEI], [Created], [Status], [Model], [Country])
SELECT IMEI,
       Created,
       Status,
       Model,
       Country FROM dbo.PhoneBlacklist_MASTER
GO
 
SELECT * FROM PhoneBlacklist
GO

As you can see in the next image, the data was inserted successfully as expected.

Next Steps

• This tip assumes that you have certain knowledge about Triggers. If you need to a refresher about some concepts take a look at this tutorial: Triggers in SQL Server Tutorial.
• In order to test this code, you have to enable SQL Server CLR integration. In this tip you will find out how to do it: How to Enable SQL Server CLR Integration using T-SQL.
• Here is a SQL Server CLR Introduction article.
• Another interesting use of CLR in SQL Server is the creation of User Defined Types. If you want to know more about this you can read my previous tip SQL Server CLR User Defined Types Example.

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips