SET TERM ^ ;
CREATE OR ALTER TRIGGER TBCOMISSAO_B02U FOR TBCOMISSAO
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
/*TRIGGER PARA CALCULO DE ACRESCIMOS/DESCONTOS EM COMISSOES*/
  IF(COALESCE(NEW.VALOR_DESCONTO,0.00)  <> COALESCE(OLD.VALOR_DESCONTO,0.00)
  OR COALESCE(NEW.VALOR_ACRESCIMO,0.00) <> COALESCE(OLD.VALOR_ACRESCIMO,0.00))THEN
  BEGIN

     IF(NEW.VALOR_ACRESCIMO > 0)THEN
     BEGIN
        NEW.SALDO                = OLD.VALOR;
        NEW.PERCENTUAL_ACRESCIMO = CAST(((CAST(NEW.VALOR_ACRESCIMO AS DOUBLE PRECISION)*100.00)/CAST(OLD.VALOR AS DOUBLE PRECISION)) AS NUMERIC(18,5));
        NEW.SALDO                = CAST(NEW.SALDO AS DOUBLE PRECISION) + CAST(NEW.VALOR_ACRESCIMO AS DOUBLE PRECISION);
     END ELSE
     BEGIN
        NEW.SALDO                = OLD.SALDO - COALESCE(OLD.VALOR_ACRESCIMO,0.00);
        NEW.PERCENTUAL_ACRESCIMO = 0.00000;
     END

     IF(NEW.VALOR_DESCONTO > 0)THEN
     BEGIN
        NEW.SALDO                = OLD.VALOR;
        NEW.PERCENTUAL_DESCONTO  = CAST(((CAST(NEW.VALOR_DESCONTO AS DOUBLE PRECISION)*100.00)/CAST(OLD.VALOR AS DOUBLE PRECISION)) AS NUMERIC(18,5));
        NEW.SALDO                = CAST(NEW.SALDO AS DOUBLE PRECISION) - CAST(NEW.VALOR_DESCONTO AS DOUBLE PRECISION);
     END ELSE
     BEGIN
        NEW.SALDO                = OLD.SALDO + COALESCE(OLD.VALOR_DESCONTO,0.00);
        IF(NEW.SALDO > NEW.VALOR AND COALESCE(NEW.VALOR_ACRESCIMO,0.00) = 0)THEN
        BEGIN
            NEW.SALDO            = NEW.VALOR;
        END
        NEW.PERCENTUAL_DESCONTO  = 0.00000;
     END
  END

  IF(NEW.VALOR_DESCONTO > (COALESCE(NEW.VALOR_ACRESCIMO,0.00)+NEW.VALOR)) THEN
  BEGIN
     EXCEPTION "..." 'VALOR DESCONTADO (R$ '||FN_FORMAT_NUMBER2(NEW.VALOR_DESCONTO,2)||') ESTA ACIMA DO VALOR DA COMISSAO (R$ '||FN_FORMAT_NUMBER2((COALESCE(NEW.VALOR_ACRESCIMO,0.00)+NEW.VALOR),2)||'). <BR> OPERACAO CANCELADA!';
  END
END

^ SET TERM ; ^