SQL Server 2005 supports data definition language
(DDL) triggers, allowing you to trap DDL operations and react to them.
You can thus roll back the DDL activity. DDL triggers work
synchronously, immediately after the triggering event, similar to the
way that DML triggers in previous versions of SQL Server work. SQL
Server 2005 also has an asynchronous event consumption mechanism that
uses notifications and allows you to be notified when certain events,
such as DDL events, occur. DDL triggers can be database-wide and can react to certain types of DDLs or all DDLs.
The cool thing about DDL triggers is that you can get context information from querying the EVENTDATA()
method. Event data is an XML payload of data about what was happening
when your DDL trigger ran, including information about the time,
connection, and user; the type of event that was fired; and other useful
data. To get at EVENTDATA() data, you have to use the EVENTDATA() function in your trigger code. If you issue a ROLLBACK statement in the trigger, the EVENTDATA() function will no longer return information. In this situation, you must store the information in a variable before issuing the ROLLBACK statement to be accessed later.
The following AdventureWorks trigger, created at the database level, will capture DROP TABLE
attempts. First we’ll create a log table to log all our event data
using an XML column, and then we’ll create a dummy table that we will
delete and test our trigger on. Our trigger will then write the event
data to this table.
Listing 1. Catching DROP TABLE attempts with a trigger
USE AdventureWorks
GO
--create a log table
CREATE TABLE tblDDLTriggerLog (LogInfo xml)
--create a dummy table to delete later on
CREATE TABLE toDelete (ID int primary key)
--add some dummy data in just for fun
INSERT INTO toDelete VALUES(1)
--create a trigger that will disallow any table
--drops and log the event data into our log table
CREATE TRIGGER StopDropAnyTable ON DATABASE AFTER DROP_TABLE
AS
DECLARE @eventData AS xml
SET @eventData = EVENTDATA()
ROLLBACK
-- For results window!
PRINT 'DROP TABLE attempt in database ' + DB_NAME() + '.'
INSERT INTO tblDDLTriggerLog VALUES(@eventData)
|
The following example will also attempt to drop a table and will query the tblDDLTriggerLog table:
--now see this trigger in action
DROP TABLE toDelete
SELECT * FROM tblDDLTriggerLog
The results look like this:
DROP TABLE attempt in database AdventureWorks.
[1 row affected]
Msg 3609, Level 16, State 2, Line 1
Transaction ended in trigger. Batch has been aborted.
The EventData() XML stored in tblDDLTriggerLog looks like this:
<EVENT_INSTANCE>
<EventType>DROP_TABLE</EventType>
<PostTime>2004-12-07T20:40:58.597</PostTime>
<SPID>53</SPID>
<ServerName>KILIMANJARO</ServerName>
<LoginName>KILIMANJARO\Stephen Forte</LoginName>
<UserName>KILIMANJARO\Stephen Forte</UserName>
<DatabaseName>AdventureWorks</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>toDelete</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_
IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>DROP TABLE toDelete</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
