SET TERM ^ ;

CREATE OR ALTER trigger tbprogramacao_setor_b01u for tbprogramacao_setor
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE ID_PROGRAMACAO INTEGER;
BEGIN

  HISTORICO = '';
  ID_PROGRAMACAO = (EXTRACT(YEAR FROM CAST(NEW.DATA AS DATE))-2000)||FN_LPAD(EXTRACT(MONTH FROM CAST(NEW.DATA AS DATE)), 2, '0')||FN_LPAD(EXTRACT(DAY FROM CAST(NEW.DATA AS DATE)), 2, '0')
                        ||FN_LPAD(NEW.ESTABELECIMENTO_CODIGO, 3, '0');

  IF (OLD.PREVISAO <> NEW.PREVISAO) THEN
       BEGIN
       HISTORICO = HISTORICO||' GP: '||NEW.ESTEIRA_CODIGO||' - '||(SELECT FIRST 1 GP.DESCRICAO FROM TBESTEIRA GP WHERE GP.CODIGO = NEW.ESTEIRA_CODIGO)
       ||': <strong>QTD: '||CAST(OLD.PREVISAO AS NUMERIC(10,5))||'->'||CAST(NEW.PREVISAO AS NUMERIC(10,5))||'</strong>';
       END

  IF(HISTORICO <> '') THEN
  BEGIN
    HISTORICO = ' REG. ALTERADO: ESTAB: '||NEW.ESTABELECIMENTO_CODIGO||', PERFIL:'||(SELECT FIRST 1 GP.PERFIL FROM TBESTEIRA GP WHERE GP.CODIGO = NEW.ESTEIRA_CODIGO)||', DATA: '
    ||FORMATDATE(NEW.DATA)||' '||HISTORICO;
--    EXCEPTION "..." ''||HISTORICO;
    EXECUTE PROCEDURE SPI_HISTORICO('TBPROGRAMACAO_SETOR', ID_PROGRAMACAO, HISTORICO);
  END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbprogramacao_setor_hora_b01u for tbprogramacao_setor_horario
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE ID_PROGRAMACAO INTEGER;
BEGIN

  HISTORICO = '';
  ID_PROGRAMACAO = (EXTRACT(YEAR FROM CAST(NEW.DATA AS DATE))-2000)||FN_LPAD(EXTRACT(MONTH FROM CAST(NEW.DATA AS DATE)), 2, '0')||FN_LPAD(EXTRACT(DAY FROM CAST(NEW.DATA AS DATE)), 2, '0')
                        ||(SELECT FIRST 1 FN_LPAD(GP.ESTABELECIMENTO_CODIGO, 3, '0') FROM TBPROGRAMACAO_SETOR GP WHERE GP.ESTEIRA_CODIGO = NEW.GP_ID);

  IF (OLD.PREVISAO <> NEW.PREVISAO) THEN
       BEGIN
       HISTORICO = HISTORICO||', GP: '||NEW.GP_ID||' - '||(SELECT FIRST 1 GP.DESCRICAO FROM TBESTEIRA GP WHERE GP.CODIGO = NEW.GP_ID)
       ||' HORÁRIO: '||(SELECT FIRST 1 FORMATTIME(H.HORA_1)||' - '||FORMATTIME(H.HORA_2) FROM TBHORARIO H WHERE H.ID = NEW.HORARIO_ID)
       ||': <strong>QTD: '||CAST(OLD.PREVISAO AS NUMERIC(10,5))||'->'||CAST(NEW.PREVISAO AS NUMERIC(10,5))||'</strong>';
       END

  IF(HISTORICO <> '') THEN
  BEGIN

    HISTORICO = ' PREV/HORARIO ALTERADA: ESTAB:'||(SELECT FIRST 1 GP.ESTABELECIMENTO_CODIGO FROM TBPROGRAMACAO_SETOR GP WHERE GP.ESTEIRA_CODIGO = NEW.GP_ID)
    ||', PERFIL: '||(SELECT FIRST 1 GP.PERFIL FROM TBESTEIRA GP WHERE GP.CODIGO = NEW.GP_ID)||', DATA: '||FORMATDATE(NEW.DATA)||HISTORICO;
    EXECUTE PROCEDURE SPI_HISTORICO('TBPROGRAMACAO_SETOR', ID_PROGRAMACAO, HISTORICO);
  END
END
^

SET TERM ; ^

