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

CREATE TRIGGER LOG_VND_TIPOCONSLOJA
ON TIPOCONSLOJA
FOR INSERT, UPDATE, DELETE
AS
  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(500);
  DECLARE @V_ITPESQ       VARCHAR(40);
  DECLARE @V_NMOPERADOR   VARCHAR(40);
  DECLARE @V_IDTIPOOPER   VARCHAR(1);

/*VARIÁVEIS DO CURSOR C_CURSOR*/

  DECLARE @V_ID_COMANDO    VARCHAR(1);
  
  DECLARE @V_NEW_CDFILIAL            varchar(4);
  DECLARE @V_NEW_CDLOJA              VARCHAR(15);
  DECLARE @V_NEW_CDTIPOCONS          varchar(2);
  DECLARE @V_NEW_VRMAXCONSDIAC       double precision;  
  DECLARE @V_NEW_VRMAXCONSDIAD       double precision;
  DECLARE @V_NEW_VRMAXCONSMESC       double precision;
  DECLARE @V_NEW_VRMAXCONSMESD       double precision;

  DECLARE @V_OLD_CDFILIAL            varchar(4);
  DECLARE @V_OLD_CDLOJA              VARCHAR(15);
  DECLARE @V_OLD_CDTIPOCONS          varchar(2);
  DECLARE @V_OLD_VRMAXCONSDIAC       double precision;  
  DECLARE @V_OLD_VRMAXCONSDIAD       double precision;
  DECLARE @V_OLD_VRMAXCONSMESC       double precision;
  DECLARE @V_OLD_VRMAXCONSMESD       double precision;

BEGIN
/* IDTIPOOPER - '1' - Inclusão, '2' - Alteração, '3' - Exclusão*/

   SET @V_DESC   = '';
   SET @V_ITPESQ     = '';
   SET @V_NMOPERADOR = '';
   SET @V_IDTIPOOPER = '';

   EXEC NMUSUARIO @P_NMUSUARIO = @V_NMOPERADOR OUTPUT;
   SET @V_NMOPERADOR = ISNULL(@V_NMOPERADOR, ' ');

   EXEC WEBTUNNING  @P_DSREMOTEADDR = @V_DSREMOTEADDR OUTPUT,
                    @P_DSREMOTEHOST = @V_DSREMOTEHOST OUTPUT,
                    @P_DSSERVERRDM  = @V_DSSERVERRDM OUTPUT,
                    @P_DSSERVMETHOD = @V_DSSERVMETHOD OUTPUT,
                    @P_DSREMOTEOPER = @V_DSREMOTEOPER OUTPUT;

   DECLARE C_CURSOR CURSOR FOR
     SELECT
     'D'   ID_COMANDO,          
           NULL,NULL,NULL,NULL,NULL,NULL,NULL,
           CDFILIAL,
           CDLOJA,
           CDTIPOCONS,		   
           VRMAXCONSDIAC,
		   VRMAXCONSDIAD,
		   VRMAXCONSMESC,
		   VRMAXCONSMESD

     FROM
           DELETED
     WHERE     
           CDFILIAL + CDLOJA + CDTIPOCONS NOT IN (SELECT CDFILIAL + CDLOJA+ CDTIPOCONS FROM INSERTED)
     UNION ALL

     SELECT
     'U'   IDCOMANDO,
           INSERTED.CDFILIAL,
		   INSERTED.CDLOJA,
           INSERTED.CDTIPOCONS,
           INSERTED.VRMAXCONSDIAC,
		   INSERTED.VRMAXCONSDIAD,
		   INSERTED.VRMAXCONSMESC,
		   INSERTED.VRMAXCONSMESD,
           DELETED.CDFILIAL,
           DELETED.CDLOJA,
           DELETED.CDTIPOCONS,
           DELETED.VRMAXCONSDIAC,
		   DELETED.VRMAXCONSDIAD,
		   DELETED.VRMAXCONSMESC,
		   DELETED.VRMAXCONSMESD
     FROM
           INSERTED,
           DELETED
     WHERE     
        INSERTED.CDFILIAL   = DELETED.CDFILIAL    AND
		INSERTED.CDLOJA     = DELETED.CDLOJA      AND
	    INSERTED.CDTIPOCONS = DELETED.CDTIPOCONS  
     UNION ALL

     SELECT
     'I'   IDCOMANDO,
           INSERTED.CDFILIAL,
		   INSERTED.CDLOJA,
           INSERTED.CDTIPOCONS,
           INSERTED.VRMAXCONSDIAC,
		   INSERTED.VRMAXCONSDIAD,
		   INSERTED.VRMAXCONSMESC,
		   INSERTED.VRMAXCONSMESD,
	   NULL,NULL,NULL,NULL,NULL,NULL,NULL
     FROM
           INSERTED
     WHERE     
          CDFILIAL + CDLOJA + CDTIPOCONS NOT IN (SELECT CDFILIAL + CDLOJA + CDTIPOCONS FROM DELETED)


     OPEN C_CURSOR;
     FETCH NEXT FROM C_CURSOR INTO @V_ID_COMANDO,
                                   @V_NEW_CDFILIAL,@V_NEW_CDLOJA,@V_NEW_CDTIPOCONS,
								   @V_NEW_VRMAXCONSDIAC, @V_NEW_VRMAXCONSDIAD, @V_NEW_VRMAXCONSMESC, @V_NEW_VRMAXCONSMESD,
                                   @V_OLD_CDFILIAL,@V_OLD_CDLOJA,@V_OLD_CDTIPOCONS,
								   @V_OLD_VRMAXCONSDIAC, @V_OLD_VRMAXCONSDIAD, @V_OLD_VRMAXCONSMESC, @V_OLD_VRMAXCONSMESD

    WHILE (@@FETCH_STATUS = 0) BEGIN
    
       IF @V_ID_COMANDO= 'U' /*UPDATE*/ BEGIN
		IF ISNULL(@V_NEW_CDFILIAL,'NULL') <> ISNULL(@V_OLD_CDFILIAL,'NULL') BEGIN
			SET @V_DESC   = 'Filial ' + ISNULL(@V_NEW_CDFILIAL,'');
		END;
		IF ISNULL(@V_NEW_CDLOJA,'NULL') <> ISNULL(@V_OLD_CDLOJA,'NULL') BEGIN
			SET @V_DESC   = ' Loja ' + ISNULL(@V_NEW_CDLOJA,'');
		END;
		IF ISNULL(@V_NEW_CDTIPOCONS,'NULL') <> ISNULL(@V_OLD_CDTIPOCONS,'NULL') BEGIN
            SET @V_DESC  = ISNULL(@V_DESC,'') + ' Tipo Cons ' + ISNULL(@V_NEW_CDTIPOCONS,'');
		END;
		IF ISNULL(CONVERT(VARCHAR,@V_NEW_VRMAXCONSDIAD),'NULL') <> ISNULL(CONVERT(VARCHAR,@V_OLD_VRMAXCONSDIAD),'NULL') BEGIN
            SET @V_DESC  = ISNULL(@V_DESC,'') + ' Vr max dia Deb ' + ISNULL(CONVERT(VARCHAR,@V_NEW_VRMAXCONSDIAD),'');
		END;
		IF ISNULL(CONVERT(VARCHAR,@V_NEW_VRMAXCONSDIAC),'NULL') <> ISNULL(CONVERT(VARCHAR,@V_OLD_VRMAXCONSDIAC),'NULL') BEGIN
            SET @V_DESC  = ISNULL(@V_DESC,'') + ' Vr max dia Cred ' + ISNULL(CONVERT(VARCHAR,@V_NEW_VRMAXCONSDIAC),'');
		END;
		IF ISNULL(CONVERT(VARCHAR,@V_NEW_VRMAXCONSMESD),'NULL') <> ISNULL(CONVERT(VARCHAR,@V_OLD_VRMAXCONSMESD),'NULL') BEGIN
            SET @V_DESC  = ISNULL(@V_DESC,'') + ' Vr max mes Deb ' + ISNULL(CONVERT(VARCHAR,@V_NEW_VRMAXCONSMESD),'');
		END;
		IF ISNULL(CONVERT(VARCHAR,@V_NEW_VRMAXCONSMESC),'NULL') <> ISNULL(CONVERT(VARCHAR,@V_OLD_VRMAXCONSMESC),'NULL') BEGIN
            SET @V_DESC  = ISNULL(@V_DESC,'') + ' Vr max mes Cred ' + ISNULL(CONVERT(VARCHAR,@V_NEW_VRMAXCONSMESC),'');
		END;
          SET @V_IDTIPOOPER = '2';
       END;
       IF @V_ID_COMANDO= 'I' /*INSERÇÃO*/ BEGIN
          SET @V_DESC   = 'Filial '           + ISNULL(@V_NEW_CDFILIAL,''    )  + ' ' +
		              'Loja '                  + ISNULL(@V_NEW_CDLOJA,''       )  + ' ' +
                      'Tipo Cons '     + ISNULL(@V_NEW_CDTIPOCONS,''  )  + ' ' +
                      'Vr max dia Deb '  + ISNULL(CONVERT(VARCHAR,@V_NEW_VRMAXCONSDIAD),'')+' '+
					  'Vr max dia Cred '  + ISNULL(CONVERT(VARCHAR,@V_NEW_VRMAXCONSDIAC),'')+' '+
					  'Vr max mes Deb '  + ISNULL(CONVERT(VARCHAR,@V_NEW_VRMAXCONSMESD),'')+' '+
					  'Vr max mes Cred '  + ISNULL(CONVERT(VARCHAR,@V_NEW_VRMAXCONSMESC),'');
          SET @V_IDTIPOOPER = '1';
       END;
       IF @V_ID_COMANDO= 'D' /*DELEÇÃO*/  BEGIN
          SET @V_DESC   = 'Filial '           + ISNULL(@V_OLD_CDFILIAL,''    )  + ' ' +
		              'Loja '                  + ISNULL(@V_OLD_CDLOJA,''       )  + ' ' +
                      'Tipo Cons '     + ISNULL(@V_OLD_CDTIPOCONS,''  )  + ' ' +
                      'Vr max dia Deb '  + ISNULL(CONVERT(VARCHAR,@V_OLD_VRMAXCONSDIAD),'')+' '+
					  'Vr max dia Cred '  + ISNULL(CONVERT(VARCHAR,@V_OLD_VRMAXCONSDIAC),'')+' '+
					  'Vr max mes Deb '  + ISNULL(CONVERT(VARCHAR,@V_OLD_VRMAXCONSMESD),'')+' '+
					  'Vr max mes Cred '  + ISNULL(CONVERT(VARCHAR,@V_OLD_VRMAXCONSMESC),'');
          SET @V_IDTIPOOPER = '3';
       END;
       IF  (@V_IDTIPOOPER <> '') BEGIN
          IF @V_DESC IS NOT NULL BEGIN
             SET @V_ITPESQ =  ISNULL(@V_NEW_CDFILIAL + @V_NEW_CDLOJA + @V_NEW_CDTIPOCONS ,@V_OLD_CDFILIAL + @V_OLD_CDLOJA + @V_OLD_CDTIPOCONS );
             SET @V_DESC = SUBSTRING(@V_DESC, 1, 300);
             INSERT INTO LOGOPERAVND
                    (NMTABELAVND, DTOPERACAOVND, NMRESPOPERVND, IDTIPOOPERVND, DSOPERACAOVND,
                     CDPESQITEMVND, DSRDMUSUOPERVND, DSENDUSUOPERVND, DSNMUSUOPERVND, DSMETUSUOPERVND,
                     CDORESPOPERVND, CDFILLOGOPERVND)
             VALUES ('TIPOCONSLOJA',GETDATE(), ISNULL(@V_NMOPERADOR,' '), @V_IDTIPOOPER, @V_DESC, @V_ITPESQ,
                     @V_DSSERVERRDM, @V_DSREMOTEADDR, @V_DSREMOTEHOST, @V_DSSERVMETHOD, @V_DSREMOTEOPER,
                     ISNULL(@V_NEW_CDFILIAL,@V_OLD_CDFILIAL));
          END;
       END;
       FETCH NEXT FROM C_CURSOR INTO @V_ID_COMANDO,
                                     @V_NEW_CDFILIAL,@V_NEW_CDLOJA,@V_NEW_CDTIPOCONS,
								     @V_NEW_VRMAXCONSDIAC, @V_NEW_VRMAXCONSDIAD, @V_NEW_VRMAXCONSMESC, @V_NEW_VRMAXCONSMESD,
                                     @V_OLD_CDFILIAL,@V_OLD_CDLOJA,@V_OLD_CDTIPOCONS,
								     @V_OLD_VRMAXCONSDIAC, @V_OLD_VRMAXCONSDIAD, @V_OLD_VRMAXCONSMESC, @V_OLD_VRMAXCONSMESD

    SET @V_DESC   = '';
    END;
    CLOSE      C_CURSOR;
    DEALLOCATE C_CURSOR;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'LOG_VND_TIPOCONSLOJA','6.02.000';
GO 