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
| Server Scope | Database Scope |
| ALTER_AUTHORIZATION_SERVER CREATE_DATABASE ALTER_DATABASE DROP_DATABASE CREATE_ENDPOINT DROP_ENDPOINT CREATE_LOGIN ALTER_LOGIN DROP_LOGIN GRANT_SERVER DENY_SERVER REVOKE_SERVER |
CREATE_APPLICATION_ROLE ALTER_APPLICATION_ROLE DROP_APPLICATION_ROLE CREATE_ASSEMBLY ALTER_ASSEMBLY DROP_ASSEMBLY ALTER_AUTHORIZATION_DATABASE CREATE_CERTIFICATE ALTER_CERTIFICATE DROP_CERTIFICATE CREATE_CONTRACT DROP_CONTRACT GRANT_DATABASE DENY_DATABASE REVOKE_DATABASE CREATE_EVENT_NOTIFICATION DROP_EVENT_NOTIFICATION CREATE_FUNCTION ALTER_FUNCTION DROP_FUNCTION CREATE_INDEX ALTER_INDEX DROP_INDEX CREATE_MESSAGE_TYPE ALTER_MESSAGE_TYPE DROP_MESSAGE_TYPE CREATE_PARTITION_FUNCTION ALTER_PARTITION_FUNCTION DROP_PARTITION_FUNCTION CREATE_PARTITION_SCHEME ALTER_PARTITION_SCHEME DROP_PARTITION_SCHEME CREATE_PROCEDURE ALTER_PROCEDURE DROP_PROCEDURE CREATE_QUEUE ALTER_QUEUE DROP_QUEUE CREATE_REMOTE_SERVICE_BINDING ALTER_REMOTE_SERVICE_BINDING DROP_REMOTE_SERVICE_BINDING CREATE_ROLE ALTER_ROLE DROP_ROLE CREATE_ROUTE ALTER_ROUTE DROP_ROUTE CREATE_SCHEMA ALTER_SCHEMA DROP_SCHEMA CREATE_SERVICE ALTER_SERVICE DROP_SERVICE CREATE_STATISTICS DROP_STATISTICS UPDATE_STATISTICS CREATE_SYNONYM DROP_SYNONYM CREATE_TABLE ALTER_TABLE DROP_TABLE CREATE_TRIGGER ALTER_TRIGGER DROP_TRIGGER CREATE_TYPE DROP_TYPE CREATE_USER ALTER_USER DROP_USER CREATE_VIEW ALTER_VIEW DROP_VIEW CREATE_XML_SCHEMA_COLLECTION ALTER_XML_SCHEMA_COLLECTION DROP_XML_SCHEMA_COLLECTION |
