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

What are CLR Triggers?

  • CLR triggers are trigger based on CLR.
  • CLR integration is new in SQL Server 2008. It allows for the database objects (such as a trigger) to be coded in .NET.
  • Objects that have heavy computation or that require a reference to an object outside SQL are coded in the CLR.
  • We can code both DDL and DML triggers by using a supported CLR language like C#.

Steps to Create a CLR trigger

Step 1: Create the CLR class. We code the CLR class module with a reference to the namespace required to compile CLR database objects.
Add the following references:
  1. using Microsoft.SqlServer.Server;
  2. using System.Data.SqlTypes;
So the following is the complete code for the class:
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Data.Sql;
  6. using System.Data.SqlClient;
  7. using Microsoft.SqlServer.Server;
  8. using System.Data.SqlTypes;
  9. using System.Text.RegularExpressions;
  10. namespace CLRTrigger
  11. {
  12. public class CLRTrigger
  13. {
  14. public static void showinserted()
  15. {
  16. SqlTriggerContext triggContext = SqlContext.TriggerContext;
  17. SqlConnection conn = new SqlConnection(" context connection =true ");
  18. conn.Open();
  19. SqlCommand sqlComm = conn.CreateCommand();
  20. SqlPipe sqlP = SqlContext.Pipe;
  21. SqlDataReader dr;
  22. sqlComm.CommandText = "SELECT pub_id, pub_name from inserted";
  23. dr = sqlComm.ExecuteReader();
  24. while (dr.Read())
  25. sqlP.Send((string)dr[0] + "," + (string)dr[1]);
  26. }
  28. }
  29. }
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.
  1. CREATE ASSEMBLY triggertest
  2. FROM 'C:\CLRTrigger\CLRTrigger.dll'
  3. WITH PERMISSION_SET = SAFE
Create CLR trigger in SQL Server
Step 4: The final step is to create the trigger that references the assembly. Now we will write the following T-SQL commands to add a trigger on the publishers table in the Pubs database.
  1. CREATE TRIGGER tri_Publishes_clr
  2. ON publishers
  3. FOR INSERT
  4. AS
  5. EXTERNAL NAME triggertest.CLRTrigger.showinserted
If you get a compatibility-related error message, run the following command to set compatibility.
  1. ALTER DATABASE pubs
  2. SET COMPATIBILITY_LEVEL = 100
Step 5: Enable CLR Stored procedure on SQL Server. For this run the following code:
  1. EXEC sp_configure 'show advanced options' , '1';
  2. reconfigure;
  3. EXEC sp_configure 'clr enabled' , '1' ;
  4. reconfigure;
  6. EXEC sp_configure 'show advanced options' , '0';
  7. reconfigure;
Step 6: Now we will run INSERT statement to the publishers table that fires the newly created CLR trigger.
  1. 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 is based on the insertion above.
-----------------------------------------------------
9922,Vishal Nayan
(1 row(s) affected)
The line of code which is printing the query result is actually the following code written in a managed environment.
  1. while (dr.Read())
  2. 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 can be done much more efficiently with CLR programming languages than they can in T-SQL.