SQL Server 2005 has extended the trigger functionality you normally use with Data Manipulation Language (DML) commands such as INSERT, UPDATE, and DELETE to incorporate Data Definition Language (DDL) commands like CREATE DATABASE, DROP TABLE, and ALTER TABLE. The new technology, called DDL triggers, comes in two flavors: database-level triggers that respond to changes to the database and server-level triggers that respond to changes on the server.
This article uses the sample AdventureWorks database and the SQL Server 2005 CTP to introduce DDL triggers and provide scenarios for using them.
Introducing DDL Triggers
Before delving into DDL triggers, learning what DDL triggers have in common with DML triggers may be helpful. First, DDL triggers are created, changed, and removed by using statements such as CREATE TRIGGER, ALTER TRIGGER, and DROP TRIGGER. The following is a typical DDL CREATE TRIGGER statement:
CREATE TRIGGER ddl_trig_loginAW
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
PRINT 'Added trigger event to DDLServerTriggerData'
INSERT INTO [AdventureWorks].[dbo].[dbo.DDLServerTriggerData]
(DDLServerEvent) VALUES ( EVENTDATA())
Also, like DML triggers, DDL triggers run in the same transaction as the DML statement. So for instance, when a DDL TSQL statement has completed, you can rollback a transaction whenever appropriate.
Finally, both DDL and DML triggers can use the new CLR functionality in SQL Server 2005 for running a managed code assembly uploaded to SQL Server.
Unlike DML triggers, DDL triggers respond to completely different events. As previously stated, DDL triggers respond to changes to a database or a server. Each time DDL statements make changes using commands such as CREATE TABLE, an event is fired. DDL triggers can subscribe to those events and execute other TSQL instructions in response to the events.
Some other differences between DDL and DML triggers include the following:
- DDL triggers do not support the INSTEAD of functionality in the CREATE TRIGGER statement.
- DDL triggers are missing the inserted and deleted tables common to DML triggers.
Now that you’re familiar with what DDL triggers do, you can set up a DDL trigger of your own.
Designing a DDL Trigger
When you define a DDL trigger, you must decide on the scope of your trigger. Scope determines whether the trigger executes at the database or the server level. (A forthcoming section discusses scope in more detail later in the article.)
After determining scope, you must decide whether the DDL trigger responds to a single DDL statement or a group of related statements. For example, the DDL trigger can respond to the DROP TABLE statement or all table operations (DROP, CREATE, ALTER). Typically, single events are named by using the TSQL statement and separating each word in the statement with an underscore (for example, CREATE_DATABASE). (Refer to the SQL Server 2005 Books Online for a complete list of events a DDL trigger can subscribe to.)
Once you’ve determined scope and events, you are ready to code the proper response. You will find the EVENTDATA() TSQL function most helpful for coding your response.
The EVENTDATA() Function
The EVENTDATA() function returns XML data with information such as event time, System Process ID (SPID), and type of event firing the trigger. A DDL trigger uses the EVENTDATA() function to determine how it must respond.
To retrieve information from XML data, you must use XQuery. A complete discussion of XQuery and the new XML Datatype is beyond the scope of this article. You can find an introduction of the XML Datatype on Developer.com at “The Fundamentals of the SQL Server 2005 XML 3Datatype“.
The following is a sample of XML data returned by the EVENTDATA() function:
<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>2005-07-30T10:48:52.537</PostTime>
<SPID>55</SPID>
<ServerName>PSE-TEST-JON1</ServerName>
<LoginName>PSE-TEST-JON1Administrator</LoginName>
<UserName>dbo</UserName>
<DatabaseName>AdventureWorks</DatabaseName>
<SchemaName>Person</SchemaName>
<ObjectName>Address</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON"
ANSI_NULL_DEFAULT="ON"
ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON"
ENCRYPTED="FALSE" />
<CommandText>CREATE TABLE [Person].[Address](
[AddressID] [int]
IDENTITY (1, 1)
NOT FOR REPLICATION NOT NULL,
[AddressLine1] [nvarchar](60) NOT NULL,
[AddressLine2] [nvarchar](60) NULL,
[City] [nvarchar](30) NOT NULL,
[StateProvinceID] [int] NOT NULL,
[PostalCode] [nvarchar](15) NOT NULL,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF_Address_rowguid] DEFAULT (NEWID()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT
[DF_Address_ModifiedDate] DEFAULT (GETDATE())
) ON [PRIMARY];</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
As you can see, there are other pieces of information that depend on the DDL statement firing the trigger. (See the SQL Server 2005 Books Online for a complete list of XML Schemas.) The following sample code illustrates how you will access the XML returned by the EVENTDATA() function:
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]',
'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]',
'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]',
'sysname')
One important point concerning XQuery: If you’re going to retrieve single values from the EVENTDATA() XML, use the “value” statement rather than the “query” statement. You will have better results with the formatting of the XML data.
DDL triggers can fire for changes in either the database or server. Now, the discussion turns to trigger scope in more detail.
