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 -
-
Create a new SQL Server project in Visual Studio and name it ClrDdlTrigger.
-
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 <path> appropriately:
CREATE ASSEMBLY ClrDdlTrigger
FROM '<path>\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
Join the conversation! Your thoughts help the community grow.