I'm glad it's not just me! I assume the only answer is to use a high
degree of locking? Like table_stability or "select * from T with
lock". Ouch.

Of course you then loose all multi-gen advantages!

Otherwise you just have to *hope* nothing sneaks through the trigger
below, and to be properly sure, each time you use the table, scan it
again in your new transaction context to ensure nothing invalid
exists.

I suppose the whole idea of constraints between records (rather than
just inside a single record) isn't all that well suited to multi-gen
databases.

They even had to 'cheat' to get PK's to work!!! ;)

At least I know of this limitation now. I always wondered why people
like M$ didn't use multi-gen. Now I know...

John

--- In firebird-support@yahoogroups.com, "Jerome Bouvattier"
<JBouvattier@I...> wrote:

> Hi,
>
> > Any idea how to check periods don't overlap?? Ie in a table:
> >
> > create table T (entryid integer not null primary key, startdate
> > timestamp not null, enddate timestamp not null);
> >
> > I could have a trigger like this:
> >
> > /* Before insert / update trigger */
> > if (exists(select entryid from T where not(T.startdate >

new.enddate

> > or T.enddate < new.startdate) and T.entryid <> new.entryid)) then
> > begin
> > exception dates_overlap 'Your dates overlap';
> > end
> >
> >
> > But hey-ho, I can't dirty-read so it doesn't work properly!!!
> >
> > Come on guys, am I the only one who finds this a bit of a

problem??

>
> No, No. You're not. <g>