Home > Sample chapters > Programming > SQL Server

DML and DDL Triggers in in Microsoft SQL Server 2008

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;

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

  1. 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
  2. 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

  1. 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
  2. Validate your trigger by attempting to create more than five concurrent connections.