You don't *need* to use a trigger, but using your solution has the
same problem - if there is an uncommitted record in another
transaction (with an overlapping time-period), you won't see it, and
it won't see you.

You'll both commit fine. And then you've got a problem.

You'd have to do the select everytime you *used* the table, not just
every time you posted to it. (or more specifically, at the start of
any transaction that used the table).

The only workaround I can think of (that doesn't use locking):

quantify the timestamps as an integer or something (ie representing
each 10-minute time period as in cast(ts * 144.000 as integer) ).
Then store these in a table like:

create table X (idcode integer not null, timecode integer not null,
primary key(idcode,timecode));

So a 1 hour period would insert 6 records into this table. A 1 day
period 144 records.

Post a record in the original table that overlaps an existing one,
and the trigger will cause duplicate keys to be entered into table X,
causing an exception!

Problem solved. If you can cope with 10 minute resolution. And
potentially millions of records that arn't used for anything except a
constraint.

Personally I'd probably go with using a selectable sproc to access my
original table. And cope with any dup periods in that. Planning would
be screwed, of course.

John

--- In firebird-support@yahoogroups.com, Lester Caine <lester@l...>
wrote:

> johnsparrowuk wrote:
>
> > Just tell me how I can solve my period-overlap problem???!?!?! :0
>
> Why do you need to check it in a trigger?
> I just do a select for overlapping room use before posting the

info, and

> don't do the post if the select fails. It's worked OK so far :)
>
> --
> Lester Caine
> -----------------------------
> L.S.Caine Electronic Services