-- TBUSUARIO_BI

DROP TRIGGER TBUSUARIO_BI_BI;

SET TERM ^ ;

CREATE OR ALTER TRIGGER TTBUSUARIO_BI_B01I FOR TBUSUARIO_BI
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GTBUSUARIO_BI,1);
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER TRIGGER TTBUSUARIO_BI_B03I FOR TBUSUARIO_BI
ACTIVE BEFORE INSERT POSITION 10
AS
DECLARE VARIABLE HISTORICO VARCHAR(500); 
DECLARE VARIABLE USUARIO VARCHAR(100);
DECLARE VARIABLE ID_ARQUIVO INTEGER;
BEGIN

    HISTORICO = '';
    USUARIO = COALESCE((SELECT FN_LPAD(U.CODIGO, 4, '0')||' - '||U.USUARIO FROM TBUSUARIO U WHERE U.CODIGO = NEW.USUARIO_ID), '');
    ID_ARQUIVO = COALESCE((SELECT A.ID FROM TBBI_PAINEIS A WHERE A.NOME = NEW.ARQUIVO), 0);

    IF(NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'USUARIO: '||COALESCE(USUARIO, FN_LPAD(NEW.USUARIO_ID, 4, '0'))||' INCLUIDO';
    END
    
    IF(HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBBI_PAINEIS', ID_ARQUIVO, HISTORICO);
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER TRIGGER TTBUSUARIO_BI_B02U FOR TBUSUARIO_BI
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE QVF VARCHAR(1000);
BEGIN

    QVF = NEW.ARQUIVO||'-'||NEW.USUARIO_ID;

    IF(OLD.ARQUIVO <> NEW.ARQUIVO) THEN
    BEGIN
         NEW.QVF = :QVF;
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER TRIGGER TTBUSUARIO_BI_B02D FOR TBUSUARIO_BI
ACTIVE BEFORE DELETE POSITION 10
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE USUARIO VARCHAR(100);
DECLARE VARIABLE ID_ARQUIVO INTEGER;
BEGIN

    HISTORICO = '';
    USUARIO = COALESCE((SELECT FN_LPAD(U.CODIGO, 4, '0')||' - '||U.USUARIO FROM TBUSUARIO U WHERE U.CODIGO = OLD.USUARIO_ID), '');
    ID_ARQUIVO = COALESCE((SELECT A.ID FROM TBBI_PAINEIS A WHERE A.NOME = OLD.ARQUIVO), 0);

    IF(OLD.USUARIO_ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'USUARIO: '||COALESCE(USUARIO ,FN_LPAD(OLD.USUARIO_ID, 4, '0'))||' EXCLUIDO';
    END
    
    IF(HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBBI_PAINEIS', ID_ARQUIVO, HISTORICO);
    END
END
^

SET TERM ; ^

-- TBBI_PAINEIS

SET TERM ^ ;

CREATE OR ALTER TRIGGER TTBBI_PAINEIS_B02U FOR TBBI_PAINEIS
ACTIVE BEFORE UPDATE POSITION 1
AS
BEGIN
    IF(NEW.NOME <> OLD.NOME)THEN
    BEGIN
       UPDATE TBUSUARIO_BI B SET B.ARQUIVO = NEW.NOME WHERE B.ARQUIVO = OLD.NOME;
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER TRIGGER TTBBI_PAINEIS_B01D FOR TBBI_PAINEIS
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('TBBI_PAINEIS', OLD.ID, HISTORICO);
    END

END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER TRIGGER TTBBI_PAINEIS_B02D FOR TBBI_PAINEIS
ACTIVE BEFORE DELETE POSITION 1
AS
BEGIN
  DELETE FROM TBUSUARIO_BI A WHERE A.ARQUIVO = OLD.NOME;
END
^

SET TERM ; ^

