How to Add a CLR Trigger DDL to a Table

When using Microsoft's SQL server 2008, you may find it useful to add CLR triggers to your data table. CLR DDL triggers are used to inform the server administrator when a table performs an operation relating to the Create, Alter and Drop commands. To add a CLR DDL trigger to your table, you simply need to establish the trigger in your table's code.

Instructions

1. Open the code file for your table, and make sure the following statements are present in the code:
   using System;
   using System.Data;
   using System.Data.Sql;
   using Microsoft.SqlServer.Server;
   using System.Data.SqlClient;
   using System.Data.SqlTypes;
   using System.Xml;
   using System.Text.RegularExpressions;

   These statements set up the data types you need to establish a DDL trigger.

2. Establish the DDL trigger class by typing the following statements:

   public class CLRTriggers
   {
   public static void DropTableTrigger()
   {
   SqlTriggerContext triggContext = SqlContext.TriggerContext;

3. Create a switch statement that will tell the DDL trigger to dump event data in response to a Create, Alter or Drop command. The code should appear as follows:

   switch(triggContext.TriggerAction)
   {
   case TriggerAction.DropTable:
   SqlContext.Pipe.Send("Table dropped! Here's the EventData:");
   SqlContext.Pipe.Send(triggContext.EventData.Value);
   break;

   default:
   SqlContext.Pipe.Send("Something happened! Here's the EventData:");
   SqlContext.Pipe.Send(triggContext.EventData.Value);
   break;
   }
   }
   }

4. Select the "Save" button to save the code to your file.