IF EXISTS (SELECT * FROM SYS.TRIGGERS WHERE NAME = 'TRG_PRC_FILIAL_CONFIG_AUDIT_TRIGGER')
BEGIN
	DROP TRIGGER TRG_PRC_FILIAL_CONFIG_AUDIT_TRIGGER
END

GO

CREATE TRIGGER [dbo].[TRG_PRC_FILIAL_CONFIG_AUDIT_TRIGGER] ON [dbo].[PRC_FILIAL_CONFIG]
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
                     , 'PRC_FILIAL_CONFIG'
                     , @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 PRC_FILIAL_CONFIG_AUDIT_TRIGGER
                        SELECT
                               @ID_AUDIT
                             , @@SPID
                             , GETDATE()
                             , 'INSERT'
                             , CD_EMP
                             , CD_FILIAL
                             , CD_CHAVE
                             , VALOR
                             , CD_USU
                             , DT_ULT_ALT
                             , GRAVA_MINUSCULO
                        FROM
                             INSERTED;
             END;
         IF @Operacao IN(1, 2)
             BEGIN
                 INSERT INTO PRC_FILIAL_CONFIG_AUDIT_TRIGGER
                        SELECT
                               @ID_AUDIT
                             , @@SPID
                             , GETDATE()
                             , CASE
                                   WHEN @Operacao = 1 THEN 'UPDATE'
                                   ELSE 'DELETE'
                               END
                             , CD_EMP
                             , CD_FILIAL
                             , CD_CHAVE
                             , VALOR
                             , CD_USU
                             , DT_ULT_ALT
                             , GRAVA_MINUSCULO
                        FROM
                             DELETED;
             END;     

/*--SELECT * FROM AUDIT_TRIGGER   --SELECT * FROM PRC_FILIAL_CONFIG_BKP;*/

/*end else return */

     END;
GO

ALTER TABLE [dbo].[PRC_FILIAL_CONFIG] ENABLE TRIGGER [TRG_PRC_FILIAL_CONFIG_AUDIT_TRIGGER]
GO


