Monitoring Changes to your Database Schema
June 28, 2007
How can you monitor changes to your Database Schema? Why would you want to track changes to your database structure? You might want to track changes to your database data definitions for a number of different reasons. With the introduction of SQL Server 2005, Microsoft now allows you to write Data Definition Language (DDL) triggers to perform actions when events occur on your server. In this article, I will discuss some of the things that DDL triggers could be use for, as well as show you a few DDL trigger examples.
Why Might You Want to Create a DDL TRIGGER?
So why would you want to track changes to the underlying structure of a database? The reasons for doing this are probably different in every SQL Server 2005 environment. Here is a list of a few possible situations where DDL triggers might be useful:
This is by no means a complete list of the reasons you would implement a DDL trigger. But at least these bullet points give you some ideas on how a DDL trigger might be used to better manage your SQL Server 2005 environment.
Building and Implementing a DDL Trigger
Before I talk about how to create a DDL trigger, I need to discuss the events that fire DDL triggers and the scope of these events. An event is just the execution of a TSQL command. Each event has a name. The event name for a command is the TSQL command where underscores replace spaces within the command. Therefore, the CREATE_TABLE event would correspond to the CREATE TABLE command. And the DROP_LOGIN event would corresponds to the DROP LOGIN command. For a complete list of events, refer to Books Online. Events can also be referred to by a group name. A group name is a name that is used to refer to a number of different individual events by a single name reference. So, the group name of DDL_TABLE_EVENTS refers to the CREATE_TABLE, ALTER_TABLE and DELETE_TABLE" events. Each event or event group has a specific scope, which can be either server or database scope. The server scope events are for commands that happen at the server level, like CREATE DATABASE or CREATE LOGIN; whereas database commands like CREATE TABLE are database scope events.
Creating a DDL trigger is similar to creating a DML trigger, meaning the TSQL command starts with CREATE TRIGGER, but then your CREATE TRIGGER statement is followed by the scope (ON DATABASE, or ON ALL SERVER), and the different events you would like the trigger to fire on, lastly you include the actual trigger specification. If your trigger is to fire for database scope events then you create the trigger in the database in which you want to track events. Server lever scope triggers can be created in any database, but the triggers are placed in the Triggers item within the Server Object folder within Management Studio. DDL Triggers are fired after the command is executed. This means, if want to restrict a particular command from being executed you will need to ROLLBACK the transaction from within your trigger. You cannot use DDL triggers as an INSTEAD OF TRIGGER.
To get a sense for how all the events and the scope of DDL trigger works I will go through a few examples. For this first example, I will create a DDL trigger that will restrict anyone from trying to create, alter or delete a store procedure in the AdventureWorks database:
CREATE TRIGGER trg_Restrict_Proc_Changes ON DATABASE FOR CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE AS RAISERROR ('Trigger trg_Restrict_Proc_Changes restricts CREATE, DROP, or ALTER commands!',10, 1) ROLLBACK
I created this DDL trigger so it will fire when a CREATE, ALTER or DELETE stored procedure command is executed. This trigger uses the following three events to accomplish this: CREATE_PROCEDURE, DROP_PROCEDURE and ALTER_PROCEDURE. With this trigger implemented, if someone should try to create a new procedure, or drop/alter an existing procedure then this trigger would perform a RAISERROR instead of creating, dropping or altering the stored procedure. Each time this trigger is fired, it displays an error message explaining that this trigger is disabling you from creating, dropping or altering a stored procedure. The ROLLBACK command is executed so the actual CREATE, DROP, or ALTER stored procedure which was executed prior to the trigger being fired is not committed.
The same functionality of the above trigger could have been accomplished by specifying a single event group, instead of the three events I used above. This is accomplished by changing the FOR criteria above with the DDL_PROCEDURE_EVENTS event group, like so:
CREATE TRIGGER trg_Restrict_Proc_Changes ON DATABASE FOR DDL_PROCEDURE_EVENTS AS RAISERROR ('Trigger trg_Restrict_Proc_Changes restricts CREATE, DROP, or ALTER commands!',10, 1) ROLLBACK
There are a number of different groupings for DDL trigger events. To find out more about all the different levels of groupings search Books Online for the topic Groups for Use with DDL Triggers.
Another use for a DDL trigger is to maintain a table that monitors all changes to your database. This way if your application stops working for some reason, you will know who and when changes where last made to your database. Below is a trigger that accomplishes this kind of monitoring:
USE AdventureWorks GO CREATE TRIGGER trgMonitorChange ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS set nocount on declare @EventType varchar(100) declare @SchemaName varchar(100) declare @ObjectName varchar(100) declare @ObjectType varchar(100) SELECT @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)','nvarchar(max)') ,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)','nvarchar(max)') ,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)','nvarchar(max)') ,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)','nvarchar(max)') -- Is the default schema used if @SchemaName = ' ' select @SchemaName = default_schema_name from sys.sysusers u join sys.database_principals p on u.uid = p.principal_id where u.name = CURRENT_USER insert into tblMonitorChange select @EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN()
This DDL trigger uses the DDL_DATABASE_LEVEL_EVENTS event to identify when this trigger should fire. This event will fire whenever any CREATE, ALTER, or DELETE command is executed against any object in the AdventureWorks database. This trigger makes use of a series of EVENTDATA() function calls to identify information about which object, and event was used to modify the database. Once all the pertinent data is gathered this trigger then inserted the collected data into the tblMonitorChange table. Here is the CREATE TABLE statement for my tblMonitorChange table used by this trigger:
USE AdventureWorks GO CREATE TABLE [dbo].[tblMonitorChange]( [EventType] [varchar](100) NULL, [SchemaName] [varchar](100) NULL, [ObjectName] [varchar](100) NULL, [ObjectType] [varchar](100) NULL, [EventDate] [datetime] NULL, [SystemUser] [varchar](100) NULL, [CurrentUser] [varchar](100) NULL, [OriginalUser] [varchar](100) NULL)
The EVENTDATA function used in the above trigger returns XML data. In my trigger I used the VALUE method of XQUERY to return just the data I needed from the XML data for each column value I want to store in my auditing table, like EventType, SchemaName, ObjectName, and ObjectType. For more information on how to use the EVENTDATE function, refer to SQL Server BOOKS Online.
Suppose you want to know when a new database is created via email. Below I have provided a trigger that will accomplish this:
CREATE TRIGGER trgMonitorNewDB ON ALL SERVER FOR CREATE_DATABASE as DECLARE @DBNAME varchar(200) DECLARE @BODY varchar(1000) SELECT @DBNAME = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)','nvarchar(max)') SET @BODY = 'Server = ' + @@SERVERNAME + CHAR(10) + CHAR(13) + 'New Database Named = ' + @DBNAME + CHAR(10) + CHAR(13) + 'Created By = ' + ORIGINAL_LOGIN() + CHAR(10) + CHAR(13) + 'Date Created = ' + cast(getdate() as CHAR) EXEC msdb.dbo.sp_send_dbmail @email@example.com', @subject = 'New Database Created', @body = @BODY
This trigger is an example of a server scope trigger. Notice I specified ALL SERVER for the ON clause of the CREATE TRIGGER statement above. Here I have used the EVENTDATA function to get the database name that was created, formatted the @BODY variable with the details about the database created, and then I used the database mail sp_send_dbmail stored procedure to send me an email. This trigger will be fired anytime a database is created on my server. This trigger helps inform me what servers are getting new databases and who created them immediately following the CREATE DATABASE command that creates any new database. This information allows me to be on top of all new databases being built in my environment.
To delete a trigger you need to specify DELETE TRIGGER <trigger name> just like you would when deleting a normal trigger, but you must follow it with either an ON DATABASE or ON ALL SERVER clause. If you dont have this extra ON clause your delete will fail. So to delete the above two triggers you would run the following script:
USE AdventureWorks GO DROP TRIGGER trg_Restrict_Proc_Changes ON DATABASE DROP TRIGGER trgMonitorChange ON DATABASE DROP TRIGGER trgMonitorNewDB ON ALL SERVER
DDL triggers are new with SQL Server 2005. With this new feature, you now have a way to fire a trigger anytime some DDL event is executed on your server. DDL triggers now allow you the capability to build processes that will help you better manage and monitor changes within your SQL Server environment. So next time you need to monitor, deny, or perform some action when a DDL event occurs, consider whether or not a DDL trigger would simplify your requirements.