USE [DBGA_DEV]
GO
/****** Object:  DdlTrigger [trigMetaDoc_TraceDbChanges]    Script Date: 04/10/2009 21:31:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create trigger [trigMetaDoc_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)=(select ga.GetDbVersion())
declare @DbName varchar(256)=@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
declare @DbType varchar(50)=(select ga.GetDbType())
declare @EventType varchar(256)=@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')
declare @ObjectName varchar(256) = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
declare @ObjectType varchar(25) = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)')
declare @TSQLCommand varchar(max) = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
declare @LoginName varchar(256) = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
declare @FirstName varchar(50)= (select [FirstName] from [ga].[tb_BLSec_LoginsForUsers] where [LoginName] = @LoginName)
declare @LastName varchar(50) = (select [LastName] from [ga].[tb_BLSec_LoginsForUsers] where [LoginName] = @LoginName)
declare @SchemaName sysname = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
declare @Description xml = @data.query('(/EVENT_INSTANCE/TSQLCommand/text())')


print cast(@data as varchar(max))

declare @Desc varchar(200) = @Description.value('(/Description)[1]' , 'varchar(200)')
print @TSQLCommand
print @Desc

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

values(

@DbName,
@SchemaName,
@DbVersion,
@DbType,
@EventType, 
@ObjectName, 
@ObjectType , 
@TSQLCommand, 
@LoginName , 
@FirstName , 
@LastName
)

-- if eventType like create -> add ext props ; 
-- if eventType like alter -> update ext props ;
-- if eventType like drop -> 
-- select name from sys.tables where name like '%ext%'

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [trigMetaDoc_TraceDbChanges] ON DATABASE
GO

ENABLE TRIGGER [trigMetaDoc_TraceDbChanges] ON DATABASE
GO