IF EXISTS(SELECT *
          FROM SYSOBJECTS O, SYSUSERS U
          WHERE O.NAME = 'LOG_GER_EMPRESA' AND
                TYPE   = 'TR'              AND
                U.UID  = O.UID             AND
                U.NAME = USER)

DROP TRIGGER LOG_GER_EMPRESA
GO

CREATE TRIGGER LOG_GER_EMPRESA
ON EMPRESA
FOR UPDATE,INSERT, DELETE
AS
BEGIN
   DECLARE @V_DSREMOTEADDR VARCHAR(15);
   DECLARE @V_DSREMOTEHOST VARCHAR(15);
   DECLARE @V_DSSERVERRDM  VARCHAR(20);
   DECLARE @V_DSSERVMETHOD VARCHAR(30);
   DECLARE @V_DSREMOTEOPER VARCHAR(12);
   DECLARE @V_DESC         VARCHAR(3000);
   DECLARE @V_IDTIPOOPER   VARCHAR(1);
   DECLARE @V_ITPESQ       VARCHAR(2);
   DECLARE @V_USUARIO      VARCHAR(40);

   /*VARIAVEIS DO CURSOR C_CURSOR*/
   DECLARE @V_IDCOMANDO    VARCHAR(1);

   DECLARE @V_OLD_CDEMPRESA      VARCHAR(2);
   DECLARE @V_OLD_NMEMPRESA      VARCHAR(40);
   DECLARE @V_OLD_CDPAIS         VARCHAR(4);
   DECLARE @V_OLD_SGESTADO       VARCHAR(2);
   DECLARE @V_OLD_CDMUNICIPIO    VARCHAR(4);
   DECLARE @V_OLD_NMRAZSOCEMPR   VARCHAR(80);
   DECLARE @V_OLD_DSENDEEMPR     VARCHAR(50);
   DECLARE @V_OLD_NRINSJUREMPR   VARCHAR(20);
   DECLARE @V_OLD_NRINSCESTA     VARCHAR(16);
   DECLARE @V_OLD_IDTPIJUREMPR   VARCHAR(1);
   DECLARE @V_OLD_NRREGJUNCOM    VARCHAR(20);
   DECLARE @V_OLD_DSEMAILEMPRESA VARCHAR(100);
   DECLARE @V_OLD_DSMENSEMAILNFE VARCHAR(2000);

   DECLARE @V_NEW_CDEMPRESA      VARCHAR(2);
   DECLARE @V_NEW_NMEMPRESA      VARCHAR(40);
   DECLARE @V_NEW_CDPAIS         VARCHAR(4);
   DECLARE @V_NEW_SGESTADO       VARCHAR(2);
   DECLARE @V_NEW_CDMUNICIPIO    VARCHAR(4);
   DECLARE @V_NEW_NMRAZSOCEMPR   VARCHAR(80);
   DECLARE @V_NEW_DSENDEEMPR     VARCHAR(50);
   DECLARE @V_NEW_NRINSJUREMPR   VARCHAR(20);
   DECLARE @V_NEW_NRINSCESTA     VARCHAR(16);
   DECLARE @V_NEW_IDTPIJUREMPR   VARCHAR(1);
   DECLARE @V_NEW_NRREGJUNCOM    VARCHAR(20);
   DECLARE @V_NEW_DSEMAILEMPRESA VARCHAR(100);
   DECLARE @V_NEW_DSMENSEMAILNFE VARCHAR(2000);

   SET @V_DESC   = '';
   SET @V_ITPESQ = '';
   EXEC NMUSUARIO  @V_USUARIO;
   SET @V_USUARIO = ISNULL(@V_USUARIO, ' ');
   EXEC WEBTUNNING @V_DSREMOTEADDR,
                   @V_DSREMOTEHOST,
                   @V_DSSERVERRDM,
                   @V_DSSERVMETHOD,
                   @V_DSREMOTEOPER;


   DECLARE C_CURSOR CURSOR FOR
      SELECT 'I' IDCOMANDO,
                 CDEMPRESA,
                 NMEMPRESA,
                 CDPAIS,
                 SGESTADO,
                 CDMUNICIPIO,
                 NMRAZSOCEMPR,
                 DSENDEEMPR,
                 NRINSJUREMPR,
                 NRINSCESTA,
                 IDTPIJUREMPR,
                 NRREGJUNCOM,
                 DSEMAILEMPRESA,
                 DSMENSEMAILNFE,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL
        FROM INSERTED
       WHERE CDEMPRESA NOT IN (SELECT CDEMPRESA FROM DELETED)

      UNION ALL

      SELECT 'U' IDCOMANDO,
                 INSERTED.CDEMPRESA,
                 INSERTED.NMEMPRESA,
                 INSERTED.CDPAIS,
                 INSERTED.SGESTADO,
                 INSERTED.CDMUNICIPIO,
                 INSERTED.NMRAZSOCEMPR,
                 INSERTED.DSENDEEMPR,
                 INSERTED.NRINSJUREMPR,
                 INSERTED.NRINSCESTA,
                 INSERTED.IDTPIJUREMPR,
                 INSERTED.NRREGJUNCOM,
                 INSERTED.DSEMAILEMPRESA,
                 INSERTED.DSMENSEMAILNFE,
                 DELETED.CDEMPRESA,
                 DELETED.NMEMPRESA,
                 DELETED.CDPAIS,
                 DELETED.SGESTADO,
                 DELETED.CDMUNICIPIO,
                 DELETED.NMRAZSOCEMPR,
                 DELETED.DSENDEEMPR,
                 DELETED.NRINSJUREMPR,
                 DELETED.NRINSCESTA,
                 DELETED.IDTPIJUREMPR,
                 DELETED.NRREGJUNCOM,
                 DELETED.DSEMAILEMPRESA,
                 DELETED.DSMENSEMAILNFE
        FROM INSERTED, DELETED
       WHERE INSERTED.CDEMPRESA = DELETED.CDEMPRESA

      UNION ALL

      SELECT 'D' IDCOMANDO,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 NULL,
                 CDEMPRESA,
                 NMEMPRESA,
                 CDPAIS,
                 SGESTADO,
                 CDMUNICIPIO,
                 NMRAZSOCEMPR,
                 DSENDEEMPR,
                 NRINSJUREMPR,
                 NRINSCESTA,
                 IDTPIJUREMPR,
                 NRREGJUNCOM,
                 DSEMAILEMPRESA,
                 DSMENSEMAILNFE
        FROM DELETED
       WHERE CDEMPRESA NOT IN (SELECT CDEMPRESA FROM INSERTED);

   OPEN C_CURSOR;
   FETCH NEXT FROM C_CURSOR INTO  @V_IDCOMANDO,    @V_NEW_CDEMPRESA, @V_NEW_NMEMPRESA,
                                  @V_NEW_CDPAIS, @V_NEW_SGESTADO, @V_NEW_CDMUNICIPIO, @V_NEW_NMRAZSOCEMPR,
                                  @V_NEW_DSENDEEMPR, @V_NEW_NRINSJUREMPR, @V_NEW_NRINSCESTA, @V_NEW_IDTPIJUREMPR,
                                  @V_NEW_NRREGJUNCOM, @V_NEW_DSEMAILEMPRESA, @V_NEW_DSMENSEMAILNFE,
                                  @V_OLD_CDEMPRESA, @V_OLD_NMEMPRESA,
                                  @V_OLD_CDPAIS, @V_OLD_SGESTADO, @V_OLD_CDMUNICIPIO, @V_OLD_NMRAZSOCEMPR,
                                  @V_OLD_DSENDEEMPR, @V_OLD_NRINSJUREMPR, @V_OLD_NRINSCESTA, @V_OLD_IDTPIJUREMPR,
                                  @V_OLD_NRREGJUNCOM, @V_OLD_DSEMAILEMPRESA, @V_OLD_DSMENSEMAILNFE;
   WHILE (@@FETCH_STATUS = 0) BEGIN
      IF (@V_IDCOMANDO = 'D') BEGIN
         IF @V_OLD_CDEMPRESA IS NOT NULL BEGIN
            SET @V_DESC = @V_DESC + ' CdEmpresa=' + @V_OLD_CDEMPRESA;
         END;
         IF @V_OLD_NMEMPRESA IS NOT NULL BEGIN
            SET @V_DESC = @V_DESC + ' NmEmpresa=' + @V_OLD_NMEMPRESA;
         END;
         IF @V_OLD_CDPAIS IS NOT NULL BEGIN
            SET @V_DESC = @V_DESC + ' Pais=' + @V_OLD_CDPAIS;
         END;
         IF @V_OLD_SGESTADO IS NOT NULL BEGIN
            SET @V_DESC = @V_DESC + ' Estado=' + @V_OLD_SGESTADO;
         END;
         IF @V_OLD_CDMUNICIPIO IS NOT NULL BEGIN
            SET @V_DESC = @V_DESC + ' Municipio=' + @V_OLD_CDMUNICIPIO;
         END;
         IF @V_OLD_NMRAZSOCEMPR IS NOT NULL BEGIN
            SET @V_DESC = @V_DESC + ' RazSocial=' + @V_OLD_NMRAZSOCEMPR;
         END;
         IF @V_OLD_DSENDEEMPR IS NOT NULL BEGIN
            SET @V_DESC = @V_DESC + ' EndeEmpr=' + @V_OLD_DSENDEEMPR;
         END;
         IF @V_OLD_NRINSJUREMPR IS NOT NULL BEGIN
            SET @V_DESC = @V_DESC + ' InsJur=' + @V_OLD_NRINSJUREMPR;
         END;
         IF @V_OLD_NRINSCESTA IS NOT NULL BEGIN
            SET @V_DESC = @V_DESC + ' InsEst=' + @V_OLD_NRINSCESTA;
         END;
         IF @V_OLD_IDTPIJUREMPR IS NOT NULL BEGIN
            SET @V_DESC = @V_DESC + ' TpInscJur=' + @V_OLD_IDTPIJUREMPR;
         END;
         IF @V_OLD_NRREGJUNCOM IS NOT NULL BEGIN
            SET @V_DESC = @V_DESC + ' NRegJun=' + @V_OLD_NRREGJUNCOM;
         END;
         IF @V_OLD_DSEMAILEMPRESA IS NOT NULL BEGIN
            SET @V_DESC = @V_DESC + ' Email=' + @V_OLD_DSEMAILEMPRESA;
         END;
         IF @V_OLD_DSMENSEMAILNFE IS NOT NULL BEGIN
            SET @V_DESC = @V_DESC + ' DSEmailNfe=' + @V_OLD_DSMENSEMAILNFE;
         END;

         SET @V_ITPESQ     = @V_OLD_CDEMPRESA;
         SET @V_IDTIPOOPER = 3;
      END
      ELSE BEGIN
         IF @V_IDCOMANDO = 'U' BEGIN
            SET @V_ITPESQ     = @V_OLD_CDEMPRESA;
            SET @V_IDTIPOOPER = 2;
         END
         ELSE BEGIN
            SET @V_ITPESQ     = @V_OLD_CDEMPRESA;
            SET @V_IDTIPOOPER = 1;
         END;
         IF ISNULL(@V_OLD_CDEMPRESA, '#') <> ISNULL(@V_NEW_CDEMPRESA, '#') BEGIN
            SET @V_DESC = @V_DESC + ' CdEmpresa=' + ISNULL(@V_NEW_CDEMPRESA, '');
         END;
         IF ISNULL(@V_OLD_NMEMPRESA, '#') <> ISNULL(@V_NEW_NMEMPRESA, '#') BEGIN
            SET @V_DESC = @V_DESC + ' NmEmpresa=' + ISNULL(@V_NEW_NMEMPRESA, '');
         END;
         IF ISNULL(@V_OLD_CDPAIS, '#') <> ISNULL(@V_NEW_CDPAIS, '#') BEGIN
            SET @V_DESC = @V_DESC + ' Pais=' + ISNULL(@V_NEW_CDPAIS, '');
         END;
         IF ISNULL(@V_OLD_SGESTADO, '#') <> ISNULL(@V_NEW_SGESTADO, '#') BEGIN
            SET @V_DESC = @V_DESC + ' Estado=' + ISNULL(@V_NEW_SGESTADO, '');
         END;
         IF ISNULL(@V_OLD_CDMUNICIPIO, '#') <> ISNULL(@V_NEW_CDMUNICIPIO, '#') BEGIN
            SET @V_DESC = @V_DESC + ' Municipio=' + ISNULL(@V_NEW_CDMUNICIPIO, '');
         END;
         IF ISNULL(@V_OLD_NMRAZSOCEMPR, '#') <> ISNULL(@V_NEW_NMRAZSOCEMPR, '#') BEGIN
            SET @V_DESC = @V_DESC + ' RazSocial=' + ISNULL(@V_NEW_NMRAZSOCEMPR, '');
         END;
         IF ISNULL(@V_OLD_DSENDEEMPR, '#') <> ISNULL(@V_NEW_DSENDEEMPR, '#') BEGIN
            SET @V_DESC = @V_DESC + ' EndeEmpr=' + ISNULL(@V_NEW_DSENDEEMPR, '');
         END;
         IF ISNULL(@V_OLD_NRINSJUREMPR, '#') <> ISNULL(@V_NEW_NRINSJUREMPR, '#') BEGIN
            SET @V_DESC = @V_DESC + ' InsJur=' + ISNULL(@V_NEW_NRINSJUREMPR, '');
         END;
         IF ISNULL(@V_OLD_NRINSCESTA, '#') <> ISNULL(@V_NEW_NRINSCESTA, '#') BEGIN
            SET @V_DESC = @V_DESC + ' InsEst=' + ISNULL(@V_NEW_NRINSCESTA, '');
         END;
         IF ISNULL(@V_OLD_IDTPIJUREMPR, '#') <> ISNULL(@V_NEW_IDTPIJUREMPR, '#') BEGIN
            SET @V_DESC = @V_DESC + ' TpInscJur=' + ISNULL(@V_NEW_IDTPIJUREMPR, '');
         END;
         IF ISNULL(@V_OLD_NRREGJUNCOM, '#') <> ISNULL(@V_NEW_NRREGJUNCOM, '#') BEGIN
            SET @V_DESC = @V_DESC + ' NRegJun=' + ISNULL(@V_NEW_NRREGJUNCOM, '');
         END;
         IF ISNULL(@V_OLD_DSEMAILEMPRESA, '#') <> ISNULL(@V_NEW_DSEMAILEMPRESA, '#') BEGIN
            SET @V_DESC = @V_DESC + ' Email=' + ISNULL(@V_NEW_DSEMAILEMPRESA, '');
         END;
         IF ISNULL(@V_OLD_DSMENSEMAILNFE, '#') <> ISNULL(@V_NEW_DSMENSEMAILNFE, '#') BEGIN
            SET @V_DESC = @V_DESC + ' DSEmailNfe=' + ISNULL(@V_NEW_DSMENSEMAILNFE, '');
         END;
      END;
      IF NOT(@V_DESC IS NULL) AND NOT @V_DESC = '' BEGIN
         SET @V_DESC = SUBSTRING(@V_DESC, 1, 300);
         INSERT INTO LOGOPERA
            (NMTABELA,   DTOPERACAO, NMRESPOPER,
             IDTIPOOPER, CDPESQITEM, DSOPERACAO,
             DSRDMUSUOPER, DSENDUSUOPER, DSNMUSUOPER, DSMETUSUOPER, CDORESPOPER)
         VALUES
            ('EMPRESA',  GETDATE(), @V_USUARIO,
             @V_IDTIPOOPER,  @V_ITPESQ, @V_DESC,
             @V_DSSERVERRDM, @V_DSREMOTEADDR, @V_DSREMOTEHOST, @V_DSSERVMETHOD, @V_DSREMOTEOPER);
         SET @V_DESC = '';
      END;
      FETCH NEXT FROM C_CURSOR INTO  @V_IDCOMANDO,    @V_NEW_CDEMPRESA, @V_NEW_NMEMPRESA,
                                     @V_NEW_CDPAIS, @V_NEW_SGESTADO, @V_NEW_CDMUNICIPIO, @V_NEW_NMRAZSOCEMPR,
                                     @V_NEW_DSENDEEMPR, @V_NEW_NRINSJUREMPR, @V_NEW_NRINSCESTA, @V_NEW_IDTPIJUREMPR,
                                     @V_NEW_NRREGJUNCOM, @V_NEW_DSEMAILEMPRESA, @V_NEW_DSMENSEMAILNFE,
                                     @V_OLD_CDEMPRESA, @V_OLD_NMEMPRESA,
                                     @V_OLD_CDPAIS, @V_OLD_SGESTADO, @V_OLD_CDMUNICIPIO, @V_OLD_NMRAZSOCEMPR,
                                     @V_OLD_DSENDEEMPR, @V_OLD_NRINSJUREMPR, @V_OLD_NRINSCESTA, @V_OLD_IDTPIJUREMPR,
                                     @V_OLD_NRREGJUNCOM, @V_OLD_DSEMAILEMPRESA, @V_OLD_DSMENSEMAILNFE;
   END;
   CLOSE C_CURSOR;
   DEALLOCATE C_CURSOR;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'LOG_GER_EMPRESA', '5.30.xxx';
GO

