Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Monitoring Changes in Your Database Using DDL Triggers

By David Dye,

Introduction

Additions, deletions, or changes to objects in a database can cause a great deal of hardship and require a dba or developer to rewrite existing code that may reference affected entities. To make matters worse tracking down the problematic alteration(s) may be synonymous to locating the needle in the haystack. Utilizing a DDL trigger in conjunction with a single user created table, used to document such changes, can considerably minimize the headaches involved in tracking and locating schema changes.

Creating the Table and DDL TRIGGER

The first step in implementing such a tracking strategy is to create a table that will be used to record all DDL actions fired from within a database. The below code creates a table in the AdventureWorks sample database that will be used to hold all such DDL actions:

USE AdventureWorks
GO
CREATE TABLE AuditLog
(ID        INT PRIMARY KEY IDENTITY(1,1),
Command    NVARCHAR(1000),
PostTime   NVARCHAR(24),
HostName   NVARCHAR(100),
LoginName  NVARCHAR(100)
)
GO

After creating the table to hold our DDL events it is now time to create a DDL trigger that will be specific to the AdventureWorks database and will fire on all DDL_DATABASE_LEVEL_EVENTS:

CREATE TRIGGER Audit ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
DECLARE @cmd NVARCHAR(1000)
DECLARE @posttime NVARCHAR(24)
DECLARE @spid NVARCHAR(6)
DECLARE @loginname NVARCHAR(100)
DECLARE @hostname NVARCHAR(100)
SET @data = EVENTDATA()
SET @cmd = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(1000)')
SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))
SET @posttime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'NVARCHAR(24)')
SET @spid = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(6)')
SET @loginname = @data.value('(/EVENT_INSTANCE/LoginName)[1]',
    'NVARCHAR(100)')
SET @hostname = HOST_NAME()
INSERT INTO dbo.AuditLog(Command, PostTime,HostName,LoginName)
 VALUES(@cmd, @posttime, @hostname, @loginname)
GO

The purpose of the trigger is to capture the EVENTDATA() that is created once the trigger fires and parse the data from the xml variable inserting it into the appropriate columns of our AuditLog table. The parsing of the EVENTDATA() is rather straight forward, except for when extracting the command text. The parsing of the command text includes the following code:

SET@cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))

The need for the LTRIM and RTRIM is to strip all leading and trailing white space while the REPLACE is used to remove the carriage return that is added when if using the scripting wizard from SSMS. This will provide the future ability to use SSRS string functions to further parse the command text to offer greater detail.

Once the table and trigger have been created you can test to assure that it is working properly:

UPDATE STATISTICS Production.Product
GO
CREATE TABLE dbo.Test(col INT)
GO
DROP TABLE dbo.Test
GO
-- View log table
SELECT *
FROM dbo.AuditLog
GO

The results of the above query should are shown below:

Results

Conclusions

By creating a table to hold all DDL actions and a database level DDL trigger we can successfully capture all DDL level changes to our database and provide greater ability to track and monitor any such change.

As performance of any such action(s) is most often the deciding factor as to whether implement such change control, I have limited excessive parsing or formatting in the above trigger. Consider this the first step, documenting. Later I will post how to utilize reporting services to provide reports showing:

1. DDL action, CREATE, ALTER, DELETE, etc

2. The schema and object affected

3. Workstation executing DDL statements

4. Drill down report to show object dependencies

That will use the documenting objects created above to provide greater insight and detail external of your production environment.

Total article views: 8794 | Views in the last 30 days: 9
 
Related Articles
FORUM

How notificate create database event

How notificate create database event

FORUM

How can I use a database event

Database event instead of trigger

FORUM

Error creating a logon trigger

Error creating a logon trigger

FORUM

create Trigger auto.

create Trigger auto.

FORUM

create trigger

create a trigger on SQL Server that will cause an email to be sent to my alias when a database is cr...

Tags
auditing    
ddl triggers    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones