.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Create your first CLR Trigger for SQL Server 2008 using C#

Posted By:Vishal Nayan       Posted Date: September 01, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

Create your first CLR Trigger for SQL Server 2008 using C#

Create your first CLR Trigger for SQL Server 2008 using C#


What are CLR Triggers?


a) CLR triggers are trigger based on CLR.

b) CLR integration is new in SQL Server 2008. It allows for the database objects (such as trigger) to be coded in .NET.

c)  Object that have heavy computation or require reference to object outside SQL are coded in the CLR.

d) We can code both DDL and DML triggers by using a supported CLR language like C#.


Let us follow below simple steps to create a CLR trigger;

Step 1: Create the CLR class. We code the CLR class module with reference to the namespace required to compile CLR database objects.


Add below reference;


using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;


So below is the complete code for class;


using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Sql;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

using System.Text.RegularExpressions;


namespace CLRTrigger


    public class CLRTrigger


        public static void showinserted()


            SqlTriggerContext triggContext = SqlContext.TriggerContext;

            SqlConnection conn = new SqlConnection(" context connection =true ");


            SqlCommand sqlComm = conn.CreateCommand();

            SqlPipe sqlP = SqlContext.Pipe;

            SqlDataReader dr;

            sqlComm.CommandText = "SELECT pub_id, pub_name from inserted";

            dr = sqlComm.ExecuteReader();

            while (dr.Read())

                sqlP.Send((string)dr[0] + "," + (string)dr[1]);






Step 2:  Compile this class and in the BIN folder of project we will get CLRTrigger.dll generated. After compiling for CLRTrigger.dll, we need to load the assembly into SQL Server


Step 3:  Now we will use T-SQL command to execute to create the assembly for CLRTrigger.dll. For that we will use CREATE ASSEMBLY in SQL Server.


CREATE ASSEMBLY   triggertest

FROM 'C:\CLRTrigger\CLRTrigger.dll'



Step 4: The final step is to create the trigger that references the assembly. Now we will write below T-SQL commands to add a trigger on the publishers table in the Pubs database.


CREATE TRIGGER tri_Publishes_clr

ON publishers



      EXTERNAL NAME triggertest.CLRTrigger.showinserted


If you get some compatibilit issue error message run the below command to set compatibility.




Step 5: Enable CLR Stored procedure on SQL Server. For this run the below code;


EXEC sp_configure 'show advanced options' , '1';



EXEC sp_configure 'clr enabled' , '1' ;



EXEC sp_configure 'show advanced options' , '0';




Step 6: Now we will run INSERT statement to the publishers table that fires the newly created CLR trigger.


INSERT publishers

(pub_id, pub_name)

values ('9922','Vishal Nayan')


The trigger simply echoes the contents of the inserted table. The output from the trigger based on the insertion above.



9922,Vishal Nayan


(1 row(s) affected)



The line of code which is printing the query result is actually below code written in managed environment.

            while (dr.Read())

                sqlP.Send((string)dr[0] + "," + (string)dr[1]);


Conclusion: The tri_Publishes_clr trigger demonstrates the basic steps for creating a CLR trigger. The true power of CLR triggers lies in performing more complex calculations, string manipulations

and things of this nature that the can be done much more efficiently with CLR programming languages than they can in T-SQL.


 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend