Writing Managed Triggers using C#

Introduction

With the integration of the CLR with SQL Server 2005, we can create database objects using modern object-oriented languages like VB.NET and C#.

This article is trying to explain the simple and required steps that are require starting the creation of Manage Triggers using C#.

The Project

We will create a Visual Studio 2005 database project for the managed Trigger.

Creating the Database project:

Open Microsoft Visual Studio 2005 and create a SQL Server Project.

File->New->Project->Database

 

Adding a database reference:

Now it will ask for a database reference.  Add one.

Add a Trigger:

Right click on the Project and add a Trigger.

The file Trigger1.cs:

Past following lines to the file Trigger1.cs. Make sure that the table Person exist on your database or give the name of your table instead of Person.

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server; 

 

public partial class Triggers

{

    // Enter existing table or view for the target and uncomment the attribute line

     [Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger1", Target="Person", Event="FOR UPDATE")]

    public static void Trigger1()

    {

        // Replace with your own code

        SqlContext.Pipe.Send("Trigger FIRED");

    }
}

Deploy the Trigger:

Build the project and then deploy it.

Test the Trigger:

Make sure the clr is enabled with your SQL Server by running the following sql.

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;
GO

Now execute an update command on the table 'Person'. You will get a line 'Trigger FIRED'.

Learned Triggering!!!!