-- TBTESTE_NOVOCRIAR (TABELA)

CREATE GENERATOR GTBTESTE_NOVOCRIAR;

CREATE TABLE TBTESTE_NOVOCRIAR (
    ID          INTEGER NOT NULL,
    DESCRICAO   VARCHAR(50),
    STATUS      CHAR(1),
    OBSERVACAO  VARCHAR(500)
);




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

ALTER TABLE TBTESTE_NOVOCRIAR ADD CONSTRAINT PK_TBTESTE_NOVOCRIAR PRIMARY KEY (ID);

-- TBTESTE_NOVOCRIAR (TABELA)

CREATE GENERATOR GTBTESTE_NOVOCRIAR_ITENS;

CREATE TABLE TBTESTE_NOVOCRIAR_ITENS (
    ID         INTEGER NOT NULL,
    TESTE_ID   INTEGER NOT NULL,
    DESCRICAO  VARCHAR(20)
);




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

ALTER TABLE TBTESTE_NOVOCRIAR_ITENS ADD CONSTRAINT PK_TBTESTE_NOVOCRIAR_ITENS PRIMARY KEY (ID);

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

COMMENT ON COLUMN TBTESTE_NOVOCRIAR_ITENS.TESTE_ID IS 
'ID do registro pai da tabela TBTESTE_NOVOCRIAR';

---------------------------------------------------------------------------------------------------
-- TBTESTE_NOVOCRIAR (TRIGGERS)

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



SET TERM ^ ;



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



/* Trigger: TTBTESTE_NOVOCRIAR_B01D */
CREATE OR ALTER TRIGGER TTBTESTE_NOVOCRIAR_B01D FOR TBTESTE_NOVOCRIAR
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('TBTESTE_NOVOCRIAR', OLD.ID, HISTORICO);
    END
END
^


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


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

    HISTORICO = '';

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

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


/* Trigger: TTBTESTE_NOVOCRIAR_B02D */
CREATE OR ALTER TRIGGER TTBTESTE_NOVOCRIAR_B02D FOR TBTESTE_NOVOCRIAR
ACTIVE BEFORE DELETE POSITION 1
AS
BEGIN
  DELETE FROM TBTESTE_NOVOCRIAR_ITENS A WHERE A.TESTE_ID = OLD.ID;
END
^


/* Trigger: TTBTESTE_NOVOCRIAR_B02I */
CREATE OR ALTER TRIGGER TTBTESTE_NOVOCRIAR_B02I FOR TBTESTE_NOVOCRIAR
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('TBTESTE_NOVOCRIAR', NEW.ID, HISTORICO);
    END
END
^

SET TERM ; ^

-- TBTESTE_NOVOCRIAR_ITENS (TRIGGERS)

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



SET TERM ^ ;



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



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

    HISTORICO = '';

    IF(OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO '||FN_LPAD(OLD.ID, 4, '0')||' - '||OLD.DESCRICAO||' EXCLUIDO';
    END
    
    IF(HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBTESTE_NOVOCRIAR', OLD.TESTE_ID, HISTORICO);
    END
END
^


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


/* Trigger: TTBTESTE_NOVOCRIAR_ITENS_B01U */
CREATE OR ALTER TRIGGER TTBTESTE_NOVOCRIAR_ITENS_B01U FOR TBTESTE_NOVOCRIAR_ITENS
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF(OLD.DESCRICAO <> NEW.DESCRICAO)THEN
    BEGIN
        HISTORICO = HISTORICO||' DESC:'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END
    
    IF(HISTORICO <> '') THEN
    BEGIN
        HISTORICO = ' REGISTRO '||FN_LPAD(OLD.ID, 4, '0')||' - '||NEW.DESCRICAO||' ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBTESTE_NOVOCRIAR', OLD.TESTE_ID, HISTORICO);
    END
END
^


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

    HISTORICO = '';

    IF(NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO '||FN_LPAD(NEW.ID, 4, '0')||' - '||NEW.DESCRICAO||' INCLUIDO';
    END
    
    IF(HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBTESTE_NOVOCRIAR', NEW.TESTE_ID, HISTORICO);
    END
END
^

SET TERM ; ^