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

Introduction

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

I hope you are already quite familiar with triggers in SQL Server (or in your favorite database). This article will show how to create a simple trigger in a SQL-CLR project. Find more about triggers in SQL Server here: Triggers in SQL Server.

Here we will see how to write a trigger in the C# language and deploy it 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.

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

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

Then right-click on the project, select "Properties," then choose the "Application" tab, then select ".NET Framework 3.5".

This step is essential because we use SQL Server 2008, which does not support .NET Framework 4.0 or upper. And it will ask you to reopen 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 be 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, 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.

Conclusion

In this series article, we learned how to create a simple trigger in a SQL-CLR project. 

Continue reading this series:  SQL CLR For Beginners: Part 3: Create Function in SQL-CLR.