ALTER TABLE TBCOLETOR_WEB_ITENS
    ADD AVULSO INTEGER  DEFAULT 0;

ALTER TABLE TBCOLETOR_WEB_ITENS
    ADD DATAHORA TIMESTAMP DEFAULT 'NOW';

/****************************************************************************/
/****************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbcoletor_web_b01u for tbcoletor_web
active before update position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE USUARIO VARCHAR(20);
DECLARE VARIABLE STATUS INTEGER;
BEGIN

    :STATUS = COALESCE((SELECT FIRST 1 C.EXECUTADO FROM TBCOLETOR_COMPARATIVO_ITENS CC INNER JOIN TBCOLETOR_COMPARATIVO C ON C.ID = CC.COLETOR_COMPARATIVO_ID WHERE CC.COLETOR_WEB_ID = OLD.ID AND C.EXECUTADO = 1), 0);

    IF (COALESCE(STATUS, 0) = 1) THEN
    BEGIN
        EXCEPTION "..." 'SALDO DO INVENTARIO JA AJUSTADO! ALTERACAO NAO AUTORIZADA!';
    END



   HISTORICO = '';

   IF (OLD.descricao <> NEW.descricao) THEN
   BEGIN
        HISTORICO = HISTORICO||' DESC:'||OLD.descricao||'->'||NEW.descricao;
   END
 
   IF (HISTORICO <> '') THEN
   BEGIN
       USUARIO = (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION);

       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBCOLETOR_WEB', OLD.id, :USUARIO,
       (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
  end
end
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbcoletor_web_b01d for tbcoletor_web
active before delete position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE USUARIO VARCHAR(20);
DECLARE VARIABLE STATUS INTEGER;
BEGIN

   :STATUS = COALESCE((SELECT FIRST 1 C.EXECUTADO FROM TBCOLETOR_COMPARATIVO_ITENS CC INNER JOIN TBCOLETOR_COMPARATIVO C ON C.ID = CC.COLETOR_COMPARATIVO_ID WHERE CC.COLETOR_WEB_ID = OLD.ID AND C.EXECUTADO = 1), 0);

   IF (COALESCE(STATUS, 0) = 1) THEN
   BEGIN
       EXCEPTION "..." 'SALDO DO INVENTARIO JA AJUSTADO! EXCLUSAO NAO AUTORIZADA!';
   END



   HISTORICO = 'Registro excluido.';
 
   IF (HISTORICO <> '') THEN
   BEGIN
       USUARIO = (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION);

       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBCOLETOR_WEB', OLD.id, :USUARIO,
       (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
  end
end
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbcoletor_web_b02d for tbcoletor_web
active before delete position 2
AS
BEGIN
    DELETE FROM TBCOLETOR_WEB_ITENS A WHERE A.COLETOR_WEB_ID = OLD.ID;
END
^

SET TERM ; ^

/****************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbcoletor_web_itens_b01u for tbcoletor_web_itens
active before update position 0
AS
DECLARE VARIABLE STATUS INTEGER;
BEGIN

    :STATUS = COALESCE((SELECT FIRST 1 C.EXECUTADO FROM TBCOLETOR_COMPARATIVO_ITENS CC INNER JOIN TBCOLETOR_COMPARATIVO C ON C.ID = CC.COLETOR_COMPARATIVO_ID WHERE CC.COLETOR_WEB_ID = OLD.COLETOR_WEB_ID AND C.EXECUTADO = 1), 0);
    
    IF (COALESCE(STATUS, 0) = 1) THEN
    BEGIN
        EXCEPTION "..." 'SALDO DO INVENTARIO JA AJUSTADO! ALTERACAO NAO AUTORIZADA!';
    END

END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbcoletor_web_itens_b01d for tbcoletor_web_itens
active before delete position 0
AS
DECLARE VARIABLE STATUS INTEGER;
BEGIN

    :STATUS = COALESCE((SELECT FIRST 1 C.EXECUTADO FROM TBCOLETOR_COMPARATIVO_ITENS CC INNER JOIN TBCOLETOR_COMPARATIVO C ON C.ID = CC.COLETOR_COMPARATIVO_ID WHERE CC.COLETOR_WEB_ID = OLD.COLETOR_WEB_ID AND C.EXECUTADO = 1), 0);

    IF (COALESCE(STATUS, 0) = 1) THEN
    BEGIN
        EXCEPTION "..." 'SALDO DO INVENTARIO JA AJUSTADO! EXCLUSAO NAO AUTORIZADA!';
    END

END
^

SET TERM ; ^

