//
you're reading...
Trigger

Create your first CLR Trigger in 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 “);

conn.Open();

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’

WITH PERMISSION_SET = SAFE

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

FOR INSERT

AS

EXTERNAL NAME triggertest.CLRTrigger.showinserted

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

ALTER DATABASE pubs

SET COMPATIBILITY_LEVEL =  100

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

EXEC sp_configure ‘show advanced options’ , ‘1’;

reconfigure;

EXEC sp_configure ‘clr enabled’ , ‘1’ ;

reconfigure;

EXEC sp_configure ‘show advanced options’ , ‘0’;

reconfigure;

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.

Advertisements

About Vishal

Vishal Nayan is a seasoned professional with hand on Experience on Mircrosoft Technologies. He always look for challenging IT position that allows him to learn new Microsoft Technologies while utilizing experience of Project Development and Software Engineering Ethics. A MCP in WCF ,and looking forward for more.

Discussion

14 thoughts on “Create your first CLR Trigger in SQL Server 2008 using C#

  1. Hey i am getting the following errror while creating the context connection

    System.TypeInitializationException: The type initializer for ‘System.Data.SqlClient.SqlConnection’ threw an exception. —> System.TypeInitializationException: The type initializer for ‘System.Data.SqlClient.SqlConnectionFactory’ threw an exception. —> System.TypeInitializationException: The type initializer for ‘System.Data.SqlClient.SqlPerformanceCounters’ threw an exception. —> System.IO.FileLoadException: The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
    System.IO.FileLoadException:
    at System.Diagnostics.Switch.InitializeWithStatus()
    at System.Diagnostics.Switch.get_SwitchSetting()
    at System.Diagnostics.TraceSwitch.get_Level()
    at System.Data.ProviderBase.DbConnectionPoolCounters..ctor(String categoryName, String categoryHelp)
    at System.Data.SqlClient.SqlPerformanceCounters..ctor()
    at System.Data.SqlClient.SqlPerformanceCounters..cctor()
    System.TypeInitializationException:
    at System.Data.SqlClient.SqlConnectionFactory..ctor()
    at System.Data.SqlClient.SqlConnectionFactory..cctor()
    System.TypeInitializationException:
    at System.Data.SqlClient.SqlConnection..cctor()
    System.TypeInitializationException:
    at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
    at StoredProcedures.MyService()

    Any suggestions??

    Posted by Kalashnikov | 2011/05/17, 12:16 PM
  2. Hello – I have an ASP.Net (4) application interacting with SQL server. I want some .net globals to change when a table is updated. Rather than poll the table I was wondering if a trigger can be used in such a situation. Is there a way for ASP.net to subscribe to DB events?

    Posted by Sonia Subramanian | 2011/10/14, 4:38 AM
  3. Thanks for the article about example of trigger in C# , your code explain running c# from console mode ,

    Posted by baju bali | 2011/12/01, 8:47 PM
  4. This is best one article so far I have read online. I would like to appreciate you for making it very simple and easy. I have found another nice post related to this post over the internet which also explained very well. For more details you may check it through this url…

    http://mindstick.com/Articles/f7074849-0e9e-463b-a12d-f3d7a35b2785/?Trigger%20in%20SQL%20Server

    Thanks

    Posted by Rajesh Singh | 2011/12/26, 4:12 PM
  5. It helps me a lot Thanking u.

    Posted by Rahul | 2012/01/07, 3:07 PM
  6. Thank you for article! But I did how you wrote in the article but I got error when I tried create TRIGGER tri_Publishes_clr. The error was “Could not find Type ‘CLRTrigger’ in assembly ‘CLRTrigger'”. I resolved this problem by deleting “namespace CLRTrigger” from trigger’s code. May be it is will help somebody.

    Posted by kavayii | 2012/04/11, 1:09 AM
  7. Hi there, this weekend is good in support of me, for the reason that this point in time i
    am reading this impressive informative post here at my residence.

    Posted by http://thedreamersbloggery.com | 2013/08/27, 2:08 AM
  8. Outstanding post, you have pointed out some fantastic points, I as well think this is a very great website. geeceddagkdf

    Posted by Johng743 | 2014/05/18, 7:45 AM
  9. Actually no matter if someone doesn’t know then its up to other visitors that they will help,
    so here it takes place.

    Posted by http://joycheats.com/ | 2014/07/22, 6:21 AM
  10. Thanks for the good writeup. It in truth was once a enjoyment
    account it. Look advanced to more introduced agreeable from you!
    However, how can we communicate?

    Posted by Paula | 2014/09/21, 1:22 AM
  11. I love reading through a post that can make men and women think.
    Also, many thanks for allowing for me to comment!

    Posted by brustvergrösserung | 2014/09/26, 6:14 PM

Trackbacks/Pingbacks

  1. Pingback: New Things are New | codeiwillnotforget - 2012/05/17

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow Vishalnayan on WordPress.com

Blog Stats

  • 215,404 hits
%d bloggers like this: