SET TERM ^ ;

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

     DATA_VER1 = COALESCE((SELECT
                            COUNT (INICIO)
                         FROM TBCALENDARIO_EVENTO A
                         WHERE A.PROFISSIONAL_ID  = NEW.PROFISSIONAL_ID
                         AND A.ID <> NEW.ID
                         AND A.PROFISSIONAL_ID > 0
                         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);

     DATA_VER2 = COALESCE((SELECT
                            COUNT (INICIO)
                         FROM TBCALENDARIO_EVENTO A
                         WHERE A.CONSULTORIO_ID = NEW.CONSULTORIO_ID
                         AND A.ID <> NEW.ID
                         AND A.CONSULTORIO_ID > 0
                         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_VER1 > 0 OR DATA_VER2 > 0) THEN
    BEGIN
        EXCEPTION "..." 'HORARIO OCUPADO, EM:'||formatdatetime(NEW.INICIO)||' A '||formatdatetime(NEW.FIM);
    END
END^

SET TERM ; ^

