DDL Triggers

Introduction

OK, we all know that when we create objects in a database, we’re using Data Definition Language (DDL) commands. Every time we create an object, we’re firing an event. Since we’re firing an event, we can also do some action… That means we can create a TRIGGER to handle this action.

A DDL trigger is a trigger that acts when a DDL event fires. These events include the creation, modification, or removal of an object, not its records.

That’s the real difference in Data manipulation Language and Data Definition Language.

A DDL trigger gives you the opportunity to do some administrative work in response to the event. For example, you could set up a special notice when your developers create a table in a database. This could be really useful if you have opened up your databases to developers, and not required them to send all table creation statements to you so you can add them to your maintenance plans.

Creating a DDL Trigger

You create the DDL trigger just like you would a DML trigger.

CREATE TRIGGER t_objectName_event
ON DATABASE|ALL SERVER
FOR|AFTER event
AS
    some T-SQL

Everything up through the DATABASE|ALL SERVER should be familiar.

But, the choice of DATABASE versus ALL SERVER probably isn’t. If you want the trigger to act on the current database, type DATABASE. Whatever database you are connected to(or the last database you ran USE databaseName for) will be used. Any time the event happens in that database, your trigger will fire.

IF you want the event to fire every time that event happens, no matter which database it happens in, then you would choose ALL SERVER.

Notice, the FOR|AFTER choice. There is no such thing as an INSTEAD OF DDL TRIGGER.

The last choice you have to make is the event you wish to trap. The following tables list the events you can choose, first listing the events you can track on a per database scope, then on a per server scope. This list comes directly from Microsoft’s Books On Line.

DDL Statements with Database Scope


CREATE_APPLICATION_ROLE (Applies to CREATE APPLICATION ROLE statement and sp_addapprole. If a new schema is created, this event also triggers a CREATE_SCHEMA event.) ALTER_APPLICATION_ROLE (Applies to ALTER APPLICATION ROLE statement and sp_approlepassword.) DROP_APPLICATION_ROLE (Applies to DROP APPLICATION ROLE statement and sp_dropapprole.)
CREATE_ASSEMBLY ALTER_ASSEMBLY DROP_ASSEMBLY
ALTER_AUTHORIZATION_DATABASE (Applies to ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.)
CREATE_CERTIFICATE ALTER_CERTIFICATE DROP_CERTIFICATE
CREATE_CONTRACT DROP_CONTRACT
GRANT_DATABASE DENY_DATABASE REVOKE_DATABASE
CREATE_EVENT_NOTIFICATION DROP_EVENT_NOTIFICATION
CREATE_FUNCTION ALTER_FUNCTION DROP_FUNCTION
CREATE_INDEX ALTER_INDEX DROP_INDEX
CREATE_MESSAGE_TYPE ALTER_MESSAGE_TYPE DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION ALTER_PARTITION_FUNCTION DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME ALTER_PARTITION_SCHEME DROP_PARTITION_SCHEME
CREATE_PROCEDURE ALTER_PROCEDURE DROP_PROCEDURE
CREATE_QUEUE ALTER_QUEUE DROP_QUEUE
CREATE_REMOTE_SERVICE_BINDING ALTER_REMOTE_SERVICE_BINDING DROP_REMOTE_SERVICE_BINDING
CREATE_ROLE (Applies to CREATE ROLE statement, sp_addrole, and sp_addgroup.) ALTER_ROLE DROP_ROLE (Applies to DROP ROLE statement, sp_droprole, and sp_dropgroup.)
CREATE_ROUTE ALTER_ROUTE DROP_ROUTE
CREATE_SCHEMA (Applies to CREATE SCHEMA statement, sp_addrole, sp_adduser, sp_addgroup, and sp_grantdbaccess.) ALTER_SCHEMA (Applies to ALTER SCHEMA statement and sp_changeobjectowner.) DROP_SCHEMA
CREATE_SERVICE ALTER_SERVICE DROP_SERVICE
CREATE_STATISTICS DROP_STATISTICS UPDATE_STATISTICS
CREATE_SYNONYM DROP_SYNONYM
CREATE_TABLE ALTER_TABLE DROP_TABLE
CREATE_TRIGGER ALTER_TRIGGER DROP_TRIGGER
CREATE_TYPE (Applies to CREATE TYPE statement and sp_addtype.) DROP_TYPE (Applies to DROP TYPE statement and sp_droptype.)
CREATE_USER (Applies to CREATE USER statement, sp_adduser, and sp_grantdbaccess.) ALTER_USER DROP_USER (Applies to DROP USER statement, sp_dropuser, and sp_revokedbaccess.)
CREATE_VIEW ALTER_VIEW DROP_VIEW
CREATE_XML_SCHEMA_COLLECTION ALTER_XML_SCHEMA_COLLECTION DROP_XML_SCHEMA_COLLECTION

DDL Statements with Server Scope


ALTER_AUTHORIZATION_SERVER
CREATE_DATABASE ALTER_DATABASE DROP_DATABASE
CREATE_ENDPOINT ALTER_ENDPOINT DROP_ENDPOINT
CREATE_LOGIN (Applies to CREATE LOGIN statement, sp_addlogin, sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a nonexistent login that must be implicitly created.) ALTER_LOGIN (Applies to ALTER LOGIN statement, sp_defaultdb, sp_defaultlanguage, sp_password, and sp_change_users_login when Auto_Fix is specified.) DROP_LOGIN (Applies to DROP LOGIN statement, sp_droplogin, sp_revokelogin, and xp_revokelogin.)
GRANT_SERVER DENY_SERVER REVOKE_SERVER

As mentioned for DML triggers, you manage DDL triggers by modifying or deleting them. These are done using the same description we saw for DML triggers. Other than the fact that you can now trap some very deep level events, management is just as easy as DML triggers. Don’t let this power go to your head. You still need to make sure you are using the proper tool for the job.

You can’t go crazy with triggers, or you can bring your server to a screeching halt.

If you have any questions about DDL triggers, or anything else. Let me know. I’m here to help!

Leave a Reply

Your email address will not be published. Required fields are marked *