/******************************************************************************/
/****              Generated by IBExpert 04/03/2024 16:18:09               ****/
/******************************************************************************/

/******************************************************************************/
/****     Following SET SQL DIALECT is just for the Database Comparer      ****/
/******************************************************************************/
SET SQL DIALECT 3;



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


CREATE GENERATOR GTBMODELO_COMBINACAO_CATEGORIA;

CREATE TABLE TBMODELO_COMBINACAO_CATEGORIA (
    ID                  INTEGER NOT NULL,
    COMBINACAO_ID       INTEGER,    
    CATEGORIA_ID        INTEGER
);




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

ALTER TABLE TBMODELO_COMBINACAO_CATEGORIA ADD CONSTRAINT PK_TBMODELO_COMBINACAO_CAT PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX ITBMODELO_COMBINACAO_CAT_01 ON TBMODELO_COMBINACAO_CATEGORIA (COMBINACAO_ID);
CREATE INDEX ITBMODELO_COMBINACAO_CAT_02 ON TBMODELO_COMBINACAO_CATEGORIA (CATEGORIA_ID);


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



SET TERM ^ ;



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



/* Trigger: TTBMODELO_COMBINACAO_CATEGORIA_B01D */
CREATE OR ALTER TRIGGER TTBMODELO_COMBINACAO_CAT_B01D FOR TBMODELO_COMBINACAO_CATEGORIA
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE COMBINACAO VARCHAR(500);
DECLARE VARIABLE OLD_CATEGORIA VARCHAR(500);
BEGIN

    HISTORICO       = '';
    COMBINACAO    = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.DESCRICAO FROM TBMODELO_COMBINACAO A WHERE A.ID = OLD.COMBINACAO_ID), '');
    OLD_CATEGORIA = COALESCE((SELECT FIRST 1 FN_LPAD(CAT.ID, 4, '0')||' - '||CAT.DESCRICAO FROM TBCATEGORIA_LACLOFIT CAT WHERE CAT.ID = OLD.CATEGORIA_ID),'');
    
    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' CATEGORIA: '||OLD_CATEGORIA||' EXCLUIDA NA COMBINACAO: '||COMBINACAO||'';
    END

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


/* Trigger: TTBMODELO_COMBINACAO_CATEGORIA_B01I */
CREATE OR ALTER TRIGGER TTBMODELO_COMBINACAO_CAT_B01I FOR TBMODELO_COMBINACAO_CATEGORIA
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gTBMODELO_COMBINACAO_CATEGORIA,1);
end
^

/* Trigger: TTBMODELO_COMBINACAO_CATEGORIA_B02I */
CREATE OR ALTER TRIGGER TTBMODELO_COMBINACAO_CAT_B02I FOR TBMODELO_COMBINACAO_CATEGORIA
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE COMBINACAO VARCHAR(500);
DECLARE VARIABLE NEW_CATEGORIA VARCHAR(500);
BEGIN

    HISTORICO     = '';
    COMBINACAO    = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.DESCRICAO FROM TBMODELO_COMBINACAO A WHERE A.ID = NEW.COMBINACAO_ID), '');
    NEW_CATEGORIA = COALESCE((SELECT FIRST 1 FN_LPAD(CAT.ID, 4, '0')||' - '||CAT.DESCRICAO FROM TBCATEGORIA_LACLOFIT CAT WHERE CAT.ID = NEW.CATEGORIA_ID),'');
    
    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' CATEGORIA: '||NEW_CATEGORIA||' INCLUIDA NA COMBINACAO: '||COMBINACAO||'';
    END

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

SET TERM ; ^



/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/


/* Privileges of users */
GRANT SELECT ON MON$ATTACHMENTS TO PUBLIC;
GRANT SELECT ON MON$CALL_STACK TO PUBLIC;
GRANT SELECT ON MON$CONTEXT_VARIABLES TO PUBLIC;
GRANT SELECT ON MON$DATABASE TO PUBLIC;
GRANT SELECT ON MON$IO_STATS TO PUBLIC;
GRANT SELECT ON MON$MEMORY_USAGE TO PUBLIC;
GRANT SELECT ON MON$RECORD_STATS TO PUBLIC;
GRANT SELECT ON MON$STATEMENTS TO PUBLIC;
GRANT SELECT ON MON$TABLE_STATS TO PUBLIC;
GRANT SELECT ON MON$TRANSACTIONS TO PUBLIC;