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 trigger) to be coded in .NET.
Object that have heavy computation or require reference to object outside SQL are coded in the CLR.
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
Read more: C# Corner