DML and DDL Triggers in in Microsoft SQL Server 2008

11/12/2008

Contents

DDL Triggers

DDL triggers execute under the following circumstances:

DDL is executed.
A user logs into an instance.

The general syntax for creating a DDL trigger is as follows:

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

DDL triggers can be scoped at either the database or instance level. To scope a DDL trigger at the instance level, you utilize the ON ALL SERVER option. To scope a DDL trigger at the database level, you utilize the ON DATABASE option.

The following is an example of a DDL trigger:

CREATE TRIGGER tddl_tabledropalterprevent
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
  PRINT 'You are attempting to drop or alter tables in production!'
  ROLLBACK;

NOTE

Almost all DDL commands run within the context of a transaction. Since a DDL trigger also runs within the same transaction context, any DDL statement running in the context of a transaction can be rolled back. ALTER DATABASE is one of the commands which does not execute in the context of a transaction, because the command affects objects outside of SQL Server that do not obey transactional semantics. Therefore an ALTER DATBASE command cannot be rolled back.

The value for the event type is derived from the DDL statement being executed, as listed in Table 14-1.

Table 14-1 DDL Trigger Event Types

DDL Command	Event Type
CREATE DATABASE	CREATE_DATABASE
DROP TRIGGER	DROP_TRIGGER
ALTER TABLE	ALTER_TABLE

Event types roll up within a command hierarchy called event groups. For example, the CREATE_TABLE, ALTER_TABLE, and DROP_TABLE event types are contained within the DDL_TABLE_EVENTS event group. Event types and event groups allow you to create flexible and compact DDL triggers.

Although DML triggers have access to the inserted and deleted tables, DDL triggers have access to the EVENTDATA() function which returns the following XML document that can be queried by using the value() method available through XQUERY:

<EVENT_INSTANCE>
    <EventType>type</EventType>
    <PostTime>date-time</PostTime>
    <SPID>spid</SPID>
    <ServerName>name</ServerName>
    <LoginName>name</LoginName>
    <UserName>name</UserName>
    <DatabaseName>name</DatabaseName>
    <SchemaName>name</SchemaName>
    <ObjectName>name</ObjectName>
    <ObjectType>type</ObjectType>
    <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>

You can retrieve the database, schema, object, and command that you executed, through the following query:

SELECT EVENTDATA().value
        ('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)'),
EVENTDATA().value
        ('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)'),
EVENTDATA().value
        ('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'),
EVENTDATA().value
        ('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)')

In the following exercise, you create a DDL trigger to prevent accidentally dropping tables in a production environment.

Create a Database Level DDL Trigger

Execute the following code against the SQL2008SBS database (the code is from the Chapter14\code3.sql file in the book’s accompanying samples):
```
CREATE TRIGGER tddl_preventdrop
ON DATABASE
FOR DROP_TABLE
AS
    PRINT 'Please disable DDL trigger before dropping tables'
    ROLLBACK TRANSACTION
GO
```
Validate your trigger by attempting to drop a table in the SQL2008SBS database.

In the following exercise, you create a logon trigger to limit the number of concurrent connections to a user.

Create an Instance Level DDL Trigger

Execute the following code (the code is from the Chapter14\code4.sql file in the book’s accompanying samples):

CREATE TRIGGER tddl_limitconnections
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF (SELECT COUNT(*) FROM sys.dm_exec_sessions
    WHERE is_user_process = 1 AND
        login_name = suser_sname()) > 5

    PRINT 'You are only allowed a maximum of 5 concurrent connections'
    ROLLBACK
END
GO