db trigger to force versioning in ddl statements on sql server 2005 / 2008

This trigger demonstrates the enforcing of the someValue xml element in the sql command to be executed on a sql server 2005 / 2008
Thus one could modigy it to add more enforcements: for example :
I had to change the proc since ...

it also keeps track on all the sql commands and saves them into a history log table, so one could digg out the older version and rerun them straight away .

I would be glad to receive and improvement suggestions - how-to keep track on the versions , version rolling etc. Enjoy !

USE [GA_DEV]
GO

/****** Object:  DdlTrigger [TraceDbChanges]    Script Date: 05/07/2009 12:57:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




create trigger [TraceDbChanges]
on database
for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function , 
create_trigger , alter_trigger , drop_trigger 
as

set nocount on

declare @data xml
set @data = EVENTDATA()
declare @DbVersion varchar(20)
set @DbVersion =(select ga.GetDbVersion())
declare @DbType varchar(20)
set @DbType =(select ga.GetDbType())
declare @DbName varchar(256)
set @DbName =@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
declare @EventType varchar(256)
set @EventType =@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')
declare @ObjectName varchar(256)
set @ObjectName  = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
declare @ObjectType varchar(25)
set @ObjectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)')
declare @TSQLCommand varchar(max)
set @TSQLCommand = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
declare @opentag varchar(4)
set @opentag= '<'
declare @closetag varchar(4) 
set @closetag= '>'
declare @newDataTxt varchar(max) 
set @newDataTxt= cast(@data as varchar(max))
set @newDataTxt = REPLACE ( REPLACE(@newDataTxt , @opentag , '<') , @closetag , '>')
-- print @newDataTxt
declare @newDataXml xml 
set @newDataXml = CONVERT ( xml , @newDataTxt)

declare @Version varchar(50)
set @Version = @newDataXml.value('(/EVENT_INSTANCE/TSQLCommand/CommandText/Version)[1]', 'varchar(50)')

-- if we are dropping take the version from the existing object 
if  ( SUBSTRING(@EventType , 0 , 5)) = 'DROP'
set @Version =( select top 1 [Version]  from ga.DbObjChangeLog where ObjectName=@ObjectName order by [LogId] desc)
if ( @Version is null)
set @Version = '1.0.0'

declare @Description varchar(max)
set @Description = @newDataXml.value('(/EVENT_INSTANCE/TSQLCommand/CommandText/Description)[1]', 'varchar(max)')

declare @ChangeDescription varchar(max)
set @ChangeDescription = @newDataXml.value('(/EVENT_INSTANCE/TSQLCommand/CommandText/ChangeDescription)[1]', 'varchar(max)')

declare @LoginName varchar(256) 
set @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
declare @FirstName varchar(50)
set @FirstName= (select [FirstName] from [ga].[LoginsForUsers] where [LoginName] = @LoginName)
declare @LastName varchar(50)
set @LastName  = (select [LastName] from [ga].[LoginsForUsers] where [LoginName] = @LoginName)
declare @SchemaName sysname 
set @SchemaName = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
--declare @Description xml 
--set @Description = @data.query('(/EVENT_INSTANCE/TSQLCommand/text())')




--print 'VERSION IS ' + @Version
--print @newDataTxt
--print cast(@data as varchar(max))


-- select column_name from information_schema.columns where table_name ='DbObjChangeLog'
insert into [ga].[DbObjChangeLog]
(
[DatabaseName] ,
[SchemaName],
[DbVersion] ,
[DbType],
[EventType],
[ObjectName],
[ObjectType] ,
[Version],
[Description],
[ChangeDescription],
[SqlCommand] ,
[LoginName] ,
[FirstName],
[LastName] 
)

values(

@DbName,
@SchemaName,
@DbVersion,
@DbType,
@EventType, 
@ObjectName, 
@ObjectType , 
@Version,
@Description,
@ChangeDescription,
@newDataTxt, 
@LoginName , 
@FirstName , 
@LastName
)




GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [TraceDbChanges] ON DATABASE
GO

ENABLE TRIGGER [TraceDbChanges] ON DATABASE
GO