Click here to Skip to main content
Click here to Skip to main content
Go to top

Monitoring and Recording DDL changes in SQL SERVER 2005

, 11 Mar 2008
Rate this:
Please Sign up or sign in to vote.
Monitoring and Recording DDL changes in SQL SERVER 2005

Introduction

While executing the IT project of any significant size, auditing any structural change during the course of project is important. Project manager handling the team size varying from 5 developer to 50 developer cannot do all the activities. Another challenge is that client does not agree to hire a dedicated DBA in case of project having team size less than 10 assuming there is no major activity to be performed. So that case developer would be acting as front end developer, middleware operation and also performing database operation.

Background

SQL Server 2005 has extended the trigger functionality you normally use with data manipulation language DML, command such as insert, update and delete to incorporate Data Definition Language (DDL) commands like CREATE Database, Drop Table, Alter Table. The new technology comes with two flavors

a. Server-level triggers (respond to changes on server)

b. Database-level triggers (specific to a given database changes)

Defining the DDL Trigger

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name 
ON { ALL SERVER | DATABASE } 
[ WITH <ddl_trigger_option /> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option /> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier /> ::=
    assembly_name.class_name.method_name

When you define a DDL trigger, you must decide on the scope of your trigger. Scope determines whether the trigger executes at the database or the server level. Trigger scopping : In the following example, DDL trigger safety will fire whenever a DROP TABLE or ALTER TABLE event occurs in the database

CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
   PRINT 'You must disable Trigger "safety" to drop or alter tables!' 
   ROLLBACK
;

The EVENTDATA() function returns XML data with information such as event time, System Process ID (SPID), and type of event firing the trigger. A DDL trigger uses the EVENTDATA() function to determine how it must respond. The following is a sample of XML data returned by the EVENTDATA() function:

Implementing Database scope DDL Trigger

The objective of implementing the database ddl trigger is to trap the changes. So the first objective is to create a table which can hold the data about changes happening the database with date and time.

Table structure would be as follows

CREATE TABLE [dbo].[DatabaseLog](
    [DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
    [PostTime] [datetime] NOT NULL,
    [DatabaseUser] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Event] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Schema] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Object] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TSQL] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [XmlEvent] [xml] NOT NULL,
 CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED 
(
    [DatabaseLogID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

Creating database level trigger to log the records in the above table

    
CREATE TRIGGER [ddlDatabaseTriggerLog] 
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;

    SET @data = EVENTDATA();
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') 

    IF @object IS NOT NULL
        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;
    ELSE
        PRINT '  ' + @eventType + ' - ' + @schema;

    IF @eventType IS NULL
        PRINT CONVERT(nvarchar(max), @data);

    INSERT [dbo].[DatabaseLog] 
        (
        [PostTime], 
        [DatabaseUser], 
        [Event], 
        [Schema], 
        [Object], 
        [TSQL], 
        [XmlEvent]
        ) 
    VALUES 
        (
        GETDATE(), 
        CONVERT(sysname, CURRENT_USER), 
        @eventType, 
        CONVERT(sysname, @schema), 
        CONVERT(sysname, @object), 
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 
        @data
        );
END;

GO

Points of Interest

You will get a table with all changes recorded and that can be used as vital audit information at any level of project.

a. If you create manually using the sql management studio it will work
b. If you run the script for database table alter, drop or create it will work
c. If you change the constraint it would log 
d. if you break indexes or create them it will log 


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Lalit Sharma
Architect Cybage Software Private Ltd
India India
No Biography provided

Comments and Discussions

 
GeneralQuite interesting but... PinmemberJ.F.26-Sep-08 14:48 
Generalnice work Pinmemberhex7050328-Mar-08 12:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140926.1 | Last Updated 12 Mar 2008
Article Copyright 2008 by Lalit Sharma
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid