SQL Server Event Handling: DDL Events

For many SQL Server professionals, our first overt need to handle an event was for a data-related action (ie an insert, update, or delete). Naturally, we use triggers to handle these events. Or more specifically, DML triggers. And if you are familiar with them, working with DDL triggers should be familiar too. MSDN documentation explains that DDL triggers fire in response to a variety of DDL events. To put it another way, DDL triggers are the event handlers for DDL events. This simple relationship between event and event handler makes working with DDL triggers straightforward and uncomplicated. You might even go so far as to say it's easy. But I'll let you be the judge.

As opposed to DML events, which occur for data-related actions, DDL events occur for object-related actions (I'm using the word "object" loosely in this context). Here are a few examples:

• A database is created, altered, or dropped.
• A table is created, altered, or dropped.
• A permission is granted, revoked, or denied.
• A database user is added to (or dropped from) a database role.

EVENTDATA

Although there are many similarities between DML and DDL triggers, one significant difference is that DDL triggers use the EVENTDATA function to return information about the event. The return value of the function is XML. (For more information, see the Schemas Returned section of the MSDN EVENTDATA documentation and/or the Microsoft SQL Server XML Schemas Web page.) While the XML schemas can vary from one event to another, they are not complex. A few simple EVENTDATA().value() statements are usually sufficient to get at the relevant event data.

Handling DDL Events

Here is an example of a DDL trigger that handles a DROP_TABLE event. ON DATABASE is specified. Therefore, the trigger only handles events for the [AdventureWorks2012] database. Variables are declared for the specific event data we are interested in. The .value() method performs an XQuery on the EVENTDATA and returns a value of the SQL type specified (in this case, SYSNAME).

USE AdventureWorks2012
GO

CREATE TRIGGER trgDropTable
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    --Get relevant info from EVENTDATA()
    DECLARE @Database SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME');
    DECLARE @Schema SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME');
    DECLARE @Table SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME');
    DECLARE @Login SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME');
    DECLARE @Cmd SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'SYSNAME');

    PRINT 'Table dropped';
    PRINT 'Database: [' + @Database + ']';
    PRINT 'Table Name: [' + @Schema + '].[' + @Table + ']';
    PRINT 'Login: [' + @Login + ']';
    PRINT 'TSQL Command: [' + @Cmd + ']';
END;

To test the trigger, let's create a new table and immediately drop it. Afterwards, we'll see the output of the PRINT statements in the Messages tab of SSMS.

Here is the full XML text returned by EVENTDATA() when the table was dropped. The data items retrieved by the EVENTDATA().value method calls are highlighted. Hopefully, this will make the EVENTDATA().value method calls in the trigger more understandable.

<EVENT_INSTANCE>
  <EventType>DROP_TABLE</EventType>
  <PostTime>2016-06-30T13:45:46.937</PostTime>
  <SPID>61</SPID>
  <ServerName>Archibald\Tiny</ServerName>
  <LoginName>Celtics\dmason</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>AdventureWorks2012</DatabaseName>
  <SchemaName>guest</SchemaName>
  <ObjectName>DaveTemp</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>DROP TABLE guest.DaveTemp;</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

ROLLBACK

DDL triggers and the events they handle are run within the same transaction, which can be rolled back. This is a powerful and convenient feature. It gives you the ability to programmatically "undo" undesirable events. Let's look at a variation of the previous DROP_TABLE trigger we created. This script will create a temporary logging table, drop the previous trigger, and create it again. This time, the trigger will roll back the transaction, preventing the table from being dropped. Then it inserts a message to the log table.

USE AdventureWorks2012
GO

CREATE TABLE guest.LogTemp(
    ID INT IDENTITY,
    LogMsg VARCHAR(4000),
    LogDate DATETIME2
)
GO

DROP TRIGGER trgDropTable
ON DATABASE;
GO

CREATE TRIGGER trgDropTable
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    --Get relevant info from EVENTDATA()
    DECLARE @Schema SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME');
    DECLARE @Table SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME');

    --Optional: error message for end user.
    RAISERROR ('Tables cannot be dropped.', 16, 1);

    --Rollback transaction for the DROP TABLE statement that fired the DDL trigger
    ROLLBACK;

    BEGIN TRAN
        INSERT INTO guest.LogTemp(LogMsg, LogDate)
        VALUES('Table was not dropped: [' + @Schema + '].[' + @Table + ']', CURRENT_TIMESTAMP);
    COMMIT
END;

When we recreate the temp table and try to drop it, we see the following output in SSMS:

Lastly, if we query both tables, we see the temp table still exists (even though it has no rows yet). Our log table has one row, which was inserted by the DDL trigger after the DDL event was rolled back.

ALTER DATABASE

There is at least one "gotcha" I know of: ROLLBACK doesn't work with an ALTER_DATABASE DDL event. It's because most operations for ALTER DATABASE are not transacted operations. You *can* include the ROLLBACK statement within a DDL trigger for ALTER_DATABASE. You won't get any parser errors or other complaints. It just doesn't work. (More info on Microsoft Connect.)

DDL Events

The full list of available DDL events is documented on MSDN. You can also query sys.event_notification_event_types for the list of available events. Special thanks to Jonathan Kehayias (b|t) for help with this query.

SELECT 
    CASE b.type_name 
        WHEN 'DDL_SERVER_LEVEL_EVENTS' THEN 'Server'
        WHEN 'DDL_DATABASE_LEVEL_EVENTS' THEN 'Server or Database'
    END AS scope,
    COALESCE(e.type_name, d.type_name, c.type_name, b.type_name, a.type_name) AS event_name,
    COALESCE(e.type, d.type, c.type, b.type, a.type) AS type,
    COALESCE(e.parent_type, d.parent_type, c.parent_type, b.parent_type, a.parent_type) AS parent_type
FROM sys.event_notification_event_types AS a
LEFT JOIN sys.event_notification_event_types AS b ON a.type = b.parent_type
LEFT JOIN sys.event_notification_event_types AS c ON b.type = c.parent_type
LEFT JOIN sys.event_notification_event_types AS d ON c.type = d.parent_type
LEFT JOIN sys.event_notification_event_types AS e ON d.type = e.parent_type
WHERE a.parent_type IS NULL AND a.type = 10001
ORDER BY 
    b.type_name,
    COALESCE(e.parent_type, d.parent_type, c.parent_type, b.parent_type, a.parent_type)

A couple of things need to be noted here. Some DDL events only have scope at the server level (for instance, CREATE_DATABASE or DROP_LINKED_SERVER_LOGIN). Other DDL events have server or database scope: triggers for these events can be created to handle events that occur in a specific database, or anywhere within the SQL instance. Also, there is a hierarchy of DDL events and DDL Event Groups. Each DDL event belongs to a DDL event group (parent_type), which belongs to another DDL event group, etc. At the top of the hierarchy is the DDL_EVENTS event group.