Here is a small Example of How to Write CLR Triggers in SQL 2005 .
First Open a ClassLibrary Project in Visual Studio and go to the Program.cs File and edit as Required
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data;
namespace ClassLibrary1
{
public partial class Triggers
{
//In my CLR Method i have Opened the connection like below
[Microsoft.SqlServer.Server.SqlTrigger(Name = @"FuncName",
Target = "[dbo].[Table_NAME]“, Event = “FOR INSERT”)]
public static void FuncName()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
String userType = “” ;
SqlCommand command1;
SqlCommand command2;
SqlConnection connection
= new SqlConnection(@”context connection=true”);
connection.Open();
command1 = new SqlCommand(@”SELECT * FROM TableName;”,connection);
// Select all from Table
command1.ExecuteScalar();
// Returns First Row , First Column of the
command2 = new SqlCommand(@”insert into [dbo].[docs2](docname) values(1)”, connection);
int rows = command2.ExecuteNonQuery();
}
}
}
Now Build the DLL and Store it in Some Location.
I have stored it under this location -
C:\ClassLibrary1\bin\Debug\ClassLibrary1.dll’
-> After this open the SQL management studio to run the Query , Make sure the Query is run on the Database you want the trigger to be on !
sp_configure ‘clr_enabled’, 1
go
RECONFIGURE with override
go
reconfigure
create assembly ClassLibrary1
from ‘C:\ClassLibrary1\bin\Debug\ClassLibrary1.dll’
with Permission_Set =safe
go
create trigger tr_TriggerName
on AllDocs
for INSERT
as
external name ClassLibrary1.[ClassLibrary1.Triggers].FuncName
go
Now when you Insert the Trigger will be Exectuted .
You can specify Triggers similarly for Update , Delete as well
Tags: CLR Trigger SQL
