USE [Cosmos_Logs]
GO

/****** Object:  Trigger [dbo].[TX_TriggerTable]    Script Date: 10/24/2008 08:42:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[TX_TriggerTable] ON  [dbo].[TriggerTable]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

BEGIN TRY
	DECLARE @TableName sysname, @OperationDate datetime, @deleted xml, @inserted xml
	
	if exists(SELECT * FROM deleted)
		select @deleted = (select * from deleted for xml auto, elements)
	
	if exists(SELECT * FROM inserted)
		select @inserted = (select * from inserted for xml auto, elements)

	if coalesce(@deleted, @inserted) is not null
	begin
		SELECT @TableName = OBJECT_NAME(parent_id) FROM sys.triggers WHERE object_id = @@procid

		Insert Into Cosmos_Logs.dbo.Logs (
			DBName, TableName, inserted, deleted, OperationDate
		) VALUES (
			DB_NAME(), @TableName, @inserted, @deleted, GETDATE()
		)
	end

END TRY
BEGIN CATCH
	--
END CATCH

END

GO

USE [Cosmos_Logs]
GO

/****** Object:  Trigger [dbo].[TX_LoggedTables]    Script Date: 10/24/2008 08:46:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TX_LoggedTables] ON  [dbo].[LoggedTables]
   AFTER INSERT, DELETE
AS 
BEGIN
	SET NOCOUNT ON;

BEGIN TRY

	DECLARE @SQL nvarchar(max), @SQLMain nvarchar(max), @DBName sysname, @TableName sysname	

	if exists(SELECT * FROM deleted)
	BEGIN

		SELECT @DBName = DBName, @TableName = TableName FROM deleted

		-- delete trigger
		SET @SQL = N'DROP TRIGGER [dbo].[TX_' + @TableName + ']'
		
		SELECT @SQLMain = N'EXEC ' + @DBName + '.dbo.sp_executesql @SQL'
		EXEC sp_executesql @SQLMain, N'@SQL nvarchar(max)', @SQL = @SQL;

		-- create history record
		IF NOT (@@ERROR <> 0)
			INSERT INTO LoggedTableHistory
			(
				[DBName]
				,[TableName]
				,[StartedDate]
				,[StartedByUser]
				,[StoppedDate]
				,[StoppedByUser]
			) 
			SELECT
				DBName,
				TableName,
				InsertDate,
				InsertedUser,
				GETDATE(),
				SYSTEM_USER
			FROM deleted
	END

	if exists(SELECT * FROM inserted)
	begin
		
		SELECT @DBName = DBName, @TableName = TableName FROM inserted

		SET @SQL = N'
		CREATE TRIGGER [dbo].[TX_' + @TableName + '] ON  [dbo].[' + @TableName + ']
		   AFTER INSERT,DELETE,UPDATE
		AS 
		BEGIN
			SET NOCOUNT ON

		BEGIN TRY
			DECLARE @TableName sysname, @OperationDate datetime, @deleted xml, @inserted xml
			
			if exists(SELECT * FROM deleted)
				select @deleted = (select * from deleted for xml auto, elements)
			
			if exists(SELECT * FROM inserted)
				select @inserted = (select * from inserted for xml auto, elements)

			IF COALESCE(@deleted, @inserted) IS NOT NULL
			BEGIN
				SELECT @TableName = OBJECT_NAME(parent_id) FROM sys.triggers WHERE object_id = @@procid

				Insert Into Cosmos_Logs.dbo.Logs (
					DBName, TableName, inserted, deleted, OperationDate
				) VALUES (
					DB_NAME(), @TableName, @inserted, @deleted, GETDATE()
				)
			END

		END TRY
		BEGIN CATCH
			
		END CATCH

		END
		'		

		SELECT @SQLMain = N'EXEC ' + @DBName + '.dbo.sp_executesql @SQL'

		EXEC sp_executesql @SQLMain, N'@SQL nvarchar(max)', @SQL = @SQL;
	end


END TRY
BEGIN CATCH
	IF XACT_STATE() = -1 
		ROLLBACK;
END CATCH

END


GO