all bits considered

Scenario: several developers are hard at work cranking out code. The application under development relies on RDBMS back-end for persistent storage (in this particular case, the database is Microsoft SQL Server 2005, but the technique described applies to any RDBMS supporting DDL triggers). Developers are making changes to the client application code, creating/altering/dropping database objects (stored procedures, tables, views etc.) and, in the heat of the moment, forgetting to communicate the changes to their teammates left alone the project manager…

Yes, I know – this is not how it supposed to happen, and yet in the world out there, more often than not, it does happen… Here are some do-it-yourself ideas on how you could alleviate the pain and spare you some nasty surprises without buying more tools…

Enter DDL Triggers. This is relatively new feature with Microsoft SQL Server (though Oracle had them for ages), and, among many other things (rolling back changes, for instance), it could be used to solve the problem stated above.

A DDL (Data Definition Language) trigger in MS SQL Server can have two scopes – server and database. The Table 1.1 at the end of this post lists all the events for which DDL trigger could be created, grouped by scope. For the full syntax in creating a DDL trigger please see vendor’s documentation; here I will only touch basics needed to illustrate a solution.

Here’s a database scop trigger we are going to use to monitor events:

CREATE TRIGGER [tr_DDL_ALERT] ON DATABASE —- trigger is created in context of a given database
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE    —- which events to capture; see Table 1.1 for full list
AS         —- use DDL_DATABASE_LEVEL_EVENTS captures all DB events
SET NOCOUNT ON
DECLARE @xmlEventData XML —- the generated event data is in XML format
SET @xmlEventData = eventdata() —- get data from the EVENTDATA() function

Now, this trigger would not be much of use to anybody; you need to parse information contained in the XML message passed into your trigger upon the event. You could parse it and send an email message, or you could save it into a database, or both.

The following code saves it into a table [tbDDL_ALERT] – which, of course, has to be created beforehand:

INSERT INTO dbo.tbDDLEventLog
(
EventTime
,EventType
,ServerName
,DatabaseName
,ObjectType
,ObjectName
,UserName
,CommandText
)

SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query(‘data(/EVENT_INSTANCE/PostTime)’)),’T‘, ‘ ‘)
,CONVERT(VARCHAR(100), @xmlEventData.query(‘data(/EVENT_INSTANCE/EventType)‘))
,CONVERT(VARCHAR(100), @xmlEventData.query(‘data(/EVENT_INSTANCE/ServerName)‘))
,CONVERT(VARCHAR(100), @xmlEventData.query(‘data(/EVENT_INSTANCE/DatabaseName)‘))
,CONVERT(VARCHAR(100), @xmlEventData.query(‘data(/EVENT_INSTANCE/ObjectType)‘))
,CONVERT(VARCHAR(100), @xmlEventData.query(‘data(/EVENT_INSTANCE/ObjectName)‘))
,CONVERT(VARCHAR(100), @xmlEventData.query(‘data(/EVENT_INSTANCE/UserName)‘))
,CONVERT(VARCHAR(MAX), @xmlEventData.query(‘data(/EVENT_INSTANCE/TSQLCommand/CommandText)‘))

And sends out email notifications using potentially obsolete extended stored procedure (assemble message (@body variable) from the elements of the XML message as shown in the example above):

EXEC master..xp_smtp_sendmail
@TO = ‘me@somewhere.com‘
,@from = ‘someone@somewhere.com‘
,@message = @body
,@subject = ‘database was modified‘
,@server = ‘smtp.mydomain.com’

Long-term solution would be, of course, configuring SQL Server Database Mail.

In my next post I will describe how database triggers could be integrated with Hudson - an open source Continuous Integration (CI) server.

Table 1. List of the values to use with server and database scope DDL triggers