Now that I am running a couple of web servers into the database server I
was running into a problem with people trying to book the same room at
the same time, so I've introduces a unique index on the correct fields
and the first problem is under control, but now I'm trying to manage
'cancel' and 're-schedule'.
The index is
APPOINT_DATE, APPOINT_TIME, OFFICE, ROOM, TICKET_ID
and in the past I've set TICKET_ID to -1 to indicate a cancelled
appointment, so that the information on the appointment is still
available. With the new unique index this obviously fails when a second
cancelled appointment is made for the same time slot ( don't you just
love customers who find the holes within 24 hours of putting the fix in
;) ).
So my solution was fairly simple, just use any -ve number to indicate a
cancelled appointment, and update it via a trigger

CREATE TRIGGER CANCEL_CHECK FOR APPOINTMENT
BEFORE
UPDATE
AS
DECLARE VARIABLE TIC_ID INTEGER;
BEGIN
IF ( NEW.TICKET_ID < 0 ) THEN
BEGIN
SELECT COALESCE( MIN(TICKET_ID), 0 )
FROM APPOINTMENT
WHERE APPOINT_DATE = OLD.APPOINT_DATE
AND APPOINT_TIME = OLD.APPOINT_TIME
AND OFFICE = OLD.OFFICE
AND ROOM = OLD.ROOM
INTO :TIC_ID;
NEW.TICKET_ID = TIC_ID-1;
END
END

In theory it just uses the next -ve TICKET_ID if there is a clash, or -1
if there isn't.
Except it will not work - I keep getting "index unexpectedly deleted"
when trying to update the record. There is a primary key on the table
supplied by a generator, but obviously changing the value in the unique
index is being complained at despite the fact that the record being
updated is being selected with the primary key which does not change.

What am I doing wrong here?

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services
Treasurer - Firebird Foundation Inc.