IF EXISTS
(
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[P_CREATE_AUDIT_TRIGGER_HISTORICO_COMISSAO]')
          AND type IN(N'P', N'PC')
)
    BEGIN
        DROP PROCEDURE [P_CREATE_AUDIT_TRIGGER_HISTORICO_COMISSAO]
    END;
GO

CREATE PROCEDURE [P_CREATE_AUDIT_TRIGGER_HISTORICO_COMISSAO] (@TABELA VARCHAR (150) = NULL)
AS 
BEGIN

SET NOCOUNT ON;

	IF @TABELA IS NULL 
		BEGIN 
			SELECT 'INFORME UMA TABELA'
			RETURN;
		END
     
	IF NOT EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME =@TABELA)
	BEGIN 
		SELECT 'A TABELA INFORMADA NAO EXISTE'
		RETURN;
	END 
	
	DECLARE @COLUMN_ID INT,  
	   @COLUNA VARCHAR(MAX), 
	   @TIPO VARCHAR(20), 
	   @SQL_TABLE VARCHAR(MAX)= NULL, 
	   @TAMANHO INT,
	   @OBJECT INT,
	   @COLUNAS VARCHAR(MAX)='',
	   @OBJECT_TABLE INT  =(SELECT OBJECT_ID 
								FROM SYS.TABLES WHERE NAME =@TABELA);
--------------------------------------------------------------------------------------------------------------

	IF (@OBJECT_TABLE IS NULL)
	begin 
		PRINT 'NAO EXISTE A TABELA ' + @TABELA
		RETURN
	end

	SELECT @OBJECT =OBJECT_ID FROM SYS.TABLES WHERE NAME =CONVERT (VARCHAR ,@TABELA +'_AUDIT_TRIGGER')
	SET @SQL_TABLE = 'CREATE TABLE '+@TABELA+'_AUDIT_TRIGGER'+'(CD_CTR_AUDIT_TRIGGER BIGINT IDENTITY(1,1), ID_AUDIT BIGINT, SPID INT ,DT_EVENT DATETIME,OPERACAO VARCHAR(20),';
	  
    IF NOT (@OBJECT IS NULL)
    BEGIN 
		SET @SQL_TABLE = ' /* DROP TABLE '+@TABELA+'_AUDIT_TRIGGER; */ ' + CHAR(13) + @SQL_TABLE;
	END
--------------------------------------------------------------------------------------------------------------
	
	DECLARE TESTE CURSOR
	FOR   
	SELECT COLUMN_ID
		FROM SYS.COLUMNS
		WHERE OBJECT_ID =@OBJECT_TABLE
		ORDER BY COLUMN_ID ASC;
		OPEN TESTE;

	FETCH NEXT FROM TESTE INTO  @COLUMN_ID; 
	WHILE @@FETCH_STATUS=0
	BEGIN
		SELECT
				@COLUNA=COL.NAME
			,@TIPO=TY.NAME
			,@TAMANHO=COL.MAX_LENGTH
		FROM
				SYS.TABLES AS TAB
			INNER JOIN SYS.COLUMNS AS COL ON TAB.OBJECT_ID=COL.OBJECT_ID
			INNER JOIN SYS.TYPES AS TY ON TY.SYSTEM_TYPE_ID=COL.SYSTEM_TYPE_ID
		WHERE TAB.OBJECT_ID=@OBJECT_TABLE
				AND COL.COLUMN_ID=@COLUMN_ID;
			
		SET @SQL_TABLE=@SQL_TABLE+@COLUNA+' '+@TIPO+', ';
		SET @COLUNAS =@COLUNAS+@COLUNA+', '
		IF(@TIPO='NTEXT'
				OR @TIPO='VARCHAR'
				OR @TIPO='CHAR'
				OR @TIPO='NVARCHAR'
				OR @TIPO='NCHAR'
				OR @TIPO='SYSNAME')
				AND @TAMANHO=-1

		BEGIN
			SET @SQL_TABLE=LEFT(@SQL_TABLE,LEN(@SQL_TABLE)-1)+'(MAX) ,';
		END;
		ELSE IF(@TIPO='NTEXT' OR @TIPO='VARCHAR' OR @TIPO='CHAR' OR @TIPO='NVARCHAR'
				OR @TIPO='NCHAR' OR @TIPO='SYSNAME') AND @TAMANHO<>-1
		BEGIN
			SET @SQL_TABLE=LEFT(@SQL_TABLE,LEN(@SQL_TABLE)-1)+'('+CONVERT(VARCHAR,@TAMANHO)+') ,';
		END;
		ELSE
		BEGIN
				SET @SQL_TABLE=@SQL_TABLE;
		END;

		FETCH NEXT FROM TESTE INTO @COLUMN_ID;
	END;

	CLOSE TESTE;
	DEALLOCATE TESTE;
--------------------------------------------------------------------------------------------------------------

	SET @SQL_TABLE = LEFT (@SQL_TABLE, LEN(@SQL_TABLE)-1);
	SET @COLUNAS = LEFT (@COLUNAS, LEN(@COLUNAS)-1);
	SET @SQL_TABLE += ' CONSTRAINT [PK_'+@TABELA+'_AUDIT_TRIGGER] PRIMARY KEY CLUSTERED (CD_CTR_AUDIT_TRIGGER) )';
	--SELECT 'CRIACAO DA TABELA '+@TABELA+'_AUDIT_TRIGGER',@SQL_TABLE  

	
	DECLARE @SCRIPT_TABELA VARCHAR(MAX) = 'IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@TABELA+'_AUDIT_TRIGGER]'') AND type in (N''U''))
	BEGIN
		'+@SQL_TABLE+'
	END'

	SET @SCRIPT_TABELA = REPLACE(@SCRIPT_TABELA,'sysname','varchar')

	--print @SCRIPT_TABELA

	EXECUTE (@SCRIPT_TABELA)
 
    DECLARE @COMMAND VARCHAR (500)
    DECLARE @NM_TRIGGER VARCHAR (200)= 'TGR_'+@TABELA+'_AUDIT_TRIGGER'
 
    SET @COMMAND ='DROP TRIGGER '+@NM_TRIGGER
 
  --  IF EXISTS  (SELECT 1 FROM SYS.TRIGGERS WHERE [NAME] =@NM_TRIGGER)
  --  BEGIN
		--SELECT @COMMAND
  --  END
     
DECLARE @SCRIPT VARCHAR (MAX) =  
'CREATE TRIGGER '+@NM_TRIGGER+' ON ' +@TABELA+'
 AFTER INSERT,UPDATE,DELETE
AS
SET NOCOUNT ON;
BEGIN
/*
if ( program_name() =''Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'' or
program_name() =''Microsoft SQL Server Management Studio - Query'' or
program_name() =''.Net SqlClient Data Provider'' or
program_name() like ''%Management Studio%'')
begin
*/
	/*  0 - Insert
		1 - Update
		2 - Delete */
	Declare @Operacao Tinyint=0; 

	CREATE TABLE #RESULT_DBCC	(EVENT_TYPE VARCHAR(1000),PARAMETERS INT,EVENTINFO  VARCHAR(MAX));
      
	INSERT INTO #RESULT_DBCC
	EXEC (''DBCC INPUTBUFFER(@@SPID)  WITH NO_INFOMSGS '');
	DECLARE @SQL VARCHAR(MAX);
	SELECT @SQL = EVENTINFO
	FROM #RESULT_DBCC;
	CREATE TABLE #sp_who2
			 (spid        INT, 
			  STATUS      VARCHAR(100), 
			  login       VARCHAR(100), 
			  hostname    VARCHAR(200), 
			  blkby       varchar(50), 
			  dbname      VARCHAR(100), 
			  command     VARCHAR(250), 
			  cputime     varchar(50), 
			  diskio      varchar(50), 
			  lastbatch   varchar(50), 
			  programname VARCHAR(500), 
			  spid2       varchar(50), 
			  requestid   varchar(50)
			 );
				insert into #sp_who2   
				exec sp_who2  @@spid;	
	DECLARE  
		@DT_EVENTO DATETIME,
		@DS_USUARIO VARCHAR(100),
		@DS_USUARIO_ORIGINAL VARCHAR(100),
		@DS_TIPO_USUARIO VARCHAR(30),
		@DS_IP VARCHAR(30),
		@DS_HOSTNAME VARCHAR(100),
		@ID_AUDIT			  BIGINT,
		@DS_SOFTWARE VARCHAR(100); 
    select top 1 @DS_SOFTWARE = programname from #sp_who2;
	SELECT 
		@DT_EVENTO = GETDATE(),
		@DS_USUARIO = CURRENT_USER,
		@DS_TIPO_USUARIO = SUSER_SNAME() ,
		@DS_HOSTNAME = HOST_NAME(), 
		@DS_USUARIO_ORIGINAL =ORIGINAL_LOGIN(); 
     
	INSERT INTO AUDIT_TRIGGER
	SELECT  
		@DT_EVENTO,
		@@SPID,
		@DS_USUARIO,
		@DS_USUARIO_ORIGINAL,
		@DS_TIPO_USUARIO,
		@DS_IP,
		@DS_HOSTNAME ,
		@DS_SOFTWARE,
		'''+@TABELA+''',
		@SQL
				
	SET @ID_AUDIT= @@IDENTITY;

	IF EXISTS(SELECT * FROM DELETED)
		set @Operacao=1;
	IF NOT EXISTS(SELECT * FROM INSERTED)
		set @Operacao=2;
	IF @Operacao =0  
	BEGIN  
		INSERT INTO '+@TABELA+'_AUDIT_TRIGGER SELECT 
		@ID_AUDIT,	  
		@@SPID,
		GETDATE(),
		''INSERT'','+@COLUNAS+'
		FROM INSERTED ;
			
    END
	IF @Operacao in (1,2)
    BEGIN  
    INSERT INTO '+@TABELA+'_AUDIT_TRIGGER 
            SELECT 
			     @ID_AUDIT
				,@@SPID		
                ,GETDATE()
			    ,CASE when @Operacao=1 then ''UPDATE'' else ''DELETE'' END,'+@COLUNAS+'
            FROM DELETED; 
     END;

	/*--SELECT * FROM AUDIT_TRIGGER
	--SELECT * FROM '+@TABELA+'_BKP;*/
	/*end else return */;

END ';

DECLARE @SCRIPT_DROP_TRIGGER VARCHAR(MAX)
	--SELECT 'CRIACAO DA TRIGGER '+@NM_TRIGGER,@SCRIPT
	SET @SCRIPT_DROP_TRIGGER = 'IF EXISTS (SELECT * FROM SYS.TRIGGERS WHERE NAME  = '''+@NM_TRIGGER+''' )
BEGIN	
	DROP TRIGGER '+@NM_TRIGGER+'
END
'
EXECUTE (@SCRIPT_DROP_TRIGGER)

EXECUTE (@SCRIPT)

--print @SCRIPT_TRIGGER


	--PRINT 'TABELA DE BACKUP : '+@TABELA+'_BKP'+ ' VINCULADA A TABELA AUDIT_TRIGGER'

END