Ismael L. Donis Garcia wrote:
> I accomplish the following (I no if it will be OK )
My own procedure is quite simple - some sections stripped just to leave the
basics
CREATE TRIGGER UPDATETICKET ON TICKET
BEFORE UPDATE POSITION 0
AS
BEGIN
IF ( OLD.ROOM <> NEW.ROOM OR OLD.DEPARTMENT <> NEW.DEPARTMENT )
THEN BEGIN
IF ( (CURRENT_TIMESTAMP - OLD.LAST) < 1.0 ) THEN
INSERT INTO TRANSACTIONS ( TICKET_ID, TICKET_REF, TICKET_NO, TRANSACT,
STAFF_ID, PREVIOUS, ROOM, OFFICE, APPLET, PROOM, TRANSACT_NO, TAGS,
CLEARANCE)
VALUES ( OLD.TICKET_ID, OLD.TICKET_REF, OLD.TICKET_NO, CURRENT_TIMESTAMP,
NEW.STAFF_ID,
((CURRENT_TIMESTAMP - OLD.LAST) * 86400), OLD.ROOM,
OLD.OFFICE, NEW.APPLET,
COALESCE (( SELECT FIRST 1 ROOM FROM TRANSACTIONS
WHERE TICKET_ID = OLD.TICKET_ID ORDER BY TRANSACT DESC ), 0),
COALESCE (( SELECT FIRST 1 TRANSACT_NO FROM TRANSACTIONS
WHERE TICKET_ID = OLD.TICKET_ID ORDER BY TRANSACT DESC )+1, 0),
OLD.TAGS, OLD.CLEARANCE );
NEW.LAST = CURRENT_TIMESTAMP;
END
END
The important bit with this is that only one person can be updating the details
on a TICKET_ID at any time, and until the ROOM entry in TICKET has changed, it
can't be used by anybody else ... so while a ticket is being updated there
SHOULD only be one open transaction using it. And in many cases there are only
a
couple of detail record entries anyway.
------
I think I've tracked the source of the problem though ... I'm running Firebird
classic, and the xinetd.d settings had been corrupted :( So it had lost the
instances = UNLIMITED and at that time of the day we have 20 odd people all
working, so some connections were being lost. The staff member who's ticket was
the problem has now admitted that they got an error message ( it's in the log
file ;) ) - but that they just restarted the browser so they could move on.
Life
would be so easier without users :) The strange thing is that the system did
not
crash until 3 hours later ... and I was not looking far enough back to see the
real problem!
I still have to explain why the 'stuck transactions' were not simply rolled
back? PHP should have terminated the transaction.
--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php
