DDL Trigger

DDL Triggers:

DDL triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements.

DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.

Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established.

Triggers can be created directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server. SQL Server allows for creating multiple triggers for any specific statement.

They can be used to perform administrative tasks in the database such as auditing and regulating database operations.

--Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (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

-- Trigger on a LOGON event (Logon Trigger) 

 

CREATE TRIGGER trigger_name  

ON ALL SERVER  

[ WITH <logon_trigger_option> [ ,...n ] ] 

{ FOR| AFTER } LOGON   

AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] } 

 

<logon_trigger_option> ::= 

    [ ENCRYPTION ] 

    [ EXECUTE AS Clause ] 

Understanding DDL Triggers:

DDL triggers, like regular triggers, fire stored procedures in response to an event. However, unlike DML triggers, they do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they fire in response to a variety of Data Definition Language (DDL) events.

These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP. Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.

“Test your DDL triggers to determine their responses to system stored procedures that are run. For example, the CREATE TYPE statement and the sp_addtype stored procedure will both fire a DDL trigger that is created on a CREATE_TYPE event.”

DDL triggers can be used for administrative tasks such as auditing and regulating database operations.

Use DDL triggers when you want to do the following:

·         You want to prevent certain changes to your database schema.

·         You want something to occur in the database in response to a change in your database schema.

·         You want to record changes or events in the database schema.

DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers.

The following example shows how a DDL trigger can be used to prevent any table in a database from being modified or dropped.

CREATE TRIGGER safety

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

   PRINT 'You must disable Trigger "safety" to drop or alter tables!'

   ROLLBACK ;

DDL triggers can fire in response to a Transact-SQL event that is processed in the current database or on the current server. The scope of the trigger depends on the event.

Types of DDL Triggers

Transact-SQL DDL Trigger

A special type of Transact-SQL stored procedure that executes one more Transact-SQL statements in response to a server-scoped or database-scoped event. For example, a DDL Trigger may fire if a statement such as ALTER SERVER CONFIGURATION is executed or if a table is deleted by using DROP TABLE.

Database-scoped trigger:

In the following example, DDL trigger safety will fire whenever a DROP_TABLE or ALTER_TABLE event occurs in the database.

CREATE TRIGGER safety

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

   PRINT 'You must disable Trigger "safety" to drop or alter tables!'

   ROLLBACK

Server-scoped trigger:

In the following example, a DDL trigger prints a message if any CREATE_DATABASE event occurs on the current server instance.

IF EXISTS (SELECT * FROM sys.server_triggers

    WHERE name = 'ddl_trig_database')

DROP TRIGGER ddl_trig_database

ON ALL SERVER;

GO

CREATE TRIGGER ddl_trig_database

ON ALL SERVER

FOR CREATE_DATABASE

AS

    PRINT 'Database Created.'

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

GO

CLR DDL Trigger

Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server.

·         DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers. DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures.

·         DDL triggers do not create the special inserted and deleted tables.

·         Multiple triggers to be created for each DDL event.

·         Unlike DML triggers, DDL triggers are not scoped to schemas. Therefore, functions such as OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, and OBJECTPROPERTYEX cannot be used for querying metadata about DDL triggers. Use the catalog views instead.

·         Server-scoped DDL triggers appear in the SQL Server Management Studio Object Explorer in the Triggers folder. This folder is located under the Server Objects folder. Database-scoped DDL triggers appear in the Database Triggers folder. This folder is located under the Programmability folder of the corresponding database.

·         The information about an event that fires a DDL trigger, and the subsequent changes caused by the trigger, is captured by using the EVENTDATA function.

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

Understanding Trigger Scope

DDL triggers can fire in response to a Transact-SQL event processed in the current database, or on the current server.

The scope of the trigger depends on the event. For example, a DDL trigger created to fire in response to a CREATE_TABLE event can do so whenever a CREATE_TABLE event occurs in the database, or on the server instance.

A DDL trigger created to fire in response to a CREATE_LOGIN event can do so only when a CREATE_LOGIN event occurs in the server.

In the following example, DDL trigger safety will fire whenever a DROP_TABLE or ALTER_TABLE event occurs in the database.

CREATE TRIGGER safety

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

   PRINT 'You must disable Trigger "safety" to drop or alter tables!'

   ROLLBACK

In the following example, a DDL trigger prints a message if any CREATE_DATABASE event occurs on the current server instance. The example uses the EVENTDATA function to retrieve the text of the corresponding Transact-SQL statement.

CREATE TRIGGER ddl_trig_database

ON ALL SERVER

FOR CREATE_DATABASE

AS

    PRINT 'Database Created.'

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

GO

·         Database-scoped DDL triggers are stored as objects in the database in which they are created. DDL triggers can be created in the master database and behave just like those created in user-designed databases. You can obtain information about DDL triggers by querying the sys.triggers catalog view.

·         You can query sys.triggers within the database context in which the triggers are created or by specifying the database name as an identifier, such as master.sys.triggers.

·         Server-scoped DDL triggers are stored as objects in the master database. However, you can obtain information about server-scoped DDL triggers by querying the sys.server_triggers catalog view in any database context.

Specifying a Transact-SQL Statement or Group of Statements

Selecting a Particular DDL Statement to Fire a DDL Trigger

DDL triggers can be designed to fire after one or more particular Transact-SQL statements are run. In the previous example, trigger safety fires after any DROP_TABLE or ALTER_TABLE event. For lists of the Transact-SQL statements that can be specified to fire a DDL trigger, and the scope at which the trigger can fire, see DDL Events

Selecting a Predefined Group of DDL Statements to Fire a DDL Trigger

A DDL trigger can fire after execution of any Transact-SQL event that belongs to a predefined grouping of similar events. For example, if you want a DDL trigger to fire after any CREATE TABLE, ALTER TABLE, or DROP TABLE statement is run, you can specify FOR DDL_TABLE_EVENTS in the CREATE TRIGGER statement. After CREATE TRIGGER is run, the events that are covered by an event group are added to the sys.trigger_events catalog view.

In SQL Server 2005, if a trigger is created on an event group, sys.trigger_events does not include information about the event group,sys.trigger_events includes information only about the individual events covered by that group. In SQL Server 2008 and higher, sys.trigger_events persists metadata about the event group on which the triggers is created, and also about the individual events that the event group covers. Therefore, changes to the events that are covered by event groups in SQL Server 2008 and higher do not apply to DDL triggers that are created on those event groups in SQL Server 2005.

For a list of the predefined groups of DDL statements that are available for DDL triggers, the particular statements the event groups cover, and the scopes at which these event groups can be programmed, see DDL Event Groups.

Preventing changes to database objects:

CREATE TRIGGER trgNoMonkeying 

ON DATABASE

   FOR DROP_TABLE, ALTER_TABLE 
AS

    DECLARE @Message VARCHAR(255) 
    SELECT  @message = 'You are forbiddent to alter or delete the ''' 
            + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 
                                'nvarchar(100)') + ''' table' 
    RAISERROR ( @Message, 16, 1 ) 
    ROLLBACK ; 
GO 

CREATE TABLE NewTable (Column1 INT); 
GO 
DROP TABLE NewTable 

/* 
'Msg 3609, Level 16, State 2, Line 1 
The transaction ended in the trigger. The batch has been aborted. 
Msg 50000, Level 18, State 1, Procedure trg, Line 8 
You are forbiddent to alter or delete the 'NewTable' table' 

Preventing changes to server level events:

IF EXISTS ( SELECT  *

            FROM    sys.server_triggers

            WHERE   name = 'trgLogServerSecurityEvents' ) 

    DROP TRIGGER trgLogServerSecurityEvents ON ALL SERVER

GO

CREATE TRIGGER trgLogServerSecurityEvents ON ALL SERVER

    FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN, GRANT_SERVER, DENY_SERVER,

        REVOKE_SERVER, ALTER_AUTHORIZATION_SERVER

AS

    DECLARE @Message VARCHAR(255)

    SELECT  @message = 'You are forbiddent to create '''

            + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',

                                'nvarchar(100)') + ''

    RAISERROR ( @Message, 16, 1 )

    ROLLBACK ;

GO