Don't forget the check constraint on the table so start is less than
end.

insert into reservations(id,starttime,endtime) values (_id,_start,_end)
where not exists ( select id from reservations where (_start <=
starttime and _end > starttime)
or (_start > starttime and _end < endtime)
or (_start < endtime and _end > starttime))

I'm pretty sure the where statement is wrong. If zero rows were
updated, then your exists constraint was the problem, and you can handle
it accordingly.

-----Original Message-----
From: Ann W. Harrison [mailto:aharrison@...]
Sent: Wednesday, January 12, 2005 1:36 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: Locking tables


Ann W. Harrison wrote:

>
> I'd have triggers on the reservations table ....

Thinking about it a bit more, I'd use gateway approach - relying purely
on triggers leaves the possibility of undetected simultaneous
inserts/updates that would not be detected.

The update of the gateway records should be in the same transaction as
the insert/update of the reservation records.

Regards,


Ann




Yahoo! Groups Links