CREATE TABLE TBAVALIACAO_DES_NIVEL (
    ID INTEGER NOT NULL,
    TITULO VARCHAR(50),
    FAIXA_INICIAL INTEGER,
    FAIXA_FINAL INTEGER,
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
	DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
	DATAHORA_UPDATE TIMESTAMP,
	DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_NIVEL
ADD CONSTRAINT PK_TBAVALIACAO_DES_NIVEL
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_NIVEL IS 'Niveis dos fatores de avaliacao. Ex.: bom, otimo.';

CREATE SEQUENCE GTBAVALIACAO_DES_NIVEL;


CREATE INDEX ITBAVALIACAO_DES_NIVEL_01
ON TBAVALIACAO_DES_NIVEL (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_NIVEL_B0I FOR TBAVALIACAO_DES_NIVEL
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_NIVEL, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_NIVEL_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_NIVEL_B0U FOR TBAVALIACAO_DES_NIVEL
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_NIVEL',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_NIVEL_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIACAO_DES_NIVEL_B1U FOR TBAVALIACAO_DES_NIVEL
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_NIVEL', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_NIVEL_B1U IS 'Registra historico de alteracoes.';


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


CREATE TABLE TBAVALIACAO_DES_FATOR (
    ID INTEGER NOT NULL,
    TITULO VARCHAR(50),
    DESCRICAO VARCHAR(500),
    AVALIACAO_DES_FATOR_TIPO_ID INTEGER,
    ORDEM_PERC_NIVEL CHAR(1) DEFAULT '0',
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
	DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
	DATAHORA_UPDATE TIMESTAMP,
	DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_FATOR
ADD CONSTRAINT PK_TBAVALIACAO_DES_FATOR
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_FATOR IS 'Fatores para avaliacao de desempenho.';
COMMENT ON COLUMN TBAVALIACAO_DES_FATOR.ORDEM_PERC_NIVEL IS
'Ordem da faixa de percentual nos niveis do fator. 0 - crescente e 1 - decrescente.';

CREATE SEQUENCE GTBAVALIACAO_DES_FATOR;


CREATE INDEX ITBAVALIACAO_DES_FATOR_01
ON TBAVALIACAO_DES_FATOR (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_FATOR_B0I FOR TBAVALIACAO_DES_FATOR
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_FATOR, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_FATOR_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_FATOR_B0U FOR TBAVALIACAO_DES_FATOR
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_FATOR',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_FATOR_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';




CREATE TRIGGER TTBAVALIACAO_DES_FATOR_B1U FOR TBAVALIACAO_DES_FATOR
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_FATOR', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_FATOR_B1U IS 'Registra historico de alteracoes.';




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


CREATE TABLE TBAVALIACAO_DES_FATOR_NIVEL (
    ID INTEGER NOT NULL,
    AVALIACAO_DES_FATOR_ID INTEGER NOT NULL,
    AVALIACAO_DES_NIVEL_ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(500),
    FAIXA_INICIAL NUMERIC(18,2),
    FAIXA_FINAL NUMERIC(18,2),
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
	DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
	DATAHORA_UPDATE TIMESTAMP,
	DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_FATOR_NIVEL
ADD CONSTRAINT PK_TBAVALIACAO_DES_FATOR_NIVEL
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_FATOR_NIVEL IS 'Descricao dos niveis dos fatores para a avaliacao de desempenho.';

CREATE SEQUENCE GTBAVALIACAO_DES_FATOR_NIVEL;


CREATE INDEX ITBAVALIACAO_DES_FATOR_NIVEL_01
ON TBAVALIACAO_DES_FATOR_NIVEL (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_FATOR_NVL_B0I FOR TBAVALIACAO_DES_FATOR_NIVEL
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_FATOR_NIVEL, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_FATOR_NVL_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_FATOR_NVL_B0U FOR TBAVALIACAO_DES_FATOR_NIVEL
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_FATOR_NIVEL',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_FATOR_NVL_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';



CREATE TRIGGER TTBAVALIACAO_DES_FATOR_NVL_B1U FOR TBAVALIACAO_DES_FATOR_NIVEL
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_FATOR_NIVEL', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_FATOR_NVL_B1U IS 'Registra historico de alteracoes.';



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


CREATE TABLE TBAVALIACAO_DES_FATOR_TIPO (
    ID INTEGER NOT NULL,
    TITULO VARCHAR(50),
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_FATOR_TIPO
ADD CONSTRAINT PK_TBAVALIACAO_DES_FATOR_TIPO
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_FATOR_TIPO IS 'Tipos dos fatores para a avaliacao de desempenho.';

CREATE SEQUENCE GTBAVALIACAO_DES_FATOR_TIPO;


CREATE INDEX ITBAVALIACAO_DES_FATOR_TIPO_01
ON TBAVALIACAO_DES_FATOR_TIPO (STATUSEXCLUSAO);


CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_FTR_TIPO_B0I FOR TBAVALIACAO_DES_FATOR_TIPO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_FATOR_TIPO, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_FTR_TIPO_B0I IS 'Gerar ID automaticamente.';



CREATE TRIGGER TTBAVALIACAO_DES_FATOR_TIPO_B0U FOR TBAVALIACAO_DES_FATOR_TIPO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_FATOR_TIPO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_FATOR_TIPO_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';




CREATE TRIGGER TTBAVALIACAO_DES_FATOR_TIPO_B1U FOR TBAVALIACAO_DES_FATOR_TIPO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_FATOR_TIPO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_FATOR_TIPO_B1U IS 'Registra historico de alteracoes.';




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




CREATE TABLE TBAVALIACAO_DES_FORMACAO (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(100),
    PONTO INTEGER,
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_FORMACAO
ADD CONSTRAINT PK_TBAVALIACAO_DES_FORMACAO
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_FORMACAO IS 'Instrucao ou formacao do avaliado para avaliacao de desempenho.';

CREATE SEQUENCE GTBAVALIACAO_DES_FORMACAO;


CREATE INDEX ITBAVALIACAO_DES_FORMACAO_01
ON TBAVALIACAO_DES_FORMACAO (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_FORMACAO_B0I FOR TBAVALIACAO_DES_FORMACAO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_FORMACAO, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_FORMACAO_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_FORMACAO_B0U FOR TBAVALIACAO_DES_FORMACAO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_FORMACAO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_FORMACAO_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIACAO_DES_FORMACAO_B1U FOR TBAVALIACAO_DES_FORMACAO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_FORMACAO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_FORMACAO_B1U IS 'Registra historico de alteracoes.';




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




CREATE TABLE TBAVALIACAO_DES_RESUMO (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(100),
    PESO INTEGER,
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_RESUMO
ADD CONSTRAINT PK_TBAVALIACAO_DES_RESUMO
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_RESUMO IS 'Resumo para avaliacao de desempenho.';

CREATE SEQUENCE GTBAVALIACAO_DES_RESUMO;


CREATE INDEX ITBAVALIACAO_DES_RESUMO_01
ON TBAVALIACAO_DES_RESUMO (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_RESUMO_B0I FOR TBAVALIACAO_DES_RESUMO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_RESUMO, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESUMO_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_RESUMO_B0U FOR TBAVALIACAO_DES_RESUMO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_RESUMO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESUMO_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIACAO_DES_RESUMO_B1U FOR TBAVALIACAO_DES_RESUMO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_RESUMO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESUMO_B1U IS 'Registra historico de alteracoes.';





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





CREATE TABLE TBAVALIACAO_DES_RESUMO_TIPO (
    ID INTEGER NOT NULL,
    AVALIACAO_DES_RESUMO_ID INTEGER,
    AVALIACAO_DES_FATOR_TIPO_ID INTEGER DEFAULT 0,
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_RESUMO_TIPO
ADD CONSTRAINT PK_TBAVALIACAO_DES_RSM_TIPO
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_RESUMO_TIPO IS 'Tipo de fator relacionado ao item de resumo da avaliacao de desempenho. Necessario para definir o destino do calculo no resumo ao dar os pontos em cada fator.';

CREATE SEQUENCE GTBAVALIACAO_DES_RSM_TIPO;


CREATE INDEX ITBAVALIACAO_DES_RSM_TIPO_01
ON TBAVALIACAO_DES_RESUMO_TIPO (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_RSM_TIPO_B0I FOR TBAVALIACAO_DES_RESUMO_TIPO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_RSM_TIPO, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RSM_TIPO_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_RSM_TIPO_B0U FOR TBAVALIACAO_DES_RESUMO_TIPO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_RESUMO_TIPO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RSM_TIPO_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIACAO_DES_RSM_TIPO_B1U FOR TBAVALIACAO_DES_RESUMO_TIPO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_RESUMO_TIPO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RSM_TIPO_B1U IS 'Registra historico de alteracoes.';





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





CREATE TABLE TBAVALIACAO_DES_MODELO (
    ID INTEGER NOT NULL,
    TITULO VARCHAR(100),
    INSTRUCAO_INICIAL VARCHAR(600),
    META_MEDIA_GERAL INTEGER,
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_MODELO
ADD CONSTRAINT PK_TBAVALIACAO_DES_MODELO
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_MODELO IS 'Modelo de avaliacao de desempenho.';

CREATE SEQUENCE GTBAVALIACAO_DES_MODELO;


CREATE INDEX ITBAVALIACAO_DES_MODELO_01
ON TBAVALIACAO_DES_MODELO (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_MODELO_B0I FOR TBAVALIACAO_DES_MODELO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_MODELO, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_MODELO_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_MODELO_B0U FOR TBAVALIACAO_DES_MODELO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_MODELO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_MODELO_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIACAO_DES_MODELO_B1U FOR TBAVALIACAO_DES_MODELO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_MODELO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_MODELO_B1U IS 'Registra historico de alteracoes.';




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



CREATE TABLE TBAVALIACAO_DES_MOD_FATOR (
    ID INTEGER NOT NULL,
    AVALIACAO_DES_MODELO_ID INTEGER,
    AVALIACAO_DES_FATOR_ID INTEGER, 
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_MOD_FATOR
ADD CONSTRAINT PK_TBAVALIACAO_DES_MOD_FATOR
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_MOD_FATOR IS 'Fator do modelo de avaliacao de desempenho.';

CREATE SEQUENCE GTBAVALIACAO_DES_MOD_FATOR;


CREATE INDEX ITBAVALIACAO_DES_MOD_FATOR_01
ON TBAVALIACAO_DES_MOD_FATOR (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_MOD_FATOR_B0I FOR TBAVALIACAO_DES_MOD_FATOR
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_MOD_FATOR, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_MOD_FATOR_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_MOD_FATOR_B0U FOR TBAVALIACAO_DES_MOD_FATOR
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_MOD_FATOR',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_MOD_FATOR_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIACAO_DES_MOD_FATOR_B1U FOR TBAVALIACAO_DES_MOD_FATOR
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_MOD_FATOR', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_MOD_FATOR_B1U IS 'Registra historico de alteracoes.';




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




CREATE TABLE TBAVALIACAO_DES_MOD_FORMACAO (
    ID INTEGER NOT NULL,
    AVALIACAO_DES_MODELO_ID INTEGER,
    AVALIACAO_DES_FORMACAO_ID INTEGER, 
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_MOD_FORMACAO
ADD CONSTRAINT PK_TBAVALIACAO_DES_MOD_FORMACAO
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_MOD_FORMACAO IS 'Formacao do modelo de avaliacao de desempenho.';

CREATE SEQUENCE GTBAVALIACAO_DES_MOD_FORMACAO;


CREATE INDEX ITBAVALIACAO_DES_MOD_FORM_01
ON TBAVALIACAO_DES_MOD_FORMACAO (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_MOD_FORM_B0I FOR TBAVALIACAO_DES_MOD_FORMACAO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_MOD_FORMACAO, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_MOD_FORM_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_MOD_FORM_B0U FOR TBAVALIACAO_DES_MOD_FORMACAO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_MOD_FORMACAO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_MOD_FORM_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIACAO_DES_MOD_FORM_B1U FOR TBAVALIACAO_DES_MOD_FORMACAO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_MOD_FORMACAO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_MOD_FORM_B1U IS 'Registra historico de alteracoes.';




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




CREATE TABLE TBAVALIACAO_DES_MOD_RESUMO (
    ID INTEGER NOT NULL,
    AVALIACAO_DES_MODELO_ID INTEGER,
    AVALIACAO_DES_RESUMO_ID INTEGER, 
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_MOD_RESUMO
ADD CONSTRAINT PK_TBAVALIACAO_DES_MOD_RESUMO
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_MOD_RESUMO IS 'Resumo do modelo de avaliacao de desempenho.';

CREATE SEQUENCE GTBAVALIACAO_DES_MOD_RESUMO;


CREATE INDEX ITBAVALIACAO_DES_MOD_RESU_01
ON TBAVALIACAO_DES_MOD_RESUMO (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_MOD_RESU_B0I FOR TBAVALIACAO_DES_MOD_RESUMO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_MOD_RESUMO, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_MOD_RESU_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_MOD_RESU_B0U FOR TBAVALIACAO_DES_MOD_RESUMO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_MOD_RESUMO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_MOD_RESU_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIACAO_DES_MOD_RESU_B1U FOR TBAVALIACAO_DES_MOD_RESUMO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_MOD_RESUMO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_MOD_RESU_B1U IS 'Registra historico de alteracoes.';





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




CREATE TABLE TBAVALIACAO_DES_RESP_BASE (
    ID INTEGER NOT NULL,
    AVALIACAO_DES_MODELO_ID INTEGER,
    TITULO VARCHAR(100),
    INSTRUCAO_INICIAL VARCHAR(600),
    META_MEDIA_GERAL NUMERIC(18,4),
    DATA_AVALIACAO DATE DEFAULT 'NOW',
    STATUS CHAR(1) DEFAULT '1',
    RESPONDIDA CHAR(1) DEFAULT '0',
    VERIFICADOR CHAR(1) DEFAULT '1',
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_RESP_BASE
ADD CONSTRAINT PK_TBAVALIACAO_DES_RESP_BASE
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_RESP_BASE IS 'Base da resposta da avaliacao de desempenho.';

COMMENT ON COLUMN TBAVALIACAO_DES_RESP_BASE.STATUS IS 'Ativo ou inativo.';
COMMENT ON COLUMN TBAVALIACAO_DES_RESP_BASE.RESPONDIDA IS 'Indica se ja tem alguma avaliacao respondida. Definido pela trigger TTBAVALIACAO_DES_RESP_A0U.';
COMMENT ON COLUMN TBAVALIACAO_DES_RESP_BASE.VERIFICADOR IS 'Flag para indicar a necessidade de verificacao na trigger TTBAVALIACAO_DES_RESP_BAS_B2U.';

CREATE SEQUENCE GTBAVALIACAO_DES_RESP_BASE;


CREATE INDEX ITBAVALIACAO_DES_RESP_BASE_01
ON TBAVALIACAO_DES_RESP_BASE (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_RESP_BAS_B0I FOR TBAVALIACAO_DES_RESP_BASE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_RESP_BASE, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_BAS_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_RESP_BAS_B0U FOR TBAVALIACAO_DES_RESP_BASE
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_RESP_BASE',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_BAS_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIACAO_DES_RESP_BAS_B1U FOR TBAVALIACAO_DES_RESP_BASE
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

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

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_RESP_BASE', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_BAS_B1U IS 'Registra historico de alteracoes.';



CREATE TRIGGER TTBAVALIACAO_DES_RESP_BAS_B2U FOR TBAVALIACAO_DES_RESP_BASE
ACTIVE BEFORE UPDATE POSITION 2
AS
BEGIN

    IF (OLD.RESPONDIDA = '1' AND NEW.VERIFICADOR IS NULL) THEN
    BEGIN
        EXCEPTION "..." 'Avaliacao ja possui resposta. Operacao cancelada.';
    END

    NEW.VERIFICADOR = '1';

END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_BAS_B2U IS 'Bloquear gravacao caso a avaliacao ja tenha sido respondida.';





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




CREATE TABLE TBAVALIACAO_DES_RESP_BASE_CCUST (
    ID INTEGER NOT NULL,
    AVALIACAO_DES_RESP_BASE_ID INTEGER,
    CCUSTO_CODIGO VARCHAR(10),
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_RESP_BASE_CCUST
ADD CONSTRAINT PK_TBAVAL_DES_RESP_BASE_CCUST
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_RESP_BASE_CCUST IS 'Centros de custo da base da resposta da avaliacao de desempenho.';

CREATE SEQUENCE GTBAVAL_DES_RESP_BASE_CCUST;


CREATE INDEX ITBAVAL_DES_RESP_BASE_CCUST_01
ON TBAVALIACAO_DES_RESP_BASE_CCUST (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVAL_DES_RESP_BASE_CCUST_B0I FOR TBAVALIACAO_DES_RESP_BASE_CCUST
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVAL_DES_RESP_BASE_CCUST, 1);
END;

COMMENT ON TRIGGER TTBAVAL_DES_RESP_BASE_CCUST_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVAL_DES_RESP_BASE_CCUST_B0U FOR TBAVALIACAO_DES_RESP_BASE_CCUST
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_RESP_BASE_CCUST',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVAL_DES_RESP_BASE_CCUST_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVAL_DES_RESP_BASE_CCUST_B1U FOR TBAVALIACAO_DES_RESP_BASE_CCUST
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_RESP_BASE_CCUST', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVAL_DES_RESP_BASE_CCUST_B1U IS 'Registra historico de alteracoes.';




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





CREATE TABLE TBAVALIACAO_DES_RESPOSTA (
    ID INTEGER NOT NULL,
    AVALIACAO_DES_RESP_BASE_ID INTEGER,
    COLABORADOR_ID INTEGER,
    COLABORADOR_NOME VARCHAR(100),
    COLABORADOR_CARGO_ID INTEGER,
    COLABORADOR_CARGO VARCHAR(30),
    COLABORADOR_GESTOR_ID INTEGER,
    COLABORADOR_GESTOR VARCHAR(100),
    COLABORADOR_CCUSTO_CODIGO VARCHAR(10),
    COLABORADOR_CCUSTO_DESCRICAO VARCHAR(40),
    COLABORADOR_ADMISSAO DATE,
    COLABORADOR_ESCOLARIDADE_ID INTEGER,
    COLABORADOR_ESCOLARIDADE_DESC VARCHAR(50),
    PONTUACAO_TOTAL_FATOR NUMERIC(18,4),
    PONTUACAO_MEDIA_FATOR NUMERIC(18,4),
    FORMACAO_ESCOLHIDA_ID INTEGER,
    RESULTADO_FINAL_RESUMO NUMERIC(18,4),
    META_MEDIA_GERAL NUMERIC(18,4),
    ALCANCOU_META_MEDIA_GERAL CHAR(1) DEFAULT '0',
    PONTO_POSITIVO VARCHAR(500),
    PONTO_MELHORAR VARCHAR(500),
    OPINIAO_AVALIADO VARCHAR(500),
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_RESPOSTA
ADD CONSTRAINT PK_TBAVALIACAO_DES_RESPOSTA
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_RESPOSTA IS 'Avaliacao de desempenho.';

COMMENT ON COLUMN TBAVALIACAO_DES_RESPOSTA.COLABORADOR_GESTOR_ID IS 'Id de usuario do gestor.';

CREATE SEQUENCE GTBAVALIACAO_DES_RESPOSTA;


CREATE INDEX ITBAVALIACAO_DES_RESPOSTA_01
ON TBAVALIACAO_DES_RESPOSTA (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_RESP_B0I FOR TBAVALIACAO_DES_RESPOSTA
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_RESPOSTA, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_RESP_B0U FOR TBAVALIACAO_DES_RESPOSTA
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_RESPOSTA',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIACAO_DES_RESP_B1U FOR TBAVALIACAO_DES_RESPOSTA
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (OLD.AVALIACAO_DES_RESP_BASE_ID <> NEW.AVALIACAO_DES_RESP_BASE_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' BASE:'||OLD.AVALIACAO_DES_RESP_BASE_ID||'->'||NEW.AVALIACAO_DES_RESP_BASE_ID;
    END
    
    IF (OLD.COLABORADOR_ID <> NEW.COLABORADOR_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' COL:'||OLD.COLABORADOR_ID||'->'||NEW.COLABORADOR_ID;
    END

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

    IF (OLD.COLABORADOR_CARGO_ID <> NEW.COLABORADOR_CARGO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' COL CARGO ID:'||OLD.COLABORADOR_CARGO_ID||'->'||NEW.COLABORADOR_CARGO_ID;
    END

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

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

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

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

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

    IF (OLD.PONTUACAO_TOTAL_FATOR <> NEW.PONTUACAO_TOTAL_FATOR) THEN
    BEGIN
        HISTORICO = HISTORICO||' PONT TOT FTR:'||OLD.PONTUACAO_TOTAL_FATOR||'->'||NEW.PONTUACAO_TOTAL_FATOR;
    END

    IF (OLD.PONTUACAO_MEDIA_FATOR <> NEW.PONTUACAO_MEDIA_FATOR) THEN
    BEGIN
        HISTORICO = HISTORICO||' PONT MED FTR:'||OLD.PONTUACAO_MEDIA_FATOR||'->'||NEW.PONTUACAO_MEDIA_FATOR;
    END

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

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

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_RESPOSTA', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_B1U IS 'Registra historico de alteracoes.';


CREATE TRIGGER TTBAVALIACAO_DES_RESP_A0U FOR TBAVALIACAO_DES_RESPOSTA
ACTIVE AFTER INSERT OR UPDATE POSITION 0
AS
DECLARE VARIABLE QTD_RESP INTEGER;
DECLARE VARIABLE RESPONDIDA CHAR(1);
BEGIN

    :QTD_RESP = (SELECT COUNT(R.ID)
                    FROM TBAVALIACAO_DES_RESPOSTA R
                    WHERE R.STATUSEXCLUSAO = '0'
                    AND R.AVALIACAO_DES_RESP_BASE_ID = NEW.AVALIACAO_DES_RESP_BASE_ID);

    IF (QTD_RESP > 0) THEN
        :RESPONDIDA = '1';
    ELSE
        :RESPONDIDA = '0';

    UPDATE TBAVALIACAO_DES_RESP_BASE B
    SET B.RESPONDIDA = :RESPONDIDA,
        B.VERIFICADOR = '0'
    WHERE B.ID = NEW.AVALIACAO_DES_RESP_BASE_ID;

END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_A0U IS 'Definir se a base da avaliacao ja tem alguma resposta.';






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




CREATE TABLE TBAVALIACAO_DES_RESP_FATOR (
    ID INTEGER NOT NULL,
    AVALIACAO_DES_RESPOSTA_ID INTEGER,
    TITULO VARCHAR(50),
    DESCRICAO VARCHAR(500),
    PONTO NUMERIC(18,4),
    AVALIACAO_DES_RESP_FAT_TIPO_ID INTEGER,
    ORDEM_PERC_NIVEL CHAR(1) DEFAULT '0',
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_RESP_FATOR
ADD CONSTRAINT PK_TBAVALIACAO_DES_RESP_FATOR
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_RESP_FATOR IS 'Fatores da resposta da avaliacao de desempenho.';
COMMENT ON COLUMN TBAVALIACAO_DES_RESP_FATOR.ORDEM_PERC_NIVEL IS
'Ordem da faixa de percentual nos niveis do fator. 0 - crescente e 1 - decrescente.';

CREATE SEQUENCE GTBAVALIACAO_DES_RESP_FATOR;


CREATE INDEX ITBAVALIACAO_DES_RESP_FATOR_01
ON TBAVALIACAO_DES_RESP_FATOR (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_RESP_FAT_B0I FOR TBAVALIACAO_DES_RESP_FATOR
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_RESP_FATOR, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_FAT_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_RESP_FAT_B0U FOR TBAVALIACAO_DES_RESP_FATOR
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_RESP_FATOR',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_FAT_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIACAO_DES_RESP_FAT_B1U FOR TBAVALIACAO_DES_RESP_FATOR
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_RESP_FATOR', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_FAT_B1U IS 'Registra historico de alteracoes.';




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




CREATE TABLE TBAVALIACAO_DES_RESP_FAT_TIPO (
    ID INTEGER NOT NULL,
    AVALIACAO_DES_RESPOSTA_ID INTEGER,
    TITULO VARCHAR(50),
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_RESP_FAT_TIPO
ADD CONSTRAINT PK_TBAVALIAC_DES_RESP_FAT_TIPO
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_RESP_FAT_TIPO IS 'Tipos dos fatores da resposta da avaliacao de desempenho.';

CREATE SEQUENCE GTBAVALIACAO_DES_RESP_FAT_TIPO;


CREATE INDEX ITBAVALIAC_DES_RESP_FAT_TIPO_01
ON TBAVALIACAO_DES_RESP_FAT_TIPO (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIAC_DES_RESP_FAT_TIP_B0I FOR TBAVALIACAO_DES_RESP_FAT_TIPO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_RESP_FAT_TIPO, 1);
END;

COMMENT ON TRIGGER TTBAVALIAC_DES_RESP_FAT_TIP_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIAC_DES_RESP_FAT_TIP_B0U FOR TBAVALIACAO_DES_RESP_FAT_TIPO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_RESP_FAT_TIPO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIAC_DES_RESP_FAT_TIP_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIAC_DES_RESP_FAT_TIP_B1U FOR TBAVALIACAO_DES_RESP_FAT_TIPO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_RESP_FAT_TIPO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIAC_DES_RESP_FAT_TIP_B1U IS 'Registra historico de alteracoes.';




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



CREATE TABLE TBAVALIACAO_DES_RESP_FAT_NIVEL (
    ID INTEGER NOT NULL,
    AVALIACAO_DES_RESPOSTA_ID INTEGER,
    AVALIACAO_DES_RESP_FATOR_ID INTEGER,
    TITULO VARCHAR(50),
    DESCRICAO VARCHAR(500),
    FAIXA_INICIAL INTEGER,
    FAIXA_FINAL INTEGER,
    DESCRITIVO_FAIXA_INICIAL INTEGER,
    DESCRITIVO_FAIXA_FINAL INTEGER,
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_RESP_FAT_NIVEL
ADD CONSTRAINT PK_TBAVALIAC_DES_RESP_FAT_NIVEL
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_RESP_FAT_NIVEL IS 'Descricao dos niveis para os fatores da resposta da avaliacao de desempenho.';

CREATE SEQUENCE GTBAVALIACAO_DES_RESP_FAT_NIVEL;


CREATE INDEX ITBAVALIAC_DES_RESP_FAT_NIV_01
ON TBAVALIACAO_DES_RESP_FAT_NIVEL (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIAC_DES_RESP_FAT_NIV_B0I FOR TBAVALIACAO_DES_RESP_FAT_NIVEL
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_RESP_FAT_NIVEL, 1);
END;

COMMENT ON TRIGGER TTBAVALIAC_DES_RESP_FAT_NIV_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIAC_DES_RESP_FAT_NIV_B0U FOR TBAVALIACAO_DES_RESP_FAT_NIVEL
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_RESP_FAT_NIVEL',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIAC_DES_RESP_FAT_NIV_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIAC_DES_RESP_FAT_NIV_B1U FOR TBAVALIACAO_DES_RESP_FAT_NIVEL
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

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

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

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

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_RESP_FAT_NIVEL', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIAC_DES_RESP_FAT_NIV_B1U IS 'Registra historico de alteracoes.';




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




CREATE TABLE TBAVALIACAO_DES_RESP_FORMACAO (
    ID INTEGER NOT NULL,
    AVALIACAO_DES_RESPOSTA_ID INTEGER,
    DESCRICAO VARCHAR(500),
    PONTO NUMERIC(18,4),
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_RESP_FORMACAO
ADD CONSTRAINT PK_TBAVALIACAO_DES_RESP_FORM
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_RESP_FORMACAO IS 'Formacao da resposta da avaliacao de desempenho.';

CREATE SEQUENCE GTBAVALIACAO_DES_RESP_FORMACAO;


CREATE INDEX ITBAVALIACAO_DES_RESP_FORM_01
ON TBAVALIACAO_DES_RESP_FORMACAO (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_RESP_FORM_B0I FOR TBAVALIACAO_DES_RESP_FORMACAO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_RESP_FORMACAO, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_FORM_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_RESP_FORM_B0U FOR TBAVALIACAO_DES_RESP_FORMACAO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_RESP_FORMACAO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_FORM_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIACAO_DES_RESP_FORM_B1U FOR TBAVALIACAO_DES_RESP_FORMACAO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_RESP_FORMACAO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_FORM_B1U IS 'Registra historico de alteracoes.';




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




CREATE TABLE TBAVALIACAO_DES_RESP_RESUMO (
    ID INTEGER NOT NULL,
    AVALIACAO_DES_RESPOSTA_ID INTEGER,
    DESCRICAO VARCHAR(100),
    PESO NUMERIC(18,4),
    PONTUACAO_GERAL NUMERIC(18,4),
    RESULTADO NUMERIC(18,4),
    FATOR_TIPO_ID VARCHAR(20),
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBAVALIACAO_DES_RESP_RESUMO
ADD CONSTRAINT PK_TBAVALIACAO_DES_RESP_RES
PRIMARY KEY (ID);

COMMENT ON TABLE TBAVALIACAO_DES_RESP_RESUMO IS 'Resumo da resposta da avaliacao de desempenho.';

CREATE SEQUENCE GTBAVALIACAO_DES_RESP_RESUMO;


CREATE INDEX ITBAVALIACAO_DES_RESP_RES_01
ON TBAVALIACAO_DES_RESP_RESUMO (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBAVALIACAO_DES_RESP_RES_B0I FOR TBAVALIACAO_DES_RESP_RESUMO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBAVALIACAO_DES_RESP_RESUMO, 1);
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_RES_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBAVALIACAO_DES_RESP_RES_B0U FOR TBAVALIACAO_DES_RESP_RESUMO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_RESP_RESUMO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_RES_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBAVALIACAO_DES_RESP_RES_B1U FOR TBAVALIACAO_DES_RESP_RESUMO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBAVALIACAO_DES_RESP_RESUMO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBAVALIACAO_DES_RESP_RES_B1U IS 'Registra historico de alteracoes.';





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


CREATE TABLE TBINDICADOR_CCUSTO (
    ID INTEGER NOT NULL,
    INDICADOR_ID INTEGER,
    CCUSTO_CODIGO VARCHAR(10),
    PERC_INDICADOR NUMERIC(18,4),
    DATA_INI DATE,
    DATA_FIM DATE,
    STATUSEXCLUSAO CHAR(1) DEFAULT '0',
    DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
    DATAHORA_UPDATE TIMESTAMP,
    DATAHORA_DELETE TIMESTAMP
);

ALTER TABLE TBINDICADOR_CCUSTO
ADD CONSTRAINT PK_TBINDICADOR_CCUSTO
PRIMARY KEY (ID);

COMMENT ON TABLE TBINDICADOR_CCUSTO IS 'Percentuais dos indicadores por centro de custo.';

CREATE SEQUENCE GTBINDICADOR_CCUSTO;


CREATE INDEX ITBINDICADOR_CCUSTO_01
ON TBINDICADOR_CCUSTO (STATUSEXCLUSAO);



CREATE OR ALTER TRIGGER TTBINDICADOR_CCUSTO_B0I FOR TBINDICADOR_CCUSTO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBINDICADOR_CCUSTO, 1);
END;

COMMENT ON TRIGGER TTBINDICADOR_CCUSTO_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBINDICADOR_CCUSTO_B0U FOR TBINDICADOR_CCUSTO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBINDICADOR_CCUSTO',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBINDICADOR_CCUSTO_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';


CREATE TRIGGER TTBINDICADOR_CCUSTO_B1U FOR TBINDICADOR_CCUSTO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

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

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBINDICADOR_CCUSTO', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    END
END;

COMMENT ON TRIGGER TTBINDICADOR_CCUSTO_B1U IS 'Registra historico de alteracoes.';
