Hi Lester,
why don't you use this for cancel operations:

TICKET_ID = - PRIMARY_KEY_ID;

it will prevent any duplication and you can identify all canceled
tickets by ticket_id<0. it is my favorite tecknique for cancelled
rows.

Regards.
Ali

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

>
> 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 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.
>