Fórum Atualizar tabela através de tigger #54553

set term ^

CREATE TRIGGER NOME_TRIGGER FOR Movimento_Produtos 
  AFTER UPDATE POSITION AS 
BEGIN 
  update produtos set qtde = qtde - old.saldo + new.saldo
  where codigo = new.codigo;
END; 

AS
DECLARE VARIABLE NOME VARCHAR(40);
DECLARE VARIABLE SALDOOLD DOUBLE PRECISION;
DECLARE VARIABLE SALDO DOUBLE PRECISION;
DECLARE VARIABLE TOTAL DOUBLE PRECISION;
begin
  if (new.codnfm <> old.codnfm) then
  begin
    update ESAPRO SET QTDPRO = QTDPRO - old.QTDNFM
    where CODPRO = old.CODNFM;
    Select qtdpro from esapro where CODPRO = old.CODNFM INTO :SALDOOLD;
    insert into ESAHIP (CLIHIP, CODHIP, DIGHIP, DATHIP, OPEHIP, TOPHIP, DOCHIP, QTDHIP, SALHIP, NOMHIP, SERHIP)
    values (ESANFM.FORNFM, old.CODNFM, old.DIGNFM, ´NOW´, ´COM´, ´EC´, ESANFM.NUMNFM, OLD.QTDNFM, :SALDOOLD, ´TROCA DE COD PROD NA NF ENTRADA´, ESANFM.SERNFM);

    update ESAPRO SET QTDPRO = QTDPRO + new.QTDNFM
    where CODPRO = new.CODNFM;
    Select qtdpro from esapro where CODPRO = new.CODNFM INTO :SALDO;
    Select NOMCLI FROM ESACLI where CODCLI = ESANFM.FORNFM INTO :NOME;
    insert into ESAHIP (CLIHIP, CODHIP, DIGHIP, DATHIP, OPEHIP, TOPHIP, DOCHIP, QTDHIP, SALHIP, NOMHIP, SERHIP)
    values (ESANFM.FORNFM, new.CODNFM, new.DIGNFM, ´NOW´, ´COM´, ´EA´, ESANFM.NUMNFM, NEW.QTDNFM, :SALDO, :NOME, ESANFM.SERNFM);
  end
  else
  begin
    if (OLD.qtdnfm <> NEW.qtdnfm) then
    begin
     if (OLD.qtdnfm < NEW.qtdnfm) then
     begin
      TOTAL= NEW.qtdnfm - OLD.qtdnfm;
      update ESAPRO SET QTDPRO = QTDPRO + :TOTAL
      where CODPRO = ESANFM.CODNFM;
      Select qtdpro from esapro where CODPRO = ESANFM.CODNFM INTO :SALDO;
      insert into ESAHIP (CLIHIP, CODHIP, DIGHIP, DATHIP, OPEHIP, TOPHIP, DOCHIP, QTDHIP, SALHIP, NOMHIP, SERHIP)
      values (ESANFM.FORNFM, ESANFM.CODNFM, ESANFM.DIGNFM, ´NOW´, ´COM´, ´EA´, ESANFM.NUMNFM, :TOTAL, :SALDO, ´ACRESCIMO DA QTDE DO PROD DA NF ENTRADA´, ESANFM.SERNFM);
     end
     else
     begin
      TOTAL= OLD.qtdnfm - NEW.qtdnfm;
      update ESAPRO SET QTDPRO = QTDPRO - :TOTAL
      where CODPRO = ESANFM.CODNFM;
      Select qtdpro from esapro where CODPRO = ESANFM.CODNFM INTO :SALDO;
      insert into ESAHIP (CLIHIP, CODHIP, DIGHIP, DATHIP, OPEHIP, TOPHIP, DOCHIP, QTDHIP, SALHIP, NOMHIP, SERHIP)
      values (ESANFM.FORNFM, ESANFM.CODNFM, ESANFM.DIGNFM, ´NOW´, ´COM´, ´EA´, ESANFM.NUMNFM, :TOTAL, :SALDO, ´REDUÇAO NA QTDE DO PROD DA NF ENTRADA´, ESANFM.SERNFM);
     end
    end
  end
end