Various Triggers – Part 5

Another usage of Database Triggers is track Data Definition Language (DDL) changes that occur in a database. Today, I am going to enhance the AUTOS database script to add such auditing. This auditing is a very good practice when multiple people have syadmin rights to make such changes.

First, I am going to create a schema called Audit Database Tracking (ADT) to seperate the data tables from audit tracking tables.

-- Delete existing schema.
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'ADT')
DROP SCHEMA [ADT];
GO

-- Create a ADT schema
CREATE SCHEMA [ADT] AUTHORIZATION dbo;
GO

Second, I am going to create a table to hold the keep track of the DDL changes that are occuring to the database.

/*  
	Create schema level auditing - table.
*/

-- Remove table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
    OBJECT_ID(N'[ADT].[LOG_DDL_CHANGES]') AND type in (N'U'))
DROP TABLE [ADT].[LOG_DDL_CHANGES]
GO

-- Create a DDL Log Changes table
CREATE TABLE [ADT].[LOG_DDL_CHANGES]
(
	[ChangeId] BIGINT IDENTITY(1,1) NOT NULL,
	[ChangeDate] [datetime] NOT NULL,
	[ChangeType] [varchar](20) NOT NULL,
	[ChangeBy] [nvarchar](256) NOT NULL,
	[AppName] [nvarchar](128) NOT NULL,
	[HostName] [nvarchar](128) NOT NULL,
	[SchemaName] [sysname] NULL,
	[ObjectName] [sysname] NULL,
	[Tsql] nvarchar(MAX) NULL,

        CONSTRAINT [pk_Ldc_ChangeId] PRIMARY KEY CLUSTERED ([ChangeId] ASC)
) 
GO

-- Add defaults for key information
ALTER TABLE [ADT].[LOG_DDL_CHANGES] 
    ADD CONSTRAINT [df_Ldc_ChangeDate] DEFAULT (getdate()) FOR [ChangeDate];

ALTER TABLE [ADT].[LOG_DDL_CHANGES] 
    ADD CONSTRAINT [df_Ldc_ChangeType] DEFAULT ('') FOR [ChangeType];

ALTER TABLE [ADT].[LOG_DDL_CHANGES] 
    ADD CONSTRAINT [df_Ldc_ChangeBy] DEFAULT (coalesce(suser_sname(),'?')) FOR [ChangeBy];

ALTER TABLE [ADT].[LOG_DDL_CHANGES] 
    ADD CONSTRAINT [df_Ldc_AppName] DEFAULT (coalesce(APP_NAME(),'?')) FOR [AppName];

ALTER TABLE [ADT].[LOG_DDL_CHANGES] 
    ADD CONSTRAINT [df_Ldc_HostName] DEFAULT (coalesce(HOST_NAME(),'?')) FOR [HostName];
GO

Third, I am going to create a database trigger to capture the event data and add entries to the tracking table.

/*  
	Create schema level auditing - trigger.
*/

-- Remove trigger if it exists
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[TRG_TRACK_DDL_CHANGES]'))
DROP TRIGGER [TRG_TRACK_DDL_CHANGES]
GO

-- Add the trigger to the db
CREATE TRIGGER [TRG_TRACK_DDL_CHANGES] ON DATABASE 
FOR 
  DDL_TRIGGER_EVENTS, DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_DATABASE_SECURITY_EVENTS 
AS 
BEGIN

    -- Declare local variables
    DECLARE @VAR_DATA XML;
    DECLARE @VAR_SCHEMA sysname;
    DECLARE @VAR_OBJECT sysname; 
    DECLARE @VAR_EVENT_TYPE sysname;

    -- Set local variables
    SET @VAR_DATA = EVENTDATA();
    SET @VAR_EVENT_TYPE = @VAR_DATA.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @VAR_SCHEMA = @VAR_DATA.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @VAR_OBJECT = @VAR_DATA.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') 

    -- Do not track replication or statistics
    IF (( @VAR_EVENT_TYPE <> 'CREATE_STATISTICS') and ( @VAR_OBJECT not like 'syncobj_0x%')) 
        BEGIN
		INSERT [ADT].[LOG_DDL_CHANGES] ([ObjectName], [ChangeType], [Tsql], [SchemaName]) VALUES 
		( 
		    CONVERT(sysname, @VAR_OBJECT), 
		    @VAR_EVENT_TYPE, 
		    @VAR_DATA.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 
		    CONVERT(sysname, @VAR_SCHEMA)
		 );
        END

END;

The EVENTDATA() function, only available for Server and Database DDL triggers, returns XML data is key to extracting information about the event. The FOR clause of the database trigger can narrow down what events you want to track. Last but not least, a database developer needs to be aware of other events such as replication that might not wanted to be tracked.

The image below shows what is captured in the audit table after a couple tables, triggers and stored procedures are created.

In summary, database triggers (AFTER) can be used to keep track of SCHEMA changes. Since the number of SCHEMA changes on a mature database are small, this is a great way to find out who did what when there is a production issue.

In SQL Server 2008, a built in Database Auditing mechanism was introduced. We will explore this feature in future articles.