CREATE trigger ttbcalendario_evento_b04u for tbcalendario_evento
active before update position 4
AS
    DECLARE VARIABLE DATA_VER INT;
BEGIN

    DATA_VER = COALESCE((SELECT
                            COUNT (INICIO)
                         FROM TBCALENDARIO_EVENTO a
                         WHERE a.consultorio_id = new.consultorio_id
                         and a.profissional_id  = new.profissional_id
                         and (
                            a.INICIO    between NEW.inicio and new.fim or
                            a.fim       between NEW.inicio and new.fim or
                            NEW.inicio  between   A.inicio and   A.fim or
                            new.fim     between   A.inicio and   A.fim
                         )
               ), 0);

    IF(DATA_VER > 0) THEN
    BEGIN
        EXCEPTION "..." 'HORARIO OCUPADO';
    END
END;


CREATE OR ALTER trigger ttbcalendario_evento_b04i for tbcalendario_evento
active before insert position 4
AS
    DECLARE VARIABLE DATA_VER INT;
BEGIN

    DATA_VER = COALESCE((SELECT
                            COUNT (INICIO)
                         FROM TBCALENDARIO_EVENTO a
                         WHERE a.consultorio_id = new.consultorio_id
                         and a.profissional_id  = new.profissional_id
                         and (
                            a.INICIO    between NEW.inicio and new.fim or
                            a.fim       between NEW.inicio and new.fim or
                            NEW.inicio  between   A.inicio and   A.fim or
                            new.fim     between   A.inicio and   A.fim
                         )
               ), 0);

    IF(DATA_VER > 0) THEN
    BEGIN
        EXCEPTION "..." 'HORARIO OCUPADO';
    END
END

