DDL Triggers - SQL Server 2008

A common security requirement for database projects is the ability to audit any kind of change to the data. Although triggers as implemented in past versions of SQL Server made this very easy for data modification (inserts and updates), it was quite difficult to audit changes to the underlying schema. DDL triggers are the answer to this problem.

A DDL trigger can be defined at either a server-wide or database-wide granularity, and triggers can be set to fire for creation, alteration, or deletion of virtually every SQL Server object type. Unlike DML triggers, there are no inserted or updated tables, and the update() function does not apply. Instead, data about the event that fired the trigger can be obtained via the eventdata() function.

DDL triggers are created, altered, and dropped using the same T-SQL statements as DML triggers, with a slightly different syntax. DDL triggers, like DML triggers, can also be managed using catalog views (more information on this is presented later in the section “Enumerating DDL Triggers Using Catalog Views”).

Creating and Altering DDL Triggers

The syntax for creating or altering a DDL trigger is as follows:

{ CREATE | ALTER } 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

Note that unlike DML triggers, DDL triggers are not defined on database objects and cannot be defined as INSTEAD OF triggers.

DDL triggers can be specified on either an ALL SERVER or a DATABASE level, and the { event_type | event_group } section controls which event will cause the trigger to fire. If a trigger is created ON ALL SERVER, it will fire for any event for which it’s defined, on any database on the entire server. On the other hand, a trigger created on ON DATABASE will fire only if the event occurs in the database in which it was created.

The ALL SERVER and DATABASE levels have their own event types and event groups for which triggers can be defined. Database-level events such as CREATE_TABLE cannot be used for a serverleveltrigger, and server-level events such as ALTER_LOGIN cannot be used for a database-level trigger. The following server-level events can be used for DDL triggers:

  • CREATE|ALTER|DROP LOGIN
  • CREATE|DROP HTTP ENDPOINT
  • GRANT|DENY|REVOKE SERVER ACCESS
  • CREATE|ALTER|DROP CERT

All other events that can be used for DDL triggers are database-level events. These include events such as CREATE|ALTER|DROP TABLE, CREATE|ALTER|DROP TRIGGER, and so on. Every DDL event that can occur in the database can be caught using a DDL trigger. A complete list of the events available to DDL triggers can be found in the SQL Server Books Online topic “Event Groups for Use with DDL Triggers.”

One particularly useful event group is the DDL_DATABASE_LEVEL_EVENTS catchall. This group includes all DDL events that can occur in a database, and it is useful for situations in which a DBA might wish to either log or block all changes to a database. For instance, the following DDL trigger, which can be created in any database, will roll back any DDL modifications a user attempts to make, unless the trigger itself is dropped or disabled:

CREATE TRIGGER NoChanges
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SELECT 'DDL IS NOT ALLOWED IN THE CURRENT DATABASE!'
SELECT 'TO ALLOW DDL, DROP THE NoChanges trigger.'
ROLLBACK

Dropping DDL Triggers

Dropping DDL triggers is slightly different than dropping DML triggers, as the trigger’s scope must be specified in the statement. The syntax for dropping a DDL trigger is as follows:

DROP TRIGGER trigger_name [ ,...n ]
ON { DATABASE | ALL SERVER } [ ; ]

It’s important to remember the additional ON clause when working with DDL triggers. Failing to include it will yield an error message stating that the specified trigger does not exist. This can be frustrating when you know that the trigger exists, but the system insists that it can’t be found.

Enabling and Disabling DDL Triggers

DDL triggers, like DML triggers, can be enabled and disabled. In SQL Server this is done via two statements: ENABLE TRIGGER and DISABLE TRIGGER. These statements have similar syntax to DROP
TRIGGER:

{ ENABLE | DISABLE } TRIGGER trigger_name
ON { DATABASE | SERVER } [ ; ]

Note that although DDL triggers can be enabled or disabled only by using these statements, DML triggers can still be enabled or disabled using ALTER TABLE.

Enumerating DDL Triggers Using Catalog Views

For obtaining information about database DDL triggers, DBAs can use the catalog views sys.triggers and sys.trigger_events. Server-level triggers can be enumerated using sys.server_triggers and sys.server_trigger_events. The sys.triggers and sys.server_triggers views have the same column definitions, except for two columns in the sys.triggers view that do not apply to DDL triggers: is_not_for_replication and is_instead_of_trigger. The events tables, on the other hand, have the same column definitions.

The parent_class_desc column can be used to differentiate DDL triggers from DML triggers when querying sys.triggers. The following query will return the name and creation date of all DDL triggers in the current database:

SELECT
name,
create_date
FROM sys.triggers
WHERE parent_class_desc = 'DATABASE'

The events views are related to the triggers views by the object_id column. To find out which events the active server-level triggers in the system will be fired on, use the following query:

SELECT
tr.name,
ev.type_desc
FROM sys.server_triggers tr
JOIN sys.server_trigger_events ev ON tr.object_id = ev.object_id
WHERE tr.is_disabled = 0

Programming DDL Triggers with the eventdata() Function

Without a way to figure out under exactly which conditions the trigger fired, DDL triggers would be relatively useless for tasks such as logging the events taking place in a database and when they are occurring. To provide this functionality, SQL Server includes the eventdata() function. This function returns an XML document containing information about the event that fired the trigger.

Each event can return data using a different XML schema, but they all share common base schemas. Server-level events use the following base schema:

<EVENT_INSTANCE>
<EventType>name</EventType>
<PostTime>date-time</PostTime>
<SPID>spid</SPID>
<ServerName>server_name</ServerName>
<LoginName>login</LoginName>
</EVENT_INSTANCE>

Database-level events add a UserName element:

<EVENT_INSTANCE>
<EventType>name</EventType>
<PostTime>date-time</PostTime>
<SPID>spid</SPID>
<ServerName>server_name</ServerName>
<LoginName>login</LoginName>
<UserName>user</UserName>
</EVENT_INSTANCE>

Various elements appear in the schemata for events as appropriate. For instance, the objectbased events (CREATE_TABLE, ALTER_PROCEDURE, and so on) add elements for DatabaseName, SchemaName, ObjectName, and ObjectType, and a TSQLCommand element that contains SetOptions and CommandText elements.

By querying the XML document, it’s possible to determine every aspect of the event that fired the trigger. For instance, the following trigger echoes back the username, table name, and CREATE TABLE syntax used every time a table is created or altered in the database:

CREATE TRIGGER ReturnEventData
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
DECLARE @eventData XML
SET @eventData = eventdata()
SELECT
@eventData.query('data(/EVENT_INSTANCE/UserName)') AS UserName,
@eventData.query('data(/EVENT_INSTANCE/ObjectName)') AS ObjectName,
@eventData.query('data(/EVENT_INSTANCE/TSQLCommand/ CommandText)') AS CommandText

Of course, this trigger doesn’t have to just select and return the data. The data can just as easily be inserted into a logging table:

CREATE TABLE DDLEventLog
(
EventDate DATETIME NOT NULL,
UserName SYSNAME NOT NULL,
ObjectName SYSNAME NOT NULL,
CommandText VARCHAR(MAX) NOT NULL
)
GO
CREATE TRIGGER ReturnEventData
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
DECLARE @eventData XML
SET @eventData = eventdata()
INSERT DDLEventLog (EventDate, UserName, ObjectName, CommandText)
SELECT
GETDATE() AS EventDate,
@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME')
AS UserName,
@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
AS ObjectName,
@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)') AS CommandText

The event data can also be parsed and used to make decisions about what course of action to take. For instance, if you have a table called DontDropMe, you could write the following trigger to keep it from being dropped:

CREATE TRIGGER DontDropDontDropMe
ON DATABASE
FOR DROP_TABLE
AS
DECLARE @eventData XML
SET @eventData = eventdata()
DECLARE @objectName VARCHAR(MAX)
SET @objectName =
CONVERT(VARCHAR(MAX), @eventData.query('data (/EVENT_INSTANCE/ObjectName)'))
IF @objectName = 'DontDropMe'
BEGIN
PRINT 'You cannot drop DontDropMe!'
ROLLBACK
END

Since the transaction is rolled back if the object name is DontDropMe, it’s impossible to drop that table when the Dont Drop Dont DropMe trigger is applied to the database. When using DDL triggers for this type of object-level protection, remember that the trigger fires after the event has finished, but before the transaction has committed. If a large transaction has taken place and needs to be rolled back, excessive locking could occur. Proceed with caution until (we hope) you see instead-of DDL triggers implemented in a future version of SQL Server.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics