Creating CLR Trigger

Question

Hi can anyone tell me? what are the major steps we follow for creating CLR Trigger? Thanks in advance

Manish Dwivedi · Answer

Hi suthish, I am not using this concept in my projects.

Suthish Nair · Answer

Manish, Are you using this concept in any of your projects? Why we need this kind of logic's in projects. This is only for SQL Server 2008 or other DB's support it?

Manish Dwivedi · Answer

Hi Shalini, This is all about CLR Trigger. Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable{mso-style-name:"Table Normal";mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-priority:99;mso-style-parent:"";mso-padding-alt:0in 5.4pt 0in 5.4pt;mso-para-margin-top:0in;mso-para-margin-right:0in;mso-para-margin-bottom:10.0pt;mso-para-margin-left:0in;line-height:115%;mso-pagination:widow-orphan;font-size:11.0pt;font-family:"Calibri","sans-serif";mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:"Times New Roman";mso-bidi-theme-font:minor-bidi;} 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 CREATEASSEMBLY 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 compatibility 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 ('9122','Manish') The trigger simply echoes the contents of the inserted table. The output from the trigger based on the insertion above. --------------------------------------- 9122, Manish (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.