ALTER TRIGGER [ddl_test]   
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS   
AS  
BEGIN
	set nocount on;
	set xact_abort off;
	DECLARE @data XML = EVENTDATA()
	DECLARE @DBname NVARCHAR(255) = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(250)') 
	DECLARE @ObjectName NVARCHAR(250) = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(250)') 
	DECLARE @EventType NVARCHAR(250) = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(250)') 
	DECLARE @TSQLCommand NVARCHAR(MAX) = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')	

	BEGIN TRY
		IF (@EventType != 'UPDATE_STATISTICS' and @DBName != 'tempdb') 
		BEGIN
	
			INSERT INTO [ADMIN].dbo.ddl_log
			([db_name], [db_object_name], [action_name], [tsql_code],author_name)			
			VALUES (@DBName, @ObjectName, @EventType, @TSQLCommand,original_login())
		END
	END TRY
	BEGIN CATCH
    select 'xact_abort = off', error_message();
	END CATCH
END
    
GO

if (@EventType='CREATE_PROCEDURE')
		begin
			set @proc_charindex=CHARINDEX('PROC',@TSQLCommand)
			set @TSQLCommand ='/*DISCRIPTIONS*/'+Char(13)+' ALTER '+ SUBSTRING(@TSQLCommand,@proc_charindex,len(@TSQLCommand))
			--execute sp_sqlexec @TSQLCommand
		end

ALTER TRIGGER [ddl_logging]   
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS   
AS  
BEGIN
	set nocount on;
	set xact_abort off;
	DECLARE @data XML = EVENTDATA()
	DECLARE @DBname NVARCHAR(255) = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(250)') 
	DECLARE @ObjectName NVARCHAR(250) = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(250)') 
	DECLARE @EventType NVARCHAR(250) = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(250)') 
	DECLARE @TSQLCommand NVARCHAR(MAX) = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')	

	BEGIN TRY
		IF (@EventType != 'UPDATE_STATISTICS' and @DBName != 'tempdb') 
		BEGIN
		declare @proc_charindex int,@UseAndExecStatment nvarchar(max)
			INSERT INTO [ADMIN].dbo.ddl_log
			([db_name], [db_object_name], [action_name], [tsql_code],author_name)			
			VALUES (@DBName, @ObjectName, @EventType, @TSQLCommand,original_login())
		if (@EventType='CREATE_PROCEDURE')
		begin
			set @proc_charindex=CHARINDEX('PROC',@TSQLCommand)
			set @TSQLCommand =  '-- ============================================================================'+Char(10)+'
-- Author:  <'+original_login()+'>'+Char(10)+'
-- Create date: <'+CAST(GETDATE() as nvarchar(28))+'>'+Char(10)+'
-- Server:  <'+@@SERVERNAME+'>'+Char(10)+'
-- Database: <'+@DBName+'>'+Char(10)+'
-- Git:   <>'+Char(10)+'
-- Description: <>'+Char(10)+'
-- ============================================================================'+Char(13)+'
ALTER '+ SUBSTRING(@TSQLCommand,@proc_charindex,len(@TSQLCommand))
			SET @UseAndExecStatment = 'use ' + @DBName +' exec sp_executesql @TSQLCommand'
			EXEC sp_executesql  @UseAndExecStatment,
						N'@TSQLCommand nvarchar(max)', @TSQLCommand=@TSQLCommand
		end

		END
	END TRY
	BEGIN CATCH
    select 'xact_abort = off', @TSQLCommand,error_message();
	END CATCH
END