/******************************************************************************/
/****              Generated by IBExpert 14/08/2023 14:42:07               ****/
/******************************************************************************/

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



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


CREATE GENERATOR GTBDRE_FINANCEIRA_GRUPOS_SQL;

CREATE TABLE TBDRE_FINANCEIRA_GRUPOS_SQL (
    ID                       INTEGER,
    DRE_FINANCEIRA_ID        INTEGER,
    DRE_FINANCEIRA_GRUPO_ID  INTEGER,
    DESCRICAO                VARCHAR(200),
    STATUS                   INTEGER DEFAULT 0,
    SQL                      BLOB SUB_TYPE 1 SEGMENT SIZE 80
);




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

CREATE INDEX ITBDRE_FINANCEIRA_GRUPOS_SQL_01 ON TBDRE_FINANCEIRA_GRUPOS_SQL (ID);
CREATE INDEX ITBDRE_FINANCEIRA_GRUPOS_SQL_02 ON TBDRE_FINANCEIRA_GRUPOS_SQL (DRE_FINANCEIRA_ID);
CREATE INDEX ITBDRE_FINANCEIRA_GRUPOS_SQL_03 ON TBDRE_FINANCEIRA_GRUPOS_SQL (DRE_FINANCEIRA_GRUPO_ID);


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



SET TERM ^ ;



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



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

    HISTORICO = '';

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

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'GRUPO '||COALESCE((SELECT FIRST 1 FN_LPAD(GRUPO.ID,4,0)||' - '||GRUPO.DESCRICAO FROM TBDRE_FINANCEIRA_GRUPOS GRUPO WHERE GRUPO.ID = OLD.DRE_FINANCEIRA_GRUPO_ID),'')||' ALTERADA:'||HISTORICO;
       
        EXECUTE PROCEDURE SPI_HISTORICO('TBDRE_FINANCEIRA',OLD.DRE_FINANCEIRA_ID, HISTORICO);
    END
END
^

/* Trigger: TTBDRE_FINANCEIRA_GRUPOS_SQL_B01U */
CREATE OR ALTER TRIGGER TTBDRE_FIN_GRUPOS_SQL_B01U FOR TBDRE_FINANCEIRA_GRUPOS_SQL
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 ( OLD.SQL <> NEW.SQL) THEN
    BEGIN
        HISTORICO = HISTORICO||' SQL ALTERADO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'GRUPO: '||FN_LPAD(OLD.ID,4,0)||' - '||COALESCE(OLD.DESCRICAO, '')||', SQL '||FN_LPAD(OLD.ID,4, 0)||' - '||(OLD.DESCRICAO)||' ALTERADO'||HISTORICO;

        EXECUTE PROCEDURE SPI_HISTORICO('TBDRE_FINANCEIRA',NEW.DRE_FINANCEIRA_ID, HISTORICO);
    END
END
^

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


/* Trigger: TITBDRE_FINANCEIRA_GRUPOS_SQL_B02I */
CREATE OR ALTER TRIGGER TITBDRE_FIN_GRUPOS_SQL_B02I FOR TBDRE_FINANCEIRA_GRUPOS_SQL
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

   HISTORICO = '';

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

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'GRUPO '||COALESCE((SELECT FIRST 1 FN_LPAD(GRUPO.ID,4,0)||' - '||GRUPO.DESCRICAO FROM TBDRE_FINANCEIRA_GRUPOS GRUPO WHERE GRUPO.ID = NEW.DRE_FINANCEIRA_GRUPO_ID),'')||' ALTERADA:'||HISTORICO;
       
        EXECUTE PROCEDURE SPI_HISTORICO('TBDRE_FINANCEIRA',NEW.DRE_FINANCEIRA_ID, HISTORICO);
    END
END
^

SET TERM ; ^



/******************************************************************************/
/****                             Descriptions                             ****/
/******************************************************************************/

COMMENT ON TABLE TBDRE_FINANCEIRA_GRUPOS_SQL IS 
'tabela de sqls personalizados de grupos da DRE financeira';



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


/* Privileges of users */
GRANT ALL ON TBDRE_FINANCEIRA_GRUPOS_SQL TO GC;
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;

/* Privileges of roles */
GRANT ALL ON TBDRE_FINANCEIRA_GRUPOS_SQL TO RDB$ADMIN;