Programming Microsoft SQL Server 2005: DDL Triggers and Notifications

1/22/2011 4:22:15 PM
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
--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
--create a trigger that will disallow any table
--drops and log the event data into our log table
DECLARE @eventData AS xml
SET @eventData = EVENTDATA()
-- 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
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:

<LoginName>KILIMANJARO\Stephen Forte</LoginName>
<UserName>KILIMANJARO\Stephen Forte</UserName>
<CommandText>DROP TABLE toDelete</CommandText>
Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone