CREATE GENERATOR GTBCASO_PAINEL_PROCEDIMENTO;

CREATE TABLE TBCASO_PAINEL_PROCEDIMENTO (
    ID         INTEGER NOT NULL,
    PAINEL_ID  INTEGER,
    TABELA     VARCHAR(50) CHARACTER SET NONE,
    MOTIVO_ID  INTEGER,
    TIPO_ID    INTEGER,
    ORIGEM_ID  INTEGER,
    OPERACAO   INTEGER DEFAULT 1
);




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

ALTER TABLE TBCASO_PAINEL_PROCEDIMENTO ADD CONSTRAINT PK_TBCASO_PAINEL_PROCEDIMENTO PRIMARY KEY (ID);


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



SET TERM ^ ;



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



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

    HISTORICO = '';

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'PROCEDIMENTO '||FN_LPAD(OLD.ID, 4, '0')||' - '||OLD.TABELA||' INCLUIDO';
    END

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


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


/* Trigger: TTBCASO_PAINEL_PROC_B01U */
CREATE OR ALTER TRIGGER TTBCASO_PAINEL_PROC_B01U FOR TBCASO_PAINEL_PROCEDIMENTO
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_MOTIVO VARCHAR(500);
DECLARE VARIABLE NEW_MOTIVO VARCHAR(500);
DECLARE VARIABLE OLD_TIPO VARCHAR(500);
DECLARE VARIABLE NEW_TIPO VARCHAR(500);
DECLARE VARIABLE OLD_ORIGEM VARCHAR(500);
DECLARE VARIABLE NEW_ORIGEM VARCHAR(500);
BEGIN

    HISTORICO = '';

    OLD_MOTIVO  = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.DESCRICAO FROM TBCASO_MOTIVO A WHERE A.ID = OLD.MOTIVO_ID), '');
    NEW_MOTIVO  = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.DESCRICAO FROM TBCASO_MOTIVO A WHERE A.ID = NEW.MOTIVO_ID), '');
    OLD_TIPO    = COALESCE((SELECT FIRST 1 FN_LPAD(B.ID, 4, '0')||' - '||B.DESCRICAO FROM TBCASO_TIPO B WHERE B.ID = OLD.TIPO_ID), '');
    NEW_TIPO    = COALESCE((SELECT FIRST 1 FN_LPAD(B.ID, 4, '0')||' - '||B.DESCRICAO FROM TBCASO_TIPO B WHERE B.ID = NEW.TIPO_ID), '');
    OLD_ORIGEM  = COALESCE((SELECT FIRST 1 FN_LPAD(C.ID, 4, '0')||' - '||C.DESCRICAO FROM TBCASO_ORIGEM C WHERE C.ID = OLD.ORIGEM_ID), '');
    NEW_ORIGEM  = COALESCE((SELECT FIRST 1 FN_LPAD(C.ID, 4, '0')||' - '||C.DESCRICAO FROM TBCASO_ORIGEM C WHERE C.ID = NEW.ORIGEM_ID), '');

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

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

    IF (OLD.MOTIVO_ID <> NEW.MOTIVO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' OPERACAO:'||OLD_MOTIVO||'->'||NEW_MOTIVO;
    END

    IF (OLD.TIPO_ID <> NEW.TIPO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' OPERACAO:'||OLD_TIPO||'->'||NEW_TIPO;
    END

    IF (OLD.ORIGEM_ID <> NEW.ORIGEM_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' OPERACAO:'||OLD_ORIGEM||'->'||NEW_ORIGEM;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'PROCEDIMENTO '||FN_LPAD(OLD.ID, 4, '0')||' - '||NEW.TABELA||' ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBCASO_PAINEL', OLD.PAINEL_ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^


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

    HISTORICO = '';

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

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

SET TERM ; ^



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

COMMENT ON COLUMN TBCASO_PAINEL_PROCEDIMENTO.OPERACAO IS 
'1 - Apos Incluir;
2 - Apos Alterar;
3 - Apos Excluir;';


/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbcaso_painel_b02d for tbcaso_painel
active before delete position 1
AS
BEGIN
   IF(OLD.ID > 0) THEN
   BEGIN
      DELETE FROM TBCASO_VISUALIZACAO_CONF V WHERE V.PAINEL_ID = OLD.ID;    -- VISUALIZAÇÃO ESTÁ VINCULADA COM PAINEL DURANTE INCLUSÃO.
      DELETE FROM TBCASO_SQL S WHERE S.ID = OLD.SQL_EMAIL_CONTATO;          -- SQL DO CAMPO DOS EMAILS DOS CONATATOS

      DELETE FROM TBCASO_PAINEL_CONF A WHERE A.PAINEL_ID = OLD.ID;          -- CAMPOS (ALT+2)
      DELETE FROM TBCASO_USUARIOS B WHERE B.PAINEL_ID = OLD.ID;             -- USUÁRIOS (ALT+3)
      DELETE FROM TBCASO_PARAMETROS_USUARIO C WHERE C.PAINEL_ID = OLD.ID;   -- PARÂMETROS (ALT+4)
      DELETE FROM TBCASO_STATUS D WHERE D.PAINEL_ID = OLD.ID;               -- STATUS (ALT+5)
      DELETE FROM TBCASO_MOTIVO E WHERE E.PAINEL_ID = OLD.ID;               -- MOTIVO (ALT+6)
      DELETE FROM TBCASO_VISUALIZACAO_CAMPO F WHERE F.PAINEL_ID = OLD.ID;   -- VISUALIZAÇÃO (ALT+8)
      DELETE FROM TBCASO_VALIDACAO G WHERE G.PAINEL_ID = OLD.ID;            -- VALIDAÇÃO (ALT+9)
      DELETE FROM TBCASO_CONTATO_CONF H WHERE H.PAINEL_ID = OLD.ID;         -- CONTATO (ALT+0)
      DELETE FROM TBCASO_CAMPOS_AGRUPAMENTO I WHERE I.PAINEL_ID = OLD.ID;   -- AGRUPAMENTOS (ALT+7)
      DELETE FROM TBCASO_WORKFLOWS J WHERE J.PAINEL_ID = OLD.ID;            -- MODELOS DE WORKFLOW (ALT+R)

      DELETE FROM TBCASO_ACAO_ENCERRAMENTO K WHERE K.PAINEL_ID = OLD.ID;    -- ACOES DE ENCERRAMENTO DOS CASOS (DISPARO DE NOVOS CASOS)
      DELETE FROM TBCASO_PAINEL_PROCEDIMENTO L WHERE L.PAINEL_ID = OLD.ID;  -- ACOES DE TABELAS DO SISTEMA (DISPARO DE NOVOS CASOS)
      
      -- DELETE FROM TBMENU M WHERE M.CONTROLE = 99000 + OLD.ID;               -- EXCLUSÃO DO PAINEL DA LISTA DE MENUS
   END
END
^

SET TERM ; ^

