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:
- You work in an environment where many developers can create new databases, and you would like to get an email notification when each new database is created. This notification then can trigger a discussion with the developers to make sure the appropriate backup and recovery solutions are designed and implemented for the newly created database.
- You work on a large design team that is building a database that contains many objects, and you need to track the name of the developers that created, altered and/or deleted an object in a database, as well as the time in which the object was changed.
- You need to maintain some strict database standards. So to ensure no objects are created that don’t meet your standards you implement a DDL trigger to not allow anything to be created in your environment that doesn’t meet your standards.
- Say your application all of a sudden stops working for some reason. If you knew what has recently changed to your database schema, you could then develop a method to back out the change, or quickly make the necessary modification to resolve the issue that the database change has introduced.
- You need to ensure that you collect the appropriate metadata (data steward, application owner, programmer contact, backup requirements, etc.) regarding each new database prior to them being implemented into production. Therefore, you build a DDL trigger to make sure your metadata tables are populated prior to allowing a new production database to be created.
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)[1]’,’nvarchar(max)’)
,@SchemaName = EVENTDATA().value(‘(/EVENT_INSTANCE/SchemaName)[1]’,’nvarchar(max)’)
,@ObjectName = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]’,’nvarchar(max)’)
,@ObjectType = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectType)[1]’,’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)[1]’,’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 @recipients=’glarsen@jupitermedia.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 don’t 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
Conclusion
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.
