SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[p_generate_audit_trigger]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_generate_audit_trigger]
GO

CREATE procedure dbo.p_generate_audit_trigger
	@SOURCE_TBLNM varchar(128)

as

begin


  declare @CMD varchar(4000),
	 @CMD_END VARCHAR(15)

  SET NOCOUNT ON

  set @CMD_END = 'GO'

set @CMD = '

 if exists (select * from dbo.sysobjects where name = ''t_' + @SOURCE_TBLNM + '_A'' and type = ''TR'' )
	 drop trigger dbo.t_' + @SOURCE_TBLNM + '_A 
' + @CMD_END + 
'

 SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
' +
+ @CMD_END 

--********************************************************************************

set @CMD = @CMD + ' 

CREATE TRIGGER t_' + @SOURCE_TBLNM + '_A
	ON ' + @SOURCE_TBLNM + '   
    AFTER INSERT,UPDATE,DELETE
        AS 
BEGIN

declare @tblname varchar(128)

  SET NOCOUNT ON

set @tblname = ''' + @SOURCE_TBLNM + ''' 

  
	select * into #td from deleted
	select * into #ti from inserted

	 exec dbo.P_TRIGGER @tblname = @tblname

		
    end

  SET NOCOUNT OFF

END 
' + @CMD_END + '

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
' + @CMD_END + '

sp_settriggerorder @triggername= ''t_' + @SOURCE_TBLNM + '_A'', @order=''LAST'', @stmttype = ''INSERT''
' + @CMD_END + '

sp_settriggerorder @triggername= ''t_' + @SOURCE_TBLNM + '_A'', @order=''LAST'', @stmttype = ''UPDATE''
' + @CMD_END + '

sp_settriggerorder  @triggername= ''t_' + @SOURCE_TBLNM + '_A'', @order=''LAST'', @stmttype = ''DELETE''
' + @CMD_END

--*******************************************************************************************************************************
set  @CMD = '--**************************************************
-- Trigger for table ' + @SOURCE_TBLNM  + ' 
--**************************************************
' +  @CMD + '
' + '--**************************************************'  + ' 
'

PRINT @CMD


end

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO