SQL CLR For Beginners: Part 2: Create Trigger in SQL-CLR Project

Welcome to the SQL-CLR For Beginners article series. This is the second presentation in this series. In the previous article of this series we saw how to create a simple Stored Procedure in a SQL-CLR project. You can read it here:
 
SQL CLR For Beginner: Part 1: Create Store Procedure in SQL-CLR Project
 
In this article we will see how to create a simple trigger in a SQL-CLR project. I hope you are already quite familiar with the concept of triggers in SQL Server (or in your favorite database).
 
Here we will see how to write a trigger in the C# language and deploy in a SQL Server Database.
 
Open a SQL-CLR Database project in Visual Studio. Provide your favorite name and supply location to save the project.


 
Once you click "OK", it will ask you to select a database for deployment. You need to choose a database to deploy this application to. You can click "Add new reference" to select a specific database.


 
After selecting a server and a proper database, you can use the "Test Connection" button to test the connection. If the connection is "OK" then it will show you a success message as in the following.


 
Then right-click on the project and select "Properties" then choose the "Application" tab then select ".NET Framework 3.5".
 
This step is essential because we are using SQL Server 2008 and it does not support .NET Framework 4.0 or upper. And it will ask you to re –open your project to take effect. The following is the screen of the situation.


 
Now right-click on the "Test Script" folder in Solution Explorer then select "Add" - "Trigger".


 
The following code will be generated for you automatically. Let's discuss the following code. You can look at one function in the following called MyTrigger(). This is nothing but our database trigger. And above the trigger you will find a few attributes. Such as trigger name, target table and event. Event means in which event it will bee fired. Here we have set "FOR UPDATE". In other words, when an update operation occurs it will be fired.
 
 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="MyTrigger", Target="test", Event="FOR UPDATE")]
     public static void MyTrigger()
     {
         // Replace with your own code
         SqlContext.Pipe.Send("Update happen in test table");
     }
 }
 
Now we need to go "Build" - "Deploy Solution".


 
If everything is fine then it will show a success message as in the following.


 
Now open SSMS with your credentials.


 
Select a proper database (that you chose when the project was opened) and fire one update query in the associated table. You will see the following message that we had written in the trigger.