In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

Today we are going to take a look at DDL triggers. DDL triggers have Server or Database Scope, DDL triggers can fire in response to a T-SQL event processed in the current database, or on the current server. For example a CREATE_TABLE event can fire on a database level as well as on a server level, a DROP_DATABASE can only fire on the server level. DDL triggers can be a very powerful tool for auditing purposes as well.

Let’s take a quick look at a DDL trigger for a CREATE_TABLE event, this trigger will fire every time someone issues a CREATE TABLE T-SQL DDL statement

This trigger is created to capture all CREATE_TABLE events in the tempdb database

USE tempdb
GO

CREATE TRIGGER DBLevelsafety 
ON DATABASE 
FOR CREATE_TABLE
AS 
   PRINT 'You must disable Trigger "DBLevelsafety" to drop or alter tables!' 
   ROLLBACK
;

If you try to create a table now in the tempdb database

USE tempdb
GO
CREATE TABLE bla(id int)

You will get the following message

_You must disable Trigger “DBLevelsafety” to drop or alter tables!

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted._

Let’s drop the trigger

DROP TRIGGER DBLevelsafety

_Msg 3701, Level 11, State 5, Line 1

Cannot drop the trigger ‘DBLevelsafety’, because it does not exist or you do not have permission._

In order to drop a DDL trigger, the syntax is a little different from a DML trigger, you need to add ON DATABASE or ON ALL SERVER after the name, Try it again

DROP TRIGGER DBLevelsafety ON DATABASE

Now create a DDL triggers that will fire in any database.

CREATE TRIGGER ServerLevelsafety 
ON ALL SERVER 
FOR CREATE_TABLE
AS 
   PRINT 'You must disable Trigger "ServerLevelsafety" to drop or alter tables!' 
   ROLLBACK
;

Go into the model database and try to create a table

USE model
GO

CREATE TABLE temp(id int)

_You must disable Trigger “ServerLevelsafety” to drop or alter tables!

Msg 3609, Level 16, State 2, Line 2

The transaction ended in the trigger. The batch has been aborted._

Now switch to tempdb and see if you can create a table there

USE tempdb
GO

CREATE TABLE temp(id int)

_You must disable Trigger “ServerLevelsafety” to drop or alter tables!

Msg 3609, Level 16, State 2, Line 2

The transaction ended in the trigger. The batch has been aborted._

Nope, as you can see you can’t create a table in any database

Drop the trigger

DROP TRIGGER ServerLevelsafety ON ALL SERVER

Verify that you can indeed create a table again

USE tempdb
GO

CREATE TABLE temp(id int)

Let’s take a look at another example. What if you want to log anytime someone issues a DROP TABLE command in any database? You can easily accomplish that, and by using the EVENTDATA function you can capture the exact command that was used.

First create a new database with one table, this table will be used to store the messages from the trigger

CREATE DATABASE Logs
GO

USE Logs
GO

CREATE TABLE TriggerLog(DDL VARCHAR(300),DatabaseName VARCHAR(100),ExecutedBy VARCHAR(100), EventDate DATETIME)
GO

Create the following trigger

CREATE TRIGGER trDropTable
ON ALL SERVER -- A server level trigger
FOR DROP_TABLE --Event we want to capture
AS
  INSERT tempdb..TriggerLog
  SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'), -- T-SQL command
		 EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)'),  -- database name
        COALESCE(SUSER_SNAME(),USER_NAME()),
        GETDATE();
GO

The EVENT_INSTANCE/TSQLCommand/CommandText part of EVENTDATA will give us the actual command that was executed, the /EVENT_INSTANCE/DatabaseName part will give use the database name.

It is time to take it for a test, run the following block of code

USE Tempdb
GO
CREATE TABLE bla(id int)
GO
DROP TABLE bla

USE model
GO
CREATE TABLE bla(id int)
GO
DROP TABLE bla

Now if you look in your TriggerLog table, you will see 2 rows

SELECT * FROM Logs..TriggerLog

As you can see we have the two rows, since we grab the database name as well we can see what database it was run in in case you have the same table name in multiple databases

DDL            DatabaseName    ExecutedBy  EventDate
--------------  -------------   --------------- ------------------------
DROP TABLE bla  tempdb          Denis-PCDenis   2011-12-12 20:47:23.607
DROP TABLE bla  model           Denis-PCDenis   2011-12-12 20:47:23.780

Also take a look at my post Use DDL Triggers To Track Table Changes to learn how you can capture all the DDL changes that were applied to a table over time….powerful stuff!

I just scratched the surface with what you can do with DDL triggers, below is a list of all the events that you can create your trigger for.

DDL Statements That Have Server or Database Scope

DDL Events DDL Events DDL Events

CREATE_APPLICATION_ROLE (Applies to the 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 the ALTER APPLICATION ROLE statement and sp_approlepassword.)

DROP_APPLICATION_ROLE (Applies to the DROP APPLICATION ROLE statement and sp_dropapprole.)

CREATE_ASSEMBLY

ALTER_ASSEMBLY

DROP_ASSEMBLY

CREATE_ASYMMETRIC_KEY

ALTER_ASYMMETRIC_KEY

DROP_ASYMMETRIC_KEY

ALTER_AUTHORIZATION

ALTER_AUTHORIZATION_DATABASE (Applies to the ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.)

CREATE_BROKER_PRIORITY

ALTER_BROKER_PRIORITY

DROP_BROKER_PRIORITY

CREATE_CERTIFICATE

ALTER_CERTIFICATE

DROP_CERTIFICATE

CREATE_CONTRACT

DROP_CONTRACT

ADD_COUNTER_SIGNATURE

DROP_COUNTER_SIGNATURE

CREATE_CREDENTIAL

ALTER_CREDENTIAL

DROP_CREDENTIAL

GRANT_DATABASE

DENY_DATABASE

REVOKE_DATABASE

CREATE_DATABASE_AUDIT_SPEFICIATION

ALTER_DATABASE_AUDIT_SPEFICIATION

DENY_DATABASE_AUDIT_SPEFICIATION

CREATE_DATABASE_ENCRYPTION_KEY

ALTER_DATABASE_ENCRYPTION_KEY

DROP_DATABASE_ENCRYPTION_KEY

CREATE_DEFAULT

DROP_DEFAULT

BIND_DEFAULT (Applies to sp_bindefault.)

UNBIND_DEFAULT (Applies to sp_unbindefault.)

CREATE_EVENT_NOTIFICATION

DROP_EVENT_NOTIFICATION

CREATE_EXTENDED_PROPERTY (Applies to sp_addextendedproperty.)

ALTER_EXTENDED_PROPERTY (Applies to sp_updateextendedproperty.)

DROP_EXTENDED_PROPERTY (Applies to sp_dropextendedproperty.)

CREATE_FULLTEXT_CATALOG (Applies to the CREATE FULLTEXT CATALOG statement and sp_fulltextcatalog when create is specified.)

ALTER_FULLTEXT_CATALOG (Applies to the ALTER FULLTEXT CATALOG statement, sp_fulltextcatalog when start_incremental, start_full, Stop, or Rebuild is specified, and sp_fulltext_database when enable is specified.)

DROP_FULLTEXT_CATALOG (Applies to the DROP FULLTEXT CATALOG statement and sp_fulltextcatalog when drop is specified.)

CREATE_FULLTEXT_INDEX (Applies to the CREATE FULLTEXT INDEX statement and sp_fulltexttable when create is specified.)

ALTER_FULLTEXT_INDEX (Applies to the ALTER FULLTEXT INDEX statement, sp_fulltextcatalog when start_full, start_incremental, or stop is specified, sp_fulltext_column, and sp_fulltext_table when any action other than create or drop is specified.)

DROP_FULLTEXT_INDEX (Applies to the DROP FULLTEXT INDEX statement and sp_fulltexttable when drop is specified.)

CREATE_FULLTEXT_STOPLIST

ALTER_FULLTEXT_STOPLIST

DROP_FULLTEXT_STOPLIST

CREATE_FUNCTION

ALTER_FUNCTION

DROP_FUNCTION

CREATE_INDEX

ALTER_INDEX (Applies to the ALTER INDEX statement and sp_indexoption.)

DROP_INDEX

CREATE_MASTER_KEY

ALTER_MASTER_KEY

DROP_MASTER_KEY

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_PLAN_GUIDE (Applies to sp_create_plan_guide.)

ALTER_PLAN_GUIDE (Applies to sp_control_plan_guide when ENABLE, ENABLE ALL, DISABLE, or DISABLE ALL is specified.)

DROP_PLAN_GUIDE (Applies to sp_control_plan_guide when DROP or DROP ALL is specified.)

CREATE_PROCEDURE

ALTER_PROCEDURE (Applies to the ALTER PROCEDURE statement and sp_procoption.)

DROP_PROCEDURE

CREATE_QUEUE

ALTER_QUEUE

DROP_QUEUE

CREATE_REMOTE_SERVICE_BINDING

ALTER_REMOTE_SERVICE_BINDING

DROP_REMOTE_SERVICE_BINDING

CREATE_SPATIAL_INDEX

RENAME (Applies to sp_rename)

CREATE_ROLE (Applies to the CREATE ROLE statement, sp_addrole, and sp_addgroup.)

ALTER_ROLE

DROP_ROLE (Applies to the DROP ROLE statement, sp_droprole, and sp_dropgroup.)

ADD_ROLE_MEMBER

DROP_ROLE_MEMBER

CREATE_ROUTE

ALTER_ROUTE

DROP_ROUTE

CREATE_RULE

DROP_RULE

BIND_RULE (Applies to sp_bindrule.)

UNBIND_RULE (Applies to sp_unbindrule.)

CREATE_SCHEMA (Applies to the CREATE SCHEMA statement, sp_addrole, sp_adduser, sp_addgroup, and sp_grantdbaccess.)

ALTER_SCHEMA (Applies to the ALTER SCHEMA statement and sp_changeobjectowner.)

DROP_SCHEMA

CREATE_SERVICE

ALTER_SERVICE

DROP_SERVICE

ALTER_SERVICE_MASTER_KEY

BACKUP_SERVICE_MASTER_KEY

RESTORE_SERVICE_MASTER_KEY

ADD_SIGNATURE

DROP_SIGNATURE

CREATE_SPATIAL_INDEX

ALTER_INDEX can be used for spatial indexes.

DROP_INDEX can be used for spatial indexes.

CREATE_STATISTICS

DROP_STATISTICS

UPDATE_STATISTICS

CREATE_SYMMETRIC_KEY

ALTER_SYMMETRIC_KEY

DROP_SYMMETRIC_KEY

CREATE_SYNONYM

DROP_SYNONYM

CREATE_TABLE

ALTER_TABLE (Applies to the ALTER TABLE statement and sp_tableoption.)

DROP_TABLE

CREATE_TRIGGER

ALTER_TRIGGER (Applies to the ALTER TRIGGER statement and sp_settriggerorder.)

DROP_TRIGGER

CREATE_TYPE (Applies to the CREATE TYPE statement and sp_addtype.)

DROP_TYPE (Applies to the DROP TYPE statement and sp_droptype.)

CREATE_USER (Applies to the CREATE USER statement, sp_adduser, and sp_grantdbaccess.)

ALTER_USER (Applies to ALTER USER statement and sp_change_users_login.)

DROP_USER (Applies to the DROP USER statement, sp_dropuser, and sp_revokedbaccess.)

CREATE_VIEW

ALTER_VIEW

DROP_VIEW

CREATE_XML_INDEX

ALTER_INDEX can be used for XML indexes.

DROP_INDEX can be used for XML indexes.

CREATE_XML_SCHEMA_COLLECTION

ALTER_XML_SCHEMA_COLLECTION

DROP_XML_SCHEMA_COLLECTION

DDL Statements That Have Server Scope

DDL Events DDL Events DDL Events

ALTER_AUTHORIZATION_SERVER

ALTER_SERVER_CONFIGURATION

ALTER_INSTANCE (Applies to sp_configure and sp_addserver when a local server instance is specified.)

CREATE_CREDENTIAL

ALTER_CREDENTIAL

DROP_CREDENTIAL

CREATE_CRYPTOGRAPHIC_PROVIDER

ALTER_CRYPTOGRAPHIC_PROVIDER

DROP_CRYPTOGRAPHIC_PROVIDER

CREATE_DATABASE

ALTER_DATABASE (Applies to the ALTER DATABASE statement and sp_fulltext_database.)

DROP_DATABASE

CREATE_ENDPOINT

ALTER_ENDPOINT

DROP_ENDPOINT

CREATE_EVENT_SESSION

ALTER_EVENT_SESSION

DROP_EVENT_SESSION

CREATE_EXTENDED_PROCEDURE (Applies to sp_addextendedproc.)

DROP_EXTENDED_PROCEDURE (Applies to sp_dropextendedproc.)

CREATE_LINKED_SERVER (Applies to sp_addlinkedserver.)

ALTER_LINKED_SERVER (Applies to sp_serveroption.)

DROP_LINKED_SERVER (Applies to sp_dropserver when a linked server is specified.)

CREATE_LINKED_SERVER_LOGIN (Applies to sp_addlinkedsrvlogin.)

DROP_LINKED_SERVER_LOGIN (Applies to sp_droplinkedsrvlogin.)

CREATE_LOGIN (Applies to the 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 the ALTER LOGIN statement, sp_defaultdb, sp_defaultlanguage, sp_password, and sp_change_users_login when Auto_Fix is specified.)

DROP_LOGIN (Applies to the DROP LOGIN statement, sp_droplogin, sp_revokelogin, and xp_revokelogin.)

CREATE_MESSAGE (Applies to sp_addmessage.)

ALTER_MESSAGE (Applies to sp_altermessage.)

DROP_MESSAGE (Applies to sp_dropmessage.)

CREATE_REMOTE_SERVER (Applies to sp_addserver.)

ALTER_REMOTE_SERVER (Applies to sp_setnetname.)

DROP_REMOTE_SERVER (Applies to sp_dropserver when a remote server is specified.)

GRANT_SERVER

DENY_SERVER

REVOKE_SERVER

ADD_SERVER_ROLE_MEMBER

DROP_SERVER_ROLE_MEMBER

CREATE_SERVER_AUDIT

ALTER_SERVER_AUDIT

DROP_SERVER_AUDIT

CREATE_SERVER_AUDIT_SPECIFICATION

ALTER_SERVER_AUDIT_SPECIFICATION

DROP_SERVER_AUDIT_SPECIFICATION

CREATE_RESOURCE_POOL

ALTER_RESOURCE_POOL

DROP_RESOURCE_POOL

CREATE_WORKLOAD_GROUP

CREATE_WORKLOAD_GROUP

CREATE_WORKLOAD_GROUP

Come back tomorrow for the next post in this series