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!