SQL Server 2005 - Using DDL Triggers

SQL Server 2005 - Using DDL Triggers

In this tutorial you will learn about DDL Triggers vs DML Triggers, Syntax for creating a DDL Trigger, Designing a DDL Trigger, The EVENTDATA() Function, Implementing a Database-Scoped DDL Trigger, Implementing a Server-Scoped DDL Trigger, Event Notifications vs. DDL Triggers and General Trigger Considerations.

DDL triggers fire stored procedures in response to DDL Statements which begin with CREATE, ALTER and DROP. The DDL triggers require that the DDL statements that trigger them are run and these triggers cannot be used as INSTEAD OF triggers. The DDL events that fire the DDL triggers have to be specified in T-SQL DDL syntax. System stored procedures that perform DDL like operations are not supported. The scope of the trigger is confined to the current database or current server and the scope depends on the T-SQL event being processed. These triggers can be used for Administrative tasks such as auditing and regulating database operations. DDL triggers can be used to perform the following operations

1. Prevent changes to Database schema
2. Fire events in response to changes in the database schema
3. Record changes or events in the database schema

DDL Triggers vs DML Triggers

While DML triggers operate on INSERT, UPDATE and DELETE statements to enforce business rules and data integrity checks, DDL Triggers operate on CREATE, ALTER, DROP and other DDL statements and enforce business rules that affect databases across servers. Unlike DML triggers, DDL triggers are not scoped to schemas. However, both the types of triggers are created, modified and dropped using similar T_SQL syntax. They run managed code packaged in an assembly created in the Microsoft .NET framework and uploaded in the SQL server. Moreover, like DML triggers, DDL triggers can create and use more than one trigger on the same T_SQL Statement. The DDL trigger and the statement that fires the trigger are run within the same transaction. This transaction can be rolled back from within the trigger when there is an error. If a DDL trigger is run from a batch and explicitly includes the ROLLBACK TRANSACTION statement it will cancel the whole batch. Finally both DDL and DML triggers can be nested.

Syntax for creating a DDL Trigger

CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH <ddl_trigger_option> [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] } <ddl_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] <method_specifier> ::= assembly_name.class_name.method_name</body></html></body></html>

Designing a DDL Trigger

Before designing a DDL trigger the Administrator must define the scope of the trigger. He must decide whether he wants the trigger to execute at the database or server level. He must also decide whether the trigger will respond to a single DDL statement or a group of related statements. Once these have been decided upon the DBA can go on to use the EVENTDATA() TSQL function for coding the response.

This function returns XML data with event time, system process ID and event type details. The EVENTDATA() function then used by the DDL trigger to determine the kind of response to the event. The DBA can then use the XQuery to retrieve information from XML data.

The following is a sample of XML data returned by the EVENTDATA() function: <EVENT_INSTANCE> <EventType>CREATE_TABLE</EventType> <PostTime>2005-11-30T10:48:52.537</PostTime> <SPID>55</SPID> <ServerName>PSE-TEST-JON1</ServerName> <LoginName>PSE-TEST-JON1\Administrator</LoginName> <UserName>dbo</UserName> <DatabaseName>Exforsys</DatabaseName> <SchemaName>Personnel</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 [Personnel].[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>

The XML returned by the function will be accessed using the following code.

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')

If single values have to be retrieved using EVENTDATA() then the query statement wil have to be used.

Implementing a Database-Scoped DDL Trigger

Database scoped DDL triggers are fired whenever events modifying database schema are executed. The CREATE TRIGGER ON DATABASE directive is used to scope the database scoped trigger. These triggers are stored within the database and fires on events with the exception of those relating to temporary tables.

Implementing a Server-Scoped DDL Trigger

A limited set of server changes trigger off Server scoped DDL Triggers. The CREATE TRIGGER ON ALL SERVER directive scopes the DDL trigger to the server. These are stored in the master database as objects. The events are related to database changes such as CREATE, ALTER, DROP or security level changes such as CREATE LOGIN, ALTER LOGIN or DROP LOGIN.

Event Notifications vs. DDL Triggers

DDL Triggers and Event Notifications both provide login mechanisms. However, the Event notification is an asynchronous event and requires the SQL Server 2005 service Broker for processing. The event notification can be located on another server and still be processed by the current instance issuing the notification. The Event notifications can also respond to trace events. Like DDL Triggers, Event notifications utilize similarly formatted xml but do not use the EVENTDATA() function.

DDL Triggers on the other hand use the EVENTDATA() function and run in the scope of the transaction and hence a transaction can be rolled back in a DDL trigger. This is not so in an Event notification.

General Trigger Considerations

As stated earlier multiple triggers can be created for each DML or DDL statement. In SQL Server 2005 if a CREATE TRIGGER FOR UPDATA is executed on a table that already has an UPDATE Trigger, an additional trigger can be created.

Recursive Triggers enable recursions to occur when the RECURSIVE TRIGGER setting is enabled using the ALTER DATABASE. Indirect recursion occurs when cascading triggers are fired and tables are updated. Direct recursion occurs when the trigger is fired to update a table directly.

When the RECURSIVE_TRIGGERS setting is disabled, direct recursions are prevented. Indirect Recursion can be disabled by setting the nested triggers server option to 0 by using sp_configure. If any one trigger is set to perform a rollback transaction all triggers will cease to be executed.

Triggers can be nested up to 32 levels maximum. If two or more triggers are created for a table, the firing of one trigger will lead to the cascading firing of other triggers. If any one trigger in the chain goes into a loop the trigger is cancelled. Nested triggers can be disabled using the sp_configure to 0. When nested triggers are set to 0, recursive triggers are also disabled.

Triggers share with stored procedures and batches, the capability of referring to tables that do not exist at compile time. This ability is called the deferred name resolution.