CLR Triggers for SQL Server 2005
page 7 of 9
by Soyuj Kumar Sahoo
Average Rating: 
Views (Total / Last 10 Days): 64766/ 139

Steps for Creating CLR Trigger

The following are required steps for creating a CLR-Trigger of DML (After trigger) type for Insert action. This database Object is executed as the result of a user action against a table i.e. for an INSERT statement.

·         Creating a .NET class of triggering action

·         Making assembly(.DLL) from that Class

·         Enabling CLR environment in that database.

·         Registering the assembly in SQL Server

·         Creating CLR Trigger using that assembly

1. Creating a .NET class

Here we can use any managed language that is supported by .Net Framework such as C++, C#, VB, J#, JScript or XAML, etc. As I am with VB, this managed code is in Visual Basic. Let us discuss the objective of this entity. According to the above example of "tr_User_INSERT" trigger, we have the UserTable for holding the user details. There is a field "Type" which explains the user role (ADMIN, End User, Register User etc.).  Our objective is to check the role of the inserted User for ADMIN type and then do the action as we set.

Open the notepad, copy the following codes and save it as MyFirstAssembly.vb.

Listing 2 (.NET Class of Trigger type)

Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Partial Public Class MyFirstClrTrigger
 = "checkUserRole", Target
 = "UserTable"Event
 = "FOR INSERT")> _
   Public Shared Sub checkUserRole()
Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext()
Dim userType As String = String.Empty
If triggContext.TriggerAction = TriggerAction.Insert Then
  Using connection As New SqlConnection("context connection=true")
  Dim sqlComm As New SqlCommand
  Dim sqlPipe As SqlPipe = SqlContext.Pipe()
  sqlComm.Connection = connection
  sqlComm.CommandText = "SELECT Type from INSERTED"
  userType = sqlComm.ExecuteScalar.ToString()
  If userType.ToUpper = "ADMIN" Then
    sqlPipe.Send("Hello !!! You have the Admin role.")
    sqlPipe.Send("We can use e-mail codes here to inform.")
  End If
  End Using
End If
End Sub
End Class   

Let us go into the codes. There are two major Namespaces used, System.Data.SqlClient and Microsoft.SqlServer.Server. Microsoft.SqlServer.Server provides the SqlTriggerAttribute Class, which is used to mark a method definition in an assembly as a trigger in SQL Server. The Sqltrigger attribute requires some parameters to set the created trigger properties, such as Name - the name of the Trigger, Target - the table or view to which the trigger applies (in case of DML type) and Event - the event to fire the trigger for (e.g. FOR INSERT, DELETE and/or UPDATE or INSTEAD OF etc.). Again, that method must be a Static (Shared in VB) one; here checkUserRole() is the target method. 

The SqlTriggerContext class provides the required triggering properties of the Trigger for doing action. TriggerAction property of this Class is the global enumeration TriggerAction type of Microsoft.SqlServer.Server namespace which indicates what action fired the Trigger.

Here we use the INSERTED table, which is automatically created and managed by SQL Server 2005. This is used to set the conditions of DML trigger action. There is also another called DELETED, used in case of delete action. The CLR triggers can access the Inserted or Deleted tables through SqlCommand object using context connection.

2. Making assembly (.DLL)

Now we have created the MyFirstAssembly.vb file with MyFirstClrTrigger class, so we need to compile the class to create an assembly. Here I use the VB compiler "vbc.exe," found in .Net   Framework library. Use the following DOS command for creating assembly.

Listing 3 (compile the .VB file)

 /t:library /out:F:\CLR_Trigger_Test\CLR_Assembly\MyFirstAssembly

This command creates a DLL called MyFirstAssembly.dll at out path F:\CLR_Trigger_Test\CLR_Assembly\.

3. Enabling CLR environment

By default, the CLR functionality is trued off in SQL Server; so we need to enable it. Use the following T-SQL codes to enable the CLR functionality.

Listing 4 (enable CLR action)

------ Enabling CLR action in database -------
sp_configure 'clr enabled', 1
RECONFIGURE with Override

4. Registering the assembly

Our next step is to create an assembly in the Database, based on the compiled DLL (MyFirstAssembly.dll). The following T-SQL codes are useful regarding this objective.

Listing 5 (registering assembly)

------ Registering an Assembly -------
Create Assembly UserAssembly
From 'F:\CLR_Trigger_Test\CLR_Assembly\MyFirstAssembly.dll'
With Permission_Set=Safe

For more information about this check my previous article.

5. Creating CLR Trigger

Now we will create an extended Trigger using CREATE Trigger statement of T-SQL. There is a new clause named EXTERNAL NAME in SQL Server 2005, which allows us to reference a method of the Registered assembly. By doing so we set the triggering action of our Trigger using that managed code method of the assembly.

Listing 6 (creating a Trigger)

------ Creating a DML trigger in CLR -------
Create Trigger tr_User_CheckRole
on UserTable
External Name UserAssembly.MyFirstClrTrigger.checkUserRole

Here we use the checkUserRole() shared method of our MyFirstClrTrigger class of the registered assembly UserAssembly.

View Entire Article

User Comments

Title: TriGGER   
Name: xenolith
Date: 2012-11-02 5:00:45 AM
I need c# sample for CLR triggers
Title: TriGGER   
Name: Ankit
Date: 2012-11-02 5:00:18 AM
I need c# sample for CLR triggers
Title: TriGGER   
Name: Jeba
Date: 2012-08-17 12:33:28 AM
Thnaks for giving such a wonderful article..
Title: CLR TriGGER   
Name: Sunil
Date: 2011-10-17 7:11:40 AM
DDL triggers are new to SQL Server 2005. This type of triggers, like regular triggers, fire stored procedures in response to an event. They fire in response to a variety of Data Definition Language (DDL) events. These events are specified by the T-SQL statements that are start with the keywords CREATE, ALTER, and DROP. Certain stored procedures that perform DDL-like operations can also fire this. These are used for administrative tasks like auditing and regulating database operations.
Title: CLR TriGGER   
Name: Somanathan B.Tech
Date: 2011-04-20 3:36:04 AM
I need c# sample for CLR triggers
Title: Trigger   
Name: Sathish(Spiro)
Date: 2011-04-20 3:22:33 AM
I need some more article for trigger....
Title: Basic Trigger   
Name: Somanathan B.Tech
Date: 2011-04-20 3:08:47 AM
This website really useful who going to learn sql trigger...
Title: TriGGer   
Name: Ashish Kumar Gupta
Date: 2011-04-06 5:37:22 AM
Thanks for giving such a good information about Trigger...
Realy Thankx the Entire Team......!!
Title: About Trigger.   
Name: Bhausaheb Funde
Date: 2011-04-04 6:37:06 AM
Thanks Very Nice Article..
Title: Triggers   
Name: Arjun
Date: 2011-03-25 10:58:45 PM
Thnaks for giving such a wonderful article..
nice presentation....
Title: Triggers   
Name: Deepthi Somani
Date: 2011-02-14 6:44:14 AM
Hi Looking for good sample of triggers....Thanks
Title: About Trigger   
Name: Sunil Kumar(Open Futures)
Date: 2010-09-20 9:00:40 AM
Thanks for giving such a good information about Trigger...
Realy Thankx the Entire Team......
Title: CLR Triggers   
Name: kalyan
Date: 2010-09-01 4:54:00 AM
A CLR triggers can be any of the above, e.g. can be a DDL or DML one or can also be an AFTER or INSTEAD OF trigger. Here we need to execute one or more methods written in managed codes that are members of an assembly created in the .Net framework. Again, that assembly must be deployed in SQL Server 2005 using CREATE assembly statement.
Title: Cute and described   
Name: kalyan
Date: 2010-08-31 7:47:29 AM
comes only when we call!!!!Great!!!
Title: Fine   
Name: Shaan
Date: 2010-06-11 5:23:12 AM
Thanks For The Information
Title: Marvelous   
Name: Rajesh Kunwar
Date: 2010-04-12 3:07:34 PM
Title: problam solved   
Name: chittaranjan dash
Date: 2010-03-31 12:57:10 AM
I want to make changes in another database objact whenever any new database objact is created.Which of following triggers i can be used
Title: marvelous   
Name: jyoti singh
Date: 2010-03-29 12:43:02 PM
Marvelous representation of trigger.thanks a lot.
Title: Query Regrading DML trigger   
Name: swadesh
Date: 2010-01-21 8:19:18 AM
Hi there,

I 've one query regarding trigger.
Syntex for AFTER trigger is FOR???
PLZ help me out
Title: thanks   
Name: vinay singh
Date: 2010-01-15 7:48:10 AM
Very Nice Article..
Title: thanks   
Name: sanjeev
Date: 2009-12-03 2:36:48 PM
thanks but still i have a problem. plz define step by step how to create CLR Trigger

Title: trigger   
Name: manivel
Date: 2009-11-16 6:43:01 AM
trigger article is very useful for use. It has berifly explain about types of trigger. But it doesn't have any syntax and clear example.that is a disadvantage of this.
Title: CLR Triggers for SQL Server 2005   
Name: Deepak from pune
Date: 2009-10-20 2:14:22 AM
Thats g8 ......!!!
very good article with nice explation language, Thnx dude.
Title: how to use tiggers   
Name: Gurushantha.e
Date: 2009-10-13 3:34:14 AM
ow to use tiggers
Title: how to run the trigger?(or) how to see the trigger?   
Name: p.santhi
Date: 2009-10-05 12:57:12 AM
please help me.u r good guide in oracle. thank you.
Title: great help   
Name: great help
Date: 2009-10-02 10:57:04 AM
great help
Title: DML After trigger   
Name: Rohit Kumawat from Mumbai
Date: 2009-09-05 12:43:37 AM
Many Many Thanks for this article. Its greate for basic fundas of trigger. Thanks again.
Title: nice   
Name: jp
Date: 2009-09-01 7:35:52 AM
Title: DML Triggers   
Name: Hita
Date: 2009-06-02 2:19:30 AM
Good explanation
Title: CLR Triggers for SQL Server 2005   
Name: Prasad
Date: 2009-05-30 3:12:30 AM
Good help.. Gr8 job bro!
Title: Type Of Trigger   
Name: Mandeep Singh Delhi
Date: 2009-04-27 5:15:46 AM
very very useful for me thanks a lot..Give More Example
Title: Type Of Trigger   
Name: kapil dhariwal 09953012260
Date: 2009-04-27 5:14:04 AM
Really very Nice solution.will u please Explain in C#?
Title: Not bad at all   
Name: Ned
Date: 2009-04-16 11:27:35 PM
I guess write has made a good effort. But there is a room to make it better by giving more example. The best example could be generic Audit trigger using the CLR power.
Title: Types of triggers   
Name: Vikas Singh
Date: 2009-02-27 2:11:54 AM
The article lack description. It is just giving the definitions. More information will be appreciated.
Title: TRIGGERS   
Date: 2009-01-19 12:47:54 AM
Title: Good.....Very God   
Name: Vaibhava Srivastava
Date: 2008-12-30 12:16:15 PM
Article is nice but Can you explain Instead off Trigger with example....I think more examples are required to explain
Title: simple to understand   
Name: sushil
Date: 2008-12-12 9:36:44 AM
give some example
Title: Its very nice   
Name: Sujina
Date: 2008-12-09 5:21:45 AM
This article is very nice,Can u explain it with more example.
Title: Thanks for your nice article   
Name: Rajklumar
Date: 2008-12-03 7:27:03 AM
very very useful for me thanks a lot
Title: trigger   
Name: saravanan.m
Date: 2008-12-01 4:15:39 AM
this article very nice
Title: TRIGGERS   
Date: 2008-11-14 5:18:21 AM
nice article
Title: trigger   
Name: suvendu
Date: 2008-11-06 10:04:09 AM
Title: trigger   
Name: sankata
Date: 2008-11-06 9:51:26 AM
very good
Title: trigger   
Name: Nimit
Date: 2008-11-03 4:26:33 AM
Title: trigger   
Name: Harsh
Date: 2008-11-03 4:25:51 AM
nice but need some more example for ddl,dml trigger
Title: triggers   
Name: kaushik
Date: 2008-10-28 3:33:23 AM
good but can be made better by giving more examples.
Title: triggers   
Name: bangaram
Date: 2008-10-15 6:16:17 AM
good but need more examples
Title: triggers   
Name: solai
Date: 2008-10-09 2:08:31 AM
nice solutions!!!!!!!!!!!
Title: Triggers   
Name: Vivek
Date: 2008-09-24 11:49:36 PM
Nice article!!

Need more info on DDL triggers

Title: cheta   
Name: Pandu
Date: 2008-09-05 1:34:13 AM
Required info is not found
Title: good article   
Name: Digisha Modi
Date: 2008-08-18 12:35:26 AM
This is very good article for a guy who is new for CLR Trigger. From this article they can get the basic idea how to develop a CLRTriggers.

Good & Thanks,
Digisha Modi.
Title: Simply Superb   
Name: Ashis Kumar Das
Date: 2008-08-06 7:25:18 AM
I am very grateful to you for putting such a good information about triggers. I have understood what exactly a trigger means and i will try to implement it on our project.Thanks again...
Title: Simply Superb   
Name: Dsr
Date: 2008-07-22 8:07:46 AM
Hello!! Hi,
This tutorial is very good. Post examples for Insert , Update and Delete.

Thank you very much
Title: Excellent   
Name: A R Soares
Date: 2008-06-27 11:21:02 AM
Congratulations for the clarity with which managed to convey information which I always thought to be complex so that I could understand.
Title: Amazing   
Name: Shahejad Shaikh
Date: 2008-06-24 10:02:26 AM
Its really very helpful.Give very much details about triggers in Sqlserver 2005.
Title: Very Helpful   
Name: Priya
Date: 2008-06-24 2:18:53 AM
Really it helped a lot to know about triggers and CLR Triggers. Thanks a lot dude.
Title: Very Good   
Name: MadhusudhanReddy
Date: 2008-06-17 12:46:55 AM
Hello,very nice. but give to the one r two examples.
Title: Trigger   
Name: Ravi
Date: 2008-06-02 1:38:27 AM
Thank you. Good Article
Title: triggers   
Name: indian
Date: 2008-05-10 12:29:48 AM
Title: insert,update,delete triggers in 2000   
Name: singh
Date: 2008-04-29 4:08:59 AM
will u plz explain all these three triggers in brief.
u have just given an overview here
Title: Need in c#   
Name: Prakash
Date: 2008-04-08 10:49:19 AM
will u please Explain in C#?
Title: pls explain this in C#   
Name: geetha
Date: 2008-03-18 2:49:57 AM
hello sir/madam
tutorial is good and i satisfied but explain this clr and ddl triggers in C#
Title: very nice...   
Name: private
Date: 2008-03-11 12:55:25 AM
Thank u....
Title: good   
Name: prof
Date: 2008-03-02 3:40:37 PM
Good information
Title: excellent!!   
Name: Bindu
Date: 2008-02-10 8:52:07 AM
Thankyou very much
Title: Plz help me with this doubt!!!   
Name: Vishwas Setty
Date: 2007-11-18 3:38:59 PM
Hi there,

can we use this triggers in updating table values...ex: if i update a value in one table, it should reflect the same in another table??? Plz let me know how we can do this???
Title: Great Knowledge   
Name: Vishwas Setty
Date: 2007-11-18 3:30:39 PM
Hi there,

I am very grateful to you for putting such a good information about triggers and i am happy to say that, i have understood what exactly a trigger means and i ll try to implement it on our project.Thanks again...
Title: Nice to learn abt Trigger   
Name: sandeep
Date: 2007-09-29 5:10:10 AM
This is very good article to know that what is the relationship between stored procedure and Trigger.
Title: Good Article   
Name: Kesar
Date: 2007-09-28 12:47:18 PM
Very good article..good effort.
Good luck
Title: Info   
Name: Amit
Date: 2007-09-21 8:09:54 AM
Hi, Can we do this by using tables in different databases.
I mean i want to insert some data in TableA(in DatabaseA), On INSERT in TableB(in DatabaseB).
Please send a mail to me regarding the same on

Thank You
Title: Good Article   
Name: vivek sisodia
Date: 2007-07-23 3:23:43 AM
I think this is good article to know about trigger .
Title: Easy to do   
Name: Zé Luis
Date: 2007-07-19 10:50:14 AM
Title: Clear and precise article   
Name: Kartik Subramaniam
Date: 2007-07-17 1:06:51 AM
Article is really good with decent comparison between the previous versions and the latest one.
Title: Good Article   
Name: DON
Date: 2007-06-15 2:34:52 AM
Good Article
Title: CLR Triggers   
Name: Uday
Date: 2007-05-20 11:33:08 AM
Simple but clearly illustrates all the types of triggers. Good job dude. Thanks a ton.

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2023  |  Page Processed at 2023-09-28 6:03:42 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search