-- TBPARAEMTRO

INSERT INTO TBPARAMETRO (ID, TABELA, CONTROLE, CODIGO, DESCRICAO, VALOR, OBSERVACAO, GRUPO, USUARIO_ID) 
	VALUES (770, 'SISTEMA', 1055, 'SETOR_SUPORTE_ERRO_SISTEMA', 'SETOR DE SUPORTE PARA ERROS NO SISTEMA', '0', 'INFORME O ID DO SETOR DE SUPORTE (PCM) QUE RECEBERÁ OS CHAMADOS DO SISTEMA EM CASO DE ERROS. A ABERTURA DE CHAMADOS DE ERRO DO SISTEMA SERÁ DIRECIONADA PARA O SETOR INFORMADO. (EX: 1)', 'PCM', 788);

-----------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------
/******************************************************************************/
/****                                Tables                                ****/ -- TBPCM_SETOR
/******************************************************************************/

CREATE GENERATOR GTBPCM_SETOR;

CREATE TABLE TBPCM_SETOR (
    ID         INTEGER NOT NULL,
    STATUS     CHAR(1),
    DESCRICAO  VARCHAR(100),
    CCUSTO     VARCHAR(8)
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBPCM_SETOR ADD CONSTRAINT PK_TBPCM_SETOR PRIMARY KEY (ID);

/******************************************************************************/
/****                                Tables                                ****/ -- TBPCM_SETOR_OPERADOR
/******************************************************************************/


CREATE GENERATOR GTBPCM_SETOR_OPERADOR;

CREATE TABLE TBPCM_SETOR_OPERADOR (
    ID             INTEGER NOT NULL,
    SETOR_ID       INTEGER,
    USUARIO_ID     INTEGER,
    TIPO_OPERADOR  CHAR(1)
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBPCM_SETOR_OPERADOR ADD CONSTRAINT PK_TBPCM_SETOR_OPERADOR PRIMARY KEY (ID);

/******************************************************************************/
/****                         Fields descriptions                          ****/
/******************************************************************************/

COMMENT ON COLUMN TBPCM_SETOR_OPERADOR.TIPO_OPERADOR IS 
'1 - OPERADOR;
2 - GESTOR;';

/******************************************************************************/
/****                                Tables                                ****/ -- TBPCM_CHAMADO_TIPO
/******************************************************************************/


CREATE GENERATOR GTBPCM_CHAMADO_TIPO;

CREATE TABLE TBPCM_CHAMADO_TIPO (
    ID         INTEGER NOT NULL,
    STATUS     CHAR(1),
    DESCRICAO  VARCHAR(50),
    SETOR_ID   INTEGER
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBPCM_CHAMADO_TIPO ADD CONSTRAINT PK_TBPCM_CHAMADO_TIPO PRIMARY KEY (ID);

/******************************************************************************/
/****                                Tables                                ****/ -- TBPCM_CHAMADO_CATEGORIA
/******************************************************************************/


CREATE GENERATOR GTBPCM_CHAMADO_CATEGORIA;

CREATE TABLE TBPCM_CHAMADO_CATEGORIA (
    ID                  INTEGER NOT NULL,
    CHAMADO_TIPO_ID     INTEGER,
    STATUS              CHAR(1),
    DESCRICAO           VARCHAR(50),
    TEMPO_RESPOSTA      TIME,
    TEMPO_SOLUCAO       TIME,
    REQUER_EQUIPAMENTO  CHAR(1)
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBPCM_CHAMADO_CATEGORIA ADD CONSTRAINT PK_TBPCM_CHAMADO_CATEGORIA PRIMARY KEY (ID);

/******************************************************************************/
/****                         Fields descriptions                          ****/
/******************************************************************************/

COMMENT ON COLUMN TBPCM_CHAMADO_CATEGORIA.TEMPO_RESPOSTA IS 
'Tempo Estimado de Resposta';

COMMENT ON COLUMN TBPCM_CHAMADO_CATEGORIA.TEMPO_SOLUCAO IS 
'Tempo Estimado de Solução';

COMMENT ON COLUMN TBPCM_CHAMADO_CATEGORIA.REQUER_EQUIPAMENTO IS 
'0 - NÃO;
1 - SIM;';

/******************************************************************************/
/****                                Tables                                ****/ -- TBPCM_CHAMADO_SUBCATEGORIA
/******************************************************************************/


CREATE GENERATOR GTBPCM_CHAMADO_SUBCATEGORIA;

CREATE TABLE TBPCM_CHAMADO_SUBCATEGORIA (
    ID                    INTEGER NOT NULL,
    CHAMADO_CATEGORIA_ID  INTEGER,
    DESCRICAO             VARCHAR(50)
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBPCM_CHAMADO_SUBCATEGORIA ADD CONSTRAINT PK_TBPCM_CHAMADO_SUBCATEGORIA PRIMARY KEY (ID);

/******************************************************************************/
/****                                Tables                                ****/ -- TBPCM_REGISTRO_CHAMADO
/******************************************************************************/


CREATE GENERATOR GTBPCM_REGISTRO_CHAMADO;

CREATE TABLE TBPCM_REGISTRO_CHAMADO (
    ID                       INTEGER NOT NULL,
    STATUS                   CHAR(1),
    USUARIO_ID               INTEGER,
    SETOR_ID                 INTEGER,
    SOLICITANTE_NOME         VARCHAR(100),
    SOLICITATNE_SETOR        VARCHAR(50),
    SOLICITANTE_CARGO        VARCHAR(50),
    SOLICITANTE_CONTATO      BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    CHAMADO_TIPO_ID          INTEGER,
    CHAMADO_CATEGORIA_ID     INTEGER,
    CHAMADO_SUBCATEGORIA_ID  INTEGER,
    PRIORIDADE               CHAR(1),
    URGENCIA                 CHAR(1),
    DESCRICAO                BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    EQUIPAMENTO_ID           INTEGER,
    DATAHORA_REG             TIMESTAMP,
    DATAHORA_PREVISAO        TIMESTAMP
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBPCM_REGISTRO_CHAMADO ADD CONSTRAINT PK_TBPCM_REGISTRO_CHAMADO PRIMARY KEY (ID);

/******************************************************************************/
/****                         Fields descriptions                          ****/
/******************************************************************************/

COMMENT ON COLUMN TBPCM_REGISTRO_CHAMADO.STATUS IS 
'0 - ABERTO;
1 - EM ATENDIMENTO;
2 - AGUARDANDO INFORMAÇÕES DE TERCEIROS;
3 - AGUARDANDO INFORMAÇÕES DO USUÁRIO;
4 - ATRASADO;
5 - ATENDIDO;
6 - FECHADO;';

COMMENT ON COLUMN TBPCM_REGISTRO_CHAMADO.PRIORIDADE IS
'1 - BAIXA;
2 - MEDIA;
3 - ALTA;';

/******************************************************************************/
/****                                Tables                                ****/ -- TBPCM_REGISTRO_CHAMADO_ITEM
/******************************************************************************/


CREATE GENERATOR GTBPCM_REGISTRO_CHAMADO_ITEM;

CREATE TABLE TBPCM_REGISTRO_CHAMADO_ITEM (
    ID                   INTEGER NOT NULL,
    REGISTRO_CHAMADO_ID  INTEGER,
    STATUS               CHAR(1),
    DATAHORA_INI         TIMESTAMP,
    DATAHORA_FIN         TIMESTAMP,
    SETOR_OPERADOR_ID    INTEGER,
    OBSERVACAO           BLOB SUB_TYPE 1 SEGMENT SIZE 80
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBPCM_REGISTRO_CHAMADO_ITEM ADD CONSTRAINT PK_TBPCM_REGISTRO_CHAMADO_ITEM PRIMARY KEY (ID);

/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/


CREATE GENERATOR GTBPCM_REGISTRO_CHAMADO_MSG;

CREATE TABLE TBPCM_REGISTRO_CHAMADO_MSG (
    ID                   INTEGER NOT NULL,
    REGISTRO_CHAMADO_ID  INTEGER,
    MENSAGEM             BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    DATAHORA_ENV         TIMESTAMP,
    USUARIO_ID           INTEGER
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBPCM_REGISTRO_CHAMADO_MSG ADD CONSTRAINT PK_TBPCM_REGISTRO_CHAMADO_MSG PRIMARY KEY (ID);

/******************************************************************************/
/****                                Tables                                ****/ -- TBPCM_REGISTRO_CHAMADO_AVAL
/******************************************************************************/


CREATE GENERATOR GTBPCM_REGISTRO_CHAMADO_AVAL;

CREATE TABLE TBPCM_REGISTRO_CHAMADO_AVAL (
    ID                   INTEGER NOT NULL,
    REGISTRO_CHAMADO_ID  INTEGER,
    USUARIO_ID           INTEGER,
    CAMPO01              CHAR(1),
    CAMPO02              CHAR(1),
    CAMPO03              CHAR(1),
    CAMPO04              CHAR(1),
    CAMPO05              CHAR(1),
    OBSERVACAO           BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    DATAHORA_REG         TIMESTAMP
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBPCM_REGISTRO_CHAMADO_AVAL ADD CONSTRAINT PK_TBPCM_REGISTRO_CHAMADO_AVAL PRIMARY KEY (ID);

-----------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------

/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBPCM_SETOR_B01D */
CREATE OR ALTER TRIGGER TTBPCM_SETOR_B01D FOR TBPCM_SETOR
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO EXCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_SETOR', OLD.ID, HISTORICO);
    END
END
^


/* Trigger: TTBPCM_SETOR_B01I */
CREATE OR ALTER TRIGGER TTBPCM_SETOR_B01I FOR TBPCM_SETOR
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBPCM_SETOR, 1);
END
^


/* Trigger: TTBPCM_SETOR_B01U */
CREATE OR ALTER TRIGGER TTBPCM_SETOR_B01U FOR TBPCM_SETOR
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_CCUSTO VARCHAR(500);   
DECLARE VARIABLE NEW_CCUSTO VARCHAR(500);
BEGIN

    HISTORICO = '';
    OLD_CCUSTO = COALESCE((SELECT FIRST 1 FN_MASK_V2(A.CODIGO, 'CC')||' - '||A.DESCRICAO FROM TBCENTRO_DE_CUSTO A WHERE A.CODIGO = OLD.CCUSTO), '');
    NEW_CCUSTO = COALESCE((SELECT FIRST 1 FN_MASK_V2(A.CODIGO, 'CC')||' - '||A.DESCRICAO FROM TBCENTRO_DE_CUSTO A WHERE A.CODIGO = NEW.CCUSTO), '');

    IF (OLD.STATUS <> NEW.STATUS) THEN
    BEGIN
        HISTORICO = HISTORICO||' STATUS:'||OLD.STATUS||'->'||NEW.STATUS;
    END
    
    IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' DESC:'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END

    IF (OLD.CCUSTO <> NEW.CCUSTO) THEN
    BEGIN
        HISTORICO = HISTORICO||' DESC:'||OLD_CCUSTO||'->'||NEW_CCUSTO;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'REGISTRO ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_SETOR', OLD.ID, FN_ELIPSES(HISTORICO, 500));
    END
END
^


/* Trigger: TTBPCM_SETOR_B02D */
CREATE OR ALTER TRIGGER TTBPCM_SETOR_B02D FOR TBPCM_SETOR
ACTIVE BEFORE DELETE POSITION 1
AS
BEGIN
    DELETE FROM TBPCM_SETOR_OPERADOR A WHERE A.SETOR_ID = OLD.ID;
END
^


/* Trigger: TTBPCM_SETOR_B02I */
CREATE OR ALTER TRIGGER TTBPCM_SETOR_B02I FOR TBPCM_SETOR
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO INCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_SETOR', NEW.ID, HISTORICO);
    END
END
^

SET TERM ; ^

/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBPCM_SETOR_OPERADOR_B01D */
CREATE OR ALTER TRIGGER TTBPCM_SETOR_OPERADOR_B01D FOR TBPCM_SETOR_OPERADOR
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE USUARIO VARCHAR(500);
BEGIN
    
    HISTORICO = '';
    USUARIO = COALESCE((SELECT FIRST 1 A.USUARIO FROM TBUSUARIO A WHERE A.CODIGO = OLD.USUARIO_ID), '');

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'OPERADOR:'||USUARIO||' EXCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_SETOR', OLD.SETOR_ID, HISTORICO);
    END
END
^


/* Trigger: TTBPCM_SETOR_OPERADOR_B01I */
CREATE OR ALTER TRIGGER TTBPCM_SETOR_OPERADOR_B01I FOR TBPCM_SETOR_OPERADOR
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBPCM_SETOR_OPERADOR, 1);
END
^


/* Trigger: TTBPCM_SETOR_OPERADOR_B01U */
CREATE OR ALTER TRIGGER TTBPCM_SETOR_OPERADOR_B01U FOR TBPCM_SETOR_OPERADOR
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_USUARIO VARCHAR(500);
DECLARE VARIABLE NEW_USUARIO VARCHAR(500);
BEGIN

    HISTORICO = '';
    OLD_USUARIO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.USUARIO FROM TBUSUARIO A WHERE A.CODIGO = OLD.USUARIO_ID), '');
    NEW_USUARIO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.USUARIO FROM TBUSUARIO A WHERE A.CODIGO = NEW.USUARIO_ID), '');

    IF (OLD.USUARIO_ID <> NEW.USUARIO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' USUARIO:'||OLD_USUARIO||'->'||NEW_USUARIO;
    END

    IF (OLD.TIPO_OPERADOR <> NEW.TIPO_OPERADOR) THEN
    BEGIN
        HISTORICO = HISTORICO||' TIPO:'||OLD.TIPO_OPERADOR||'->'||NEW.TIPO_OPERADOR;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'USUARIO:'||NEW_USUARIO||' ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_SETOR', OLD.SETOR_ID, FN_ELIPSE(HISTORICO, 500));
    END                                             
END
^


/* Trigger: TTBPCM_SETOR_OPERADOR_B02I */
CREATE OR ALTER TRIGGER TTBPCM_SETOR_OPERADOR_B02I FOR TBPCM_SETOR_OPERADOR
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE USUARIO VARCHAR(500);
BEGIN
    
    HISTORICO = '';
    USUARIO = COALESCE((SELECT FIRST 1 A.USUARIO FROM TBUSUARIO A WHERE A.CODIGO = NEW.USUARIO_ID), '');

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'OPERADOR:'||USUARIO||' INCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_SETOR', NEW.SETOR_ID, HISTORICO);
    END
END
^

SET TERM ; ^

/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBPCM_CHAMADO_TIPO_B01D */
CREATE OR ALTER TRIGGER TTBPCM_CHAMADO_TIPO_B01D FOR TBPCM_CHAMADO_TIPO
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO EXCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_CHAMADO_TIPO', OLD.ID, HISTORICO);
    END
END
^


/* Trigger: TTBPCM_CHAMADO_TIPO_B01I */
CREATE OR ALTER TRIGGER TTBPCM_CHAMADO_TIPO_B01I FOR TBPCM_CHAMADO_TIPO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBPCM_CHAMADO_TIPO, 1);
END
^


/* Trigger: TTBPCM_CHAMADO_TIPO_B01U */
CREATE OR ALTER TRIGGER TTBPCM_CHAMADO_TIPO_B01U FOR TBPCM_CHAMADO_TIPO
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_SETOR VARCHAR(500);
DECLARE VARIABLE NEW_SETOR VARCHAR(500);
BEGIN
    
    HISTORICO = '';
    OLD_SETOR = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.DESCRICAO FROM TBPCM_SETOR A WHERE A.ID = OLD.SETOR_ID), '');
    NEW_SETOR = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.DESCRICAO FROM TBPCM_SETOR A WHERE A.ID = NEW.SETOR_ID), '');

    IF (OLD.STATUS <> NEW.STATUS) THEN
    BEGIN
        HISTORICO = HISTORICO||' STATUS:'||OLD.STATUS||'->'||NEW.STATUS;
    END
    
    IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' DESC:'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END

    IF (OLD.SETOR_ID <> NEW.SETOR_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' SETOR:'||OLD_SETOR||'->'||NEW_SETOR;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'REGISTRO ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_CHAMADO_TIPO', OLD.ID, FN_ELIPSES(HISTORICO, 500));
    END

END
^


/* Trigger: TTBPCM_CHAMADO_TIPO_B02D */
CREATE OR ALTER TRIGGER TTBPCM_CHAMADO_TIPO_B02D FOR TBPCM_CHAMADO_TIPO
ACTIVE BEFORE DELETE POSITION 1
AS
BEGIN
    DELETE FROM TBPCM_CHAMADO_CATEGORIA A WHERE A.CHAMADO_TIPO_ID = OLD.ID;
END
^


/* Trigger: TTBPCM_CHAMADO_TIPO_B02I */
CREATE OR ALTER TRIGGER TTBPCM_CHAMADO_TIPO_B02I FOR TBPCM_CHAMADO_TIPO
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO INCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_CHAMADO_TIPO', NEW.ID, HISTORICO);
    END
END
^

SET TERM ; ^

/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBPCM_CHAMADO_CAT_B01D */
CREATE OR ALTER TRIGGER TTBPCM_CHAMADO_CAT_B01D FOR TBPCM_CHAMADO_CATEGORIA
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'CATEGORIA:'||FN_LPAD(OLD.ID, 4, '0')||' - '||OLD.DESCRICAO||' EXCLUIDA';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_CHAMADO_TIPO', OLD.CHAMADO_TIPO_ID, HISTORICO);
    END
END
^


/* Trigger: TTBPCM_CHAMADO_CAT_B01I */
CREATE OR ALTER TRIGGER TTBPCM_CHAMADO_CAT_B01I FOR TBPCM_CHAMADO_CATEGORIA
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBPCM_CHAMADO_CATEGORIA, 1);
END
^


/* Trigger: TTBPCM_CHAMADO_CAT_B01U */
CREATE OR ALTER TRIGGER TTBPCM_CHAMADO_CAT_B01U FOR TBPCM_CHAMADO_CATEGORIA
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

    HISTORICO = '';
    
    IF (OLD.STATUS <> NEW.STATUS) THEN
    BEGIN
        HISTORICO = HISTORICO||' STATUS:'||OLD.STATUS||'->'||NEW.STATUS;
    END

    IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' DESC:'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END
    
    IF (OLD.TEMPO_RESPOSTA <> NEW.TEMPO_RESPOSTA) THEN
    BEGIN
        HISTORICO = HISTORICO||' TEMP.RESP:'||FORMATTIME_EXT(OLD.TEMPO_RESPOSTA)||'->'||FORMATTIME_EXT(NEW.TEMPO_RESPOSTA);
    END

    IF (OLD.TEMPO_SOLUCAO <> NEW.TEMPO_SOLUCAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' TEMP.SOLUC:'||FORMATTIME_EXT(OLD.TEMPO_SOLUCAO)||'->'||FORMATTIME_EXT(NEW.TEMPO_SOLUCAO);
    END

    IF (OLD.REQUER_EQUIPAMENTO <> NEW.REQUER_EQUIPAMENTO) THEN
    BEGIN
        HISTORICO = HISTORICO||' REQ.EQUIP:'||OLD.REQUER_EQUIPAMENTO||'->'||NEW.REQUER_EQUIPAMENTO;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'CATEGORIA:'||FN_LPAD(OLD.ID, 4, '0')||' - '||NEW.DESCRICAO||' ALTERADA:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_CHAMADO_TIPO', OLD.CHAMADO_TIPO_ID, FN_ELIPSES(HISTORICO, 500));
    END
END
^


/* Trigger: TTBPCM_CHAMADO_CAT_B02D */
CREATE OR ALTER TRIGGER TTBPCM_CHAMADO_CAT_B02D FOR TBPCM_CHAMADO_CATEGORIA
ACTIVE BEFORE DELETE POSITION 1
AS
BEGIN
    DELETE FROM TBPCM_CHAMADO_SUBCATEGORIA A WHERE A.CHAMADO_CATEGORIA_ID = OLD.ID;
END
^


/* Trigger: TTBPCM_CHAMADO_CAT_B02I */
CREATE OR ALTER TRIGGER TTBPCM_CHAMADO_CAT_B02I FOR TBPCM_CHAMADO_CATEGORIA
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'CATEGORIA:'||FN_LPAD(NEW.ID, 4, '0')||' - '||NEW.DESCRICAO||' INCLUIDA';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_CHAMADO_TIPO', NEW.CHAMADO_TIPO_ID, HISTORICO);
    END
END
^

SET TERM ; ^

/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBPCM_CHAMADO_SUBCAT_B01D */
CREATE OR ALTER TRIGGER TTBPCM_CHAMADO_SUBCAT_B01D FOR TBPCM_CHAMADO_SUBCATEGORIA
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE CHAMADO_TIPO_ID INTEGER;
DECLARE VARIABLE CHAMADO_CATEGORIA VARCHAR(500);
BEGIN

    HISTORICO = '';
    CHAMADO_TIPO_ID = COALESCE((SELECT FIRST 1 A.CHAMADO_TIPO_ID FROM TBPCM_CHAMADO_CATEGORIA A WHERE A.ID = OLD.CHAMADO_CATEGORIA_ID), 0);
    CHAMADO_CATEGORIA = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.DESCRICAO FROM TBPCM_CHAMADO_CATEGORIA A WHERE A.ID = OLD.CHAMADO_CATEGORIA_ID), '');

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'SUBCATEGORIA:'||FN_LPAD(OLD.ID, 4, '0')||' - '||OLD.DESCRICAO||' DA CATEGORIA:'||CHAMADO_CATEGORIA||' EXCLUIDA';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_CHAMADO_TIPO', CHAMADO_TIPO_ID, HISTORICO);
    END
END
^


/* Trigger: TTBPCM_CHAMADO_SUBCAT_B01I */
CREATE OR ALTER TRIGGER TTBPCM_CHAMADO_SUBCAT_B01I FOR TBPCM_CHAMADO_SUBCATEGORIA
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBPCM_CHAMADO_SUBCATEGORIA, 1);
END
^


/* Trigger: TTBPCM_CHAMADO_SUBCAT_B01U */
CREATE OR ALTER TRIGGER TTBPCM_CHAMADO_SUBCAT_B01U FOR TBPCM_CHAMADO_SUBCATEGORIA
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE CHAMADO_TIPO_ID INTEGER;
DECLARE VARIABLE CHAMADO_CATEGORIA VARCHAR(500);
BEGIN
    
    HISTORICO = '';
    CHAMADO_TIPO_ID = COALESCE((SELECT FIRST 1 A.CHAMADO_TIPO_ID FROM TBPCM_CHAMADO_CATEGORIA A WHERE A.ID = OLD.CHAMADO_CATEGORIA_ID), 0);
    CHAMADO_CATEGORIA = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.DESCRICAO FROM TBPCM_CHAMADO_CATEGORIA A WHERE A.ID = OLD.CHAMADO_CATEGORIA_ID), '');

    IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' DESC:'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'SUBCATEGORIA:'||FN_LPAD(OLD.ID, 4, '0')||' - '||NEW.DESCRICAO||' DA CATEGORIA:'||CHAMADO_CATEGORIA||' ALTERADA:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_CHAMADO_TIPO', CHAMADO_TIPO_ID, FN_ELIPSES(HISTORICO, 500));
    END
END
^


/* Trigger: TTBPCM_CHAMADO_SUBCAT_B02I */
CREATE OR ALTER TRIGGER TTBPCM_CHAMADO_SUBCAT_B02I FOR TBPCM_CHAMADO_SUBCATEGORIA
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE CHAMADO_TIPO_ID INTEGER;
DECLARE VARIABLE CHAMADO_CATEGORIA VARCHAR(500);
BEGIN

    HISTORICO = '';
    CHAMADO_TIPO_ID = COALESCE((SELECT FIRST 1 A.CHAMADO_TIPO_ID FROM TBPCM_CHAMADO_CATEGORIA A WHERE A.ID = NEW.CHAMADO_CATEGORIA_ID), 0);
    CHAMADO_CATEGORIA = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.DESCRICAO FROM TBPCM_CHAMADO_CATEGORIA A WHERE A.ID = NEW.CHAMADO_CATEGORIA_ID), '');

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'SUBCATEGORIA:'||FN_LPAD(NEW.ID, 4, '0')||' - '||NEW.DESCRICAO||' DA CATEGORIA:'||CHAMADO_CATEGORIA||' INCLUIDA';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_CHAMADO_TIPO', CHAMADO_TIPO_ID, HISTORICO);
    END
END
^

SET TERM ; ^

/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBPCM_REGISTRO_CHAMADO_B01D */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHAMADO_B01D FOR TBPCM_REGISTRO_CHAMADO
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO EXCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO', OLD.ID, HISTORICO);
    END
END
^


/* Trigger: TTBPCM_REGISTRO_CHAMADO_B01I */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHAMADO_B01I FOR TBPCM_REGISTRO_CHAMADO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBPCM_REGISTRO_CHAMADO, 1);
END
^


/* Trigger: TTBPCM_REGISTRO_CHAMADO_B01U */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHAMADO_B01U FOR TBPCM_REGISTRO_CHAMADO
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_USUARIO VARCHAR(500);
DECLARE VARIABLE NEW_USUARIO VARCHAR(500);
DECLARE VARIABLE OLD_SETOR VARCHAR(500);
DECLARE VARIABLE NEW_SETOR VARCHAR(500); 
DECLARE VARIABLE OLD_CHAMADO_TIPO VARCHAR(500);
DECLARE VARIABLE NEW_CHAMADO_TIPO VARCHAR(500);
DECLARE VARIABLE OLD_CHAMADO_CATEGORIA VARCHAR(500);
DECLARE VARIABLE NEW_CHAMADO_CATEGORIA VARCHAR(500);
DECLARE VARIABLE OLD_CHAMADO_SUBCATEGORIA VARCHAR(500);
DECLARE VARIABLE NEW_CHAMADO_SUBCATEGORIA VARCHAR(500);
DECLARE VARIABLE OLD_EQUIPAMENTO VARCHAR(500);
DECLARE VARIABLE NEW_EQUIPAMENTO VARCHAR(500);
BEGIN

    HISTORICO = '';
    OLD_USUARIO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.USUARIO FROM TBUSUARIO A WHERE A.CODIGO = OLD.USUARIO_ID), '');
    NEW_USUARIO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.USUARIO FROM TBUSUARIO A WHERE A.CODIGO = NEW.USUARIO_ID), '');
    OLD_SETOR = COALESCE((SELECT FIRST 1 FN_LPAD(B.ID, 4, '0')||' - '||B.DESCRICAO FROM TBPCM_SETOR B WHERE B.ID = OLD.SETOR_ID), '');
    NEW_SETOR = COALESCE((SELECT FIRST 1 FN_LPAD(B.ID, 4, '0')||' - '||B.DESCRICAO FROM TBPCM_SETOR B WHERE B.ID = NEW.SETOR_ID), '');
    OLD_CHAMADO_TIPO = COALESCE((SELECT FIRST 1 FN_LPAD(C.ID, 4, '0')||' - '||C.DESCRICAO FROM TBPCM_CHAMADO_TIPO C WHERE C.ID = OLD.CHAMADO_TIPO_ID), '');
    NEW_CHAMADO_TIPO = COALESCE((SELECT FIRST 1 FN_LPAD(C.ID, 4, '0')||' - '||C.DESCRICAO FROM TBPCM_CHAMADO_TIPO C WHERE C.ID = NEW.CHAMADO_TIPO_ID), '');
    OLD_CHAMADO_CATEGORIA = COALESCE((SELECT FIRST 1 FN_LPAD(D.ID, 4, '0')||' - '||D.DESCRICAO FROM TBPCM_CHAMADO_CATEGORIA D WHERE D.ID = OLD.CHAMADO_CATEGORIA_ID), '');
    NEW_CHAMADO_CATEGORIA = COALESCE((SELECT FIRST 1 FN_LPAD(D.ID, 4, '0')||' - '||D.DESCRICAO FROM TBPCM_CHAMADO_CATEGORIA D WHERE D.ID = NEW.CHAMADO_CATEGORIA_ID), '');
    OLD_CHAMADO_SUBCATEGORIA = COALESCE((SELECT FIRST 1 FN_LPAD(E.ID, 4, '0')||' - '||E.DESCRICAO FROM TBPCM_CHAMADO_SUBCATEGORIA E WHERE E.ID = OLD.CHAMADO_SUBCATEGORIA_ID), '');
    NEW_CHAMADO_SUBCATEGORIA = COALESCE((SELECT FIRST 1 FN_LPAD(E.ID, 4, '0')||' - '||E.DESCRICAO FROM TBPCM_CHAMADO_SUBCATEGORIA E WHERE E.ID = NEW.CHAMADO_SUBCATEGORIA_ID), '');
    OLD_EQUIPAMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(F.ID, 7, '0')||' - '||F.DESCRICAO FROM TBPATRIMONIO F WHERE F.ID = OLD.EQUIPAMENTO_ID), '');
    NEW_EQUIPAMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(F.ID, 7, '0')||' - '||F.DESCRICAO FROM TBPATRIMONIO F WHERE F.ID = NEW.EQUIPAMENTO_ID), '');

    IF (OLD.STATUS <> NEW.STATUS) THEN
    BEGIN
        HISTORICO = HISTORICO||' STATUS:'||OLD.STATUS||'->'||NEW.STATUS;
    END

    IF (OLD.USUARIO_ID <> NEW.USUARIO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' USUARIO:'||OLD_USUARIO||'->'||NEW_USUARIO;
    END

    IF (OLD.SETOR_ID <> NEW.SETOR_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' SETOR:'||OLD_SETOR||'->'||NEW_SETOR;
    END

    IF (OLD.SOLICITANTE_NOME <> NEW.SOLICITANTE_NOME) THEN
    BEGIN
        HISTORICO = HISTORICO||' SOLIC.NOME:'||OLD.SOLICITANTE_NOME||'->'||NEW.SOLICITANTE_NOME;
    END

    IF (OLD.SOLICITATNE_SETOR <> NEW.SOLICITATNE_SETOR) THEN
    BEGIN
        HISTORICO = HISTORICO||' SOLIC.SETOR:'||OLD.SOLICITATNE_SETOR||'->'||NEW.SOLICITATNE_SETOR;
    END

    IF (OLD.SOLICITANTE_CARGO <> NEW.SOLICITANTE_CARGO) THEN
    BEGIN
        HISTORICO = HISTORICO||' SOLIC.CARGO:'||OLD.SOLICITANTE_CARGO||'->'||NEW.SOLICITANTE_CARGO;
    END

    IF (OLD.SOLICITANTE_CONTATO <> NEW.SOLICITANTE_CONTATO) THEN
    BEGIN
        HISTORICO = HISTORICO||' INFORMACOES DE CONTATO DO SOLICITANTE ALTERADAS';
    END

    IF (OLD.CHAMADO_TIPO_ID <> NEW.CHAMADO_TIPO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' OCORR.TIPO:'||OLD_CHAMADO_TIPO||'->'||NEW_CHAMADO_TIPO;
    END

    IF (OLD.CHAMADO_CATEGORIA_ID <> NEW.CHAMADO_CATEGORIA_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' OCORR.CAT.:'||OLD_CHAMADO_CATEGORIA||'->'||NEW_CHAMADO_CATEGORIA;
    END

    IF (OLD.CHAMADO_SUBCATEGORIA_ID <> NEW.CHAMADO_SUBCATEGORIA_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' OCORR.SUBCAT.:'||OLD_CHAMADO_SUBCATEGORIA||'->'||NEW_CHAMADO_SUBCATEGORIA;
    END

    IF (OLD.PRIORIDADE <> NEW.PRIORIDADE) THEN
    BEGIN
        HISTORICO = HISTORICO||' PRIORIDADE:'||OLD.PRIORIDADE||'->'||NEW.PRIORIDADE;
    END

    IF (OLD.URGENCIA <> NEW.URGENCIA) THEN
    BEGIN
        HISTORICO = HISTORICO||' URGENCIA:'||OLD.URGENCIA||'->'||NEW.URGENCIA;
    END

    IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' DESCRICAO DO PROBLEMA ALTERADA';
    END

    IF (OLD.EQUIPAMENTO_ID <> NEW.EQUIPAMENTO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' EQUIPAMENTO:'||OLD_EQUIPAMENTO||'->'||NEW_EQUIPAMENTO;
    END

    IF (OLD.DATAHORA_REG <> NEW.DATAHORA_REG) THEN
    BEGIN
        HISTORICO = HISTORICO||' DATA/HORA:'||FORMATDATETIME_S(OLD.DATAHORA_REG)||'->'||FORMATDATETIME_S(NEW.DATAHORA_REG);
    END

    IF (OLD.DATAHORA_PREVISAO <> NEW.DATAHORA_PREVISAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' PREVISAO:'||FORMATDATETIME_S(OLD.DATAHORA_PREVISAO)||'->'||FORMATDATETIME_S(NEW.DATAHORA_PREVISAO);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'REGISTRO ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO', OLD.ID, FN_ELIPSES(HISTORICO, 500));
    END
END
^


/* Trigger: TTBPCM_REGISTRO_CHAMADO_B02D */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHAMADO_B02D FOR TBPCM_REGISTRO_CHAMADO
ACTIVE BEFORE DELETE POSITION 1
AS
BEGIN
    DELETE FROM TBPCM_REGISTRO_CHAMADO_ITEM A WHERE A.REGISTRO_CHAMADO_ID = OLD.ID; -- Apaga Registros de Atendimento do Chamado
    DELETE FROM TBPCM_REGISTRO_CHAMADO_MSG  B WHERE B.REGISTRO_CHAMADO_ID = OLD.ID; -- Apaga Registros de Mensagens do Chamado
    DELETE FROM TBPCM_REGISTRO_CHAMADO_AVAL C WHERE C.REGISTRO_CHAMADO_ID = OLD.ID; -- Apaga Registros de Avaliações do Chamado
END
^


/* Trigger: TTBPCM_REGISTRO_CHAMADO_B02I */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHAMADO_B02I FOR TBPCM_REGISTRO_CHAMADO
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO INCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO', NEW.ID, HISTORICO);
    END
END
^


/* Trigger: TTBPCM_REGISTRO_CHAMADO_B02U */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHAMADO_B02U FOR TBPCM_REGISTRO_CHAMADO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN
    
    HISTORICO = '';

    IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
        HISTORICO = HISTORICO||FN_ELIPSES(OLD.DESCRICAO, 200)||'->'||FN_ELIPSES(NEW.DESCRICAO, 200);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'DESCRICAO ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO', OLD.ID, FN_ELIPSES(HISTORICO, 500));
    END
END
^


/* Trigger: TTBPCM_REGISTRO_CHAMADO_B03U */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHAMADO_B03U FOR TBPCM_REGISTRO_CHAMADO
ACTIVE BEFORE UPDATE POSITION 2
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

    HISTORICO = '';
    
    IF (OLD.SOLICITANTE_CONTATO <> NEW.SOLICITANTE_CONTATO) THEN
    BEGIN
        HISTORICO = HISTORICO||FN_ELIPSES(OLD.SOLICITANTE_CONTATO, 200)||'->'||FN_ELIPSES(NEW.SOLICITANTE_CONTATO, 200);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'SOLIC.CONTATO ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO', OLD.ID, FN_ELIPSES(HISTORICO, 500));
    END
END
^

SET TERM ; ^

/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBPCM_REGISTRO_CHA_ITEM_B01D */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_ITEM_B01D FOR TBPCM_REGISTRO_CHAMADO_ITEM
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'SITUACAO:'||FN_LPAD(OLD.ID, 4, '0')||' EXCLUIDA';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO', OLD.REGISTRO_CHAMADO_ID, HISTORICO);
    END
END
^


/* Trigger: TTBPCM_REGISTRO_CHA_ITEM_B01I */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_ITEM_B01I FOR TBPCM_REGISTRO_CHAMADO_ITEM
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBPCM_REGISTRO_CHAMADO_ITEM, 1);
END
^


/* Trigger: TTBPCM_REGISTRO_CHA_ITEM_B01U */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_ITEM_B01U FOR TBPCM_REGISTRO_CHAMADO_ITEM
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_SETOR_OPERADOR VARCHAR(500);
DECLARE VARIABLE NEW_SETOR_OPERADOR VARCHAR(500);
BEGIN
    
    HISTORICO = '';
    OLD_SETOR_OPERADOR = COALESCE((SELECT FIRST 1 FN_LPAD(B.CODIGO, 4, '0')||' - '||B.USUARIO FROM TBPCM_SETOR_OPERADOR A LEFT JOIN TBUSUARIO B ON B.CODIGO = A.USUARIO_ID WHERE A.ID = OLD.SETOR_OPERADOR_ID), '');
    NEW_SETOR_OPERADOR = COALESCE((SELECT FIRST 1 FN_LPAD(B.CODIGO, 4, '0')||' - '||B.USUARIO FROM TBPCM_SETOR_OPERADOR A LEFT JOIN TBUSUARIO B ON B.CODIGO = A.USUARIO_ID WHERE A.ID = NEW.SETOR_OPERADOR_ID), '');

    IF (OLD.STATUS <> NEW.STATUS) THEN
    BEGIN
        HISTORICO = HISTORICO||' STATUS:'||OLD.STATUS||'->'||NEW.STATUS;
    END

    IF (OLD.DATAHORA_INI <> NEW.DATAHORA_INI) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT.INICIAL:'||FORMATDATETIME_S(OLD.DATAHORA_INI)||'->'||FORMATDATETIME_S(NEW.DATAHORA_INI);
    END

    IF (OLD.DATAHORA_FIN <> NEW.DATAHORA_FIN) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT.FINAL:'||FORMATDATETIME_S(OLD.DATAHORA_FIN)||'->'||FORMATDATETIME_S(NEW.DATAHORA_FIN);
    END   

    IF (OLD.SETOR_OPERADOR_ID <> NEW.SETOR_OPERADOR_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' OPERADOR:'||OLD_SETOR_OPERADOR||'->'||NEW_SETOR_OPERADOR;
    END

    IF (OLD.OBSERVACAO <> NEW.OBSERVACAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' OBSERVACOES ALTERADAS';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'SITUACAO:'||FN_LPAD(OLD.ID, 4, '0')||' ALTERADA:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO', OLD.REGISTRO_CHAMADO_ID, FN_ELIPSES(HISTORICO, 500));
    END
END
^


/* Trigger: TTBPCM_REGISTRO_CHA_ITEM_B02I */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_ITEM_B02I FOR TBPCM_REGISTRO_CHAMADO_ITEM
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'SITUACAO:'||FN_LPAD(NEW.ID, 4, '0')||' INCLUIDA';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO', NEW.REGISTRO_CHAMADO_ID, HISTORICO);
    END
END
^


/* Trigger: TTBPCM_REGISTRO_CHA_ITEM_B02U */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_ITEM_B02U FOR TBPCM_REGISTRO_CHAMADO_ITEM
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

    HISTORICO = '';

    IF (OLD.OBSERVACAO <> NEW.OBSERVACAO) THEN
    BEGIN
        HISTORICO = HISTORICO||FN_ELIPSES(OLD.OBSERVACAO, 200)||'->'||FN_ELIPSES(NEW.OBSERVACAO, 200);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'OBSERVACAO DA SITUACAO:'||FN_LPAD(OLD.ID, 4, '0')||' ALTERADA:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO', OLD.REGISTRO_CHAMADO_ID, FN_ELIPSES(HISTORICO, 500));
    END
END
^

SET TERM ; ^

/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBPCM_REGISTRO_CHA_MSG_B01D */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_MSG_B01D FOR TBPCM_REGISTRO_CHAMADO_MSG
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'MENSAGEM:'||FN_LPAD(OLD.ID, 4, '0')||' EXCLUIDA';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO', OLD.REGISTRO_CHAMADO_ID, HISTORICO);
    END
END
^


/* Trigger: TTBPCM_REGISTRO_CHA_MSG_B01I */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_MSG_B01I FOR TBPCM_REGISTRO_CHAMADO_MSG
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBPCM_REGISTRO_CHAMADO_MSG, 1);
END
^


/* Trigger: TTBPCM_REGISTRO_CHA_MSG_B01U */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_MSG_B01U FOR TBPCM_REGISTRO_CHAMADO_MSG
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_USUARIO VARCHAR(500);
DECLARE VARIABLE NEW_USUARIO VARCHAR(500);
BEGIN

    HISTORICO = '';
    OLD_USUARIO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||A.USUARIO FROM TBUSUARIO A WHERE A.CODIGO = OLD.USUARIO_ID), '');
    NEW_USUARIO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||A.USUARIO FROM TBUSUARIO A WHERE A.CODIGO = NEW.USUARIO_ID), '');

    IF (OLD.MENSAGEM <> NEW.MENSAGEM) THEN
    BEGIN
        HISTORICO = HISTORICO||' CORPO DA MENSAGEM ALTERADA';
    END

    IF (OLD.DATAHORA_ENV <> NEW.DATAHORA_ENV) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT.ENVIO:'||FORMATDATETIME_S(OLD.DATAHORA_ENV)||'->'||FORMATDATETIME_S(NEW.DATAHORA_ENV);
    END

    IF (OLD.USUARIO_ID <> NEW.USUARIO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' USUARIO:'||OLD_USUARIO||'->'||NEW_USUARIO;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'MENSAGEM:'||FN_LPAD(OLD.ID, 4, '0')||' ALTERADA:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO', OLD.REGISTRO_CHAMADO_ID, FN_ELIPSES(HISTORICO, 500));
    END
END
^


/* Trigger: TTBPCM_REGISTRO_CHA_MSG_B02I */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_MSG_B02I FOR TBPCM_REGISTRO_CHAMADO_MSG
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'MENSAGEM:'||FN_LPAD(NEW.ID, 4, '0')||' INCLUIDA';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO', NEW.REGISTRO_CHAMADO_ID, HISTORICO);
    END
END
^


/* Trigger: TTBPCM_REGISTRO_CHA_MSG_B02U */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_MSG_B02U FOR TBPCM_REGISTRO_CHAMADO_MSG
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

    HISTORICO = '';

    IF (OLD.MENSAGEM <> NEW.MENSAGEM) THEN
    BEGIN
        HISTORICO = HISTORICO||FN_ELIPSES(OLD.MENSAGEM, 200)||'->'||FN_ELIPSES(NEW.MENSAGEM, 200);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'CORPO DA MENSAGEM:'||FN_LPAD(OLD.ID, 4, '0')||' ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO', OLD.REGISTRO_CHAMADO_ID, FN_ELIPSES(HISTORICO, 500));
    END
END
^

SET TERM ; ^

/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBPCM_REGISTRO_CHA_AVAL_B01D */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_AVAL_B01D FOR TBPCM_REGISTRO_CHAMADO_AVAL
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'AVALICAO:'||FN_LPAD(OLD.ID, 4, '0')||' EXCLUIDA';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO_AVAL', OLD.REGISTRO_CHAMADO_ID, HISTORICO);
    END
END
^


/* Trigger: TTBPCM_REGISTRO_CHA_AVAL_B01I */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_AVAL_B01I FOR TBPCM_REGISTRO_CHAMADO_AVAL
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBPCM_REGISTRO_CHAMADO_AVAL, 1);
END
^


/* Trigger: TTBPCM_REGISTRO_CHA_AVAL_B01U */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_AVAL_B01U FOR TBPCM_REGISTRO_CHAMADO_AVAL
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_USUARIO VARCHAR(500);
DECLARE VARIABLE NEW_USUARIO VARCHAR(500);
BEGIN

    HISTORICO = '';
    OLD_USUARIO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.USUARIO FROM TBUSUARIO A WHERE A.CODIGO = OLD.USUARIO_ID), '');
    NEW_USUARIO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.USUARIO FROM TBUSUARIO A WHERE A.CODIGO = NEW.USUARIO_ID), '');

    IF (OLD.USUARIO_ID <> NEW.USUARIO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' USUARIO:'||OLD_USUARIO||'->'||NEW_USUARIO;
    END

    IF (OLD.CAMPO01 <> NEW.CAMPO01) THEN
    BEGIN
        HISTORICO = HISTORICO||' RESP.01:'||OLD.CAMPO01||'->'||NEW.CAMPO01;
    END

    IF (OLD.CAMPO02 <> NEW.CAMPO02) THEN
    BEGIN
        HISTORICO = HISTORICO||' RESP.02:'||OLD.CAMPO02||'->'||NEW.CAMPO02;
    END

    IF (OLD.CAMPO03 <> NEW.CAMPO03) THEN
    BEGIN
        HISTORICO = HISTORICO||' RESP.03:'||OLD.CAMPO03||'->'||NEW.CAMPO03;
    END

    IF (OLD.CAMPO04 <> NEW.CAMPO04) THEN
    BEGIN
        HISTORICO = HISTORICO||' RESP.04:'||OLD.CAMPO04||'->'||NEW.CAMPO04;
    END

    IF (OLD.CAMPO05 <> NEW.CAMPO05) THEN
    BEGIN
        HISTORICO = HISTORICO||' RESP.05:'||OLD.CAMPO05||'->'||NEW.CAMPO05;
    END

    IF (OLD.OBSERVACAO <> NEW.OBSERVACAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' OBSERVACAO ALTERADA';
    END
    
    IF (OLD.DATAHORA_REG <> NEW.DATAHORA_REG) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT.REG:'||FORMATDATETIME_S(OLD.DATAHORA_REG)||'->'||FORMATDATETIME_S(NEW.DATAHORA_REG);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'AVALIACAO:'||FN_LPAD(OLD.ID, 4, '0')||' ALTERADA:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO_AVAL', OLD.REGISTRO_CHAMADO_ID, FN_ELIPSES(HISTORICO, 500));
    END
END
^


/* Trigger: TTBPCM_REGISTRO_CHA_AVAL_B02I */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_AVAL_B02I FOR TBPCM_REGISTRO_CHAMADO_AVAL
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'AVALICAO:'||FN_LPAD(NEW.ID, 4, '0')||' INCLUIDA';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO_AVAL', NEW.REGISTRO_CHAMADO_ID, HISTORICO);
    END
END
^


/* Trigger: TTBPCM_REGISTRO_CHA_AVAL_B02U */
CREATE OR ALTER TRIGGER TTBPCM_REGISTRO_CHA_AVAL_B02U FOR TBPCM_REGISTRO_CHAMADO_AVAL
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

    HISTORICO = '';

    IF (OLD.OBSERVACAO <> NEW.OBSERVACAO) THEN
    BEGIN
        HISTORICO = HISTORICO||FN_ELIPSES(OLD.OBSERVACAO, 200)||'->'||FN_ELIPSES(NEW.OBSERVACAO, 200);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'OBSERVACAO DA AVALICAO:'||FN_LPAD(OLD.ID, 4, '0')||' ALTERADA:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_REGISTRO_CHAMADO', OLD.REGISTRO_CHAMADO_ID, FN_ELIPSES(HISTORICO, 500));
    END
END
^

SET TERM ; ^