Use of DDL Trigger in SQL Server

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:

CREATE TABLE Log

(

   LogID int IDENTITY(1,1) NOT NULL,

   LogEntry varchar(max) NOT NULL,

   CONSTRAINT PK_Log PRIMARY KEY CLUSTERED

       ( LogID ASC )

)

Follow the below 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.

The C# code in LogTableActivityTrigger.cs in the project ClrDdlTrigger

* File: LogTableActivityTrigger.cs

Code View

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 the SqlTriggerContext and then logs the EventData for the event that caused this trigger to fire. In this example, it is not necessary to check the trigger context, as all events for which the trigger is registered execute the same code to log the event. You could use the TriggerAction property to perform different actions for each of the different events that a DDL trigger is registered to handle.

Build the solution.

Register the assembly and create the aggregate function by executing the following T-SQL statement in SQL Server Management Studio, replacing appropriately:

CREATE ASSEMBLY ClrDdlTrigger

FROM '\ClrDdlTrigger\bin\Debug\ClrDdlTrigger.dll'

GO

CREATE TRIGGER LogTableActivityTrigger

ON DATABASE

FOR CREATE_TABLE, DROP_TABLE

AS

EXTERNAL NAME ClrDdlTrigger.Triggers.LogTableActivityTrigger

Execute the following T-SQL statement to create and then drop a table named TestTable to demonstrate the DDL trigger:

CREATE TABLE TestTable

(

    TestID int NOT NULL,

    CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED

    ( TestID ASC )

)

GO

DROP TABLE TestTable

GO

* The Log table that now contains two rows detailing the DDL CREATE_TABLE and DROP_TABLE events.

* Log table LogID LogEntry 

Code View

   1<EVENT_INSTANCE>   <EventType>CREATE_TABLE</EventType>    <PostTime>2007-11-03T17:25:45.407</PostTime>    <SPID>52</SPID>    <ServerName>CTSUSNJY9779A</ServerName>    <LoginName>CTSUSNJY9779A\bill</LoginName>    <UserName>dbo</UserName>    <DatabaseName>AdoDotNet35Cookbook</DatabaseName>
|    <SchemaName>dbo</SchemaName>
   <ObjectName>TestTable</ObjectName>    <ObjectType>TABLE</ObjectType>    <TSQLCommand>        <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"        ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"        ENCRYPTED="FALSE" />       <CommandText>           CREATE TABLE TestTable
           (
             TestID int NOT NULL,
             CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED
             ( TestID ASC )
            )             </CommandText>      </TSQLCommand>
</EVENT_INSTANCE> 

2 <EVENT_INSTANCE>    <EventType>DROP_TABLE</EventType>    <PostTime>2007-11-03T17:25:45.827</PostTime>    <SPID>52</SPID>    <ServerName>CTSUSNJY9779A</ServerName>    <LoginName>CTSUSNJY9779A\bill</LoginName>    <UserName>dbo</UserName>    <DatabaseName>AdoDotNet35Cookbook</DatabaseName>    <SchemaName>dbo</SchemaName>    <ObjectName>TestTable</ObjectName>    <ObjectType>TABLE</ObjectType>    <TSQLCommand>        <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"        ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"        ENCRYPTED="FALSE" />        <CommandText>DROP TABLE TestTable</CommandText>        </TSQLCommand>
</EVENT_INSTANCE>