ADO.NET 3.5 Cookbook, 2nd Edition

12.7. Creating a DDL Trigger

Problem

You need to create a CLR DDL trigger.

Solution

The solution creates a DDL trigger that executes when CREATE TABLE and DROP TABLE DDL statements are executed and logs the events to a table named Log.

The solution uses a single table named Log. Execute the following T-SQL statement to create the table:

	USE AdoDotNet35Cookbook
	GO

	CREATE TABLE Log
	(
	    LogID int IDENTITY(1,1) NOT NULL,
	    LogEntry varchar(max) NOT NULL,
	    CONSTRAINT PK_Log PRIMARY KEY CLUSTERED
	        ( LogID ASC )
	)

Follow these steps:

1. Create a new SQL Server project in Visual Studio and name it ClrDdlTrigger.

2. Create a trigger item in the project. Name the item LogTableActivityTrigger.cs.

3. The C# code in LogTableActivityTrigger.cs in the project ClrDdlTrigger is shown in Example 12-10.

   Example 12-10. File: LogTableActivityTrigger.cs

   using System;
   using System.Data;
   using System.Data.SqlClient;
   using Microsoft.SqlServer.Server;
   
   public partial class Triggers
   {
       public static void LogTableActivityTrigger()
       {
           SqlTriggerContext tc = SqlContext.TriggerContext;
           using (SqlConnection conn = new SqlConnection("context connection=true"))
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand();
               cmd.Connection = conn;
   
               if (tc.TriggerAction == TriggerAction.CreateTable ||
                   tc.TriggerAction == TriggerAction.DropTable)
               {
                   cmd.CommandText = "INSERT INTO Log VALUES " +
                       "('" + tc.EventData.Value + "')";
                   cmd.ExecuteNonQuery();
               }
           }
       }
   }

   A single DDL trigger is defined in the Triggers class. The trigger checks the TriggerAction property of ...