Hongdong wrote:
> I just have a base question:
> assume user A and user B now both connection to a same database and both of
> them want to update a same record,but only one is allowed
> in this condition:
> A begin to browse the data in a client application,and load rowid into GUI
> and keep it in memory.
> B update the record which A want to update
> A now used the id stored in memory to update the same record
> now it's wrong.
> anyone can give me advice how to avoid this and keep the operation is
> effient
You need to be able to detect if the record has changed since you read it. If
it has, then you can choose what to do - overwrite, cancel, or perhaps you
could program some kind of merge to offer a combination of the two changes (if
the users changed different columns).
One way to do this is to keep a complete copy of the record when read, then
inside the update transaction, re-read the record and compare all the data
with the original.
Provided you don't need the merge option, you can do it more easily with a
timestamp on each record, which is updated every time the record is written -
all you need to do then is to compare the timestamp.
You could update the timestamp automatically in a trigger if you like -
something like...
CREATE TRIGGER insert_notes_timeStamp AFTER INSERT ON notes
BEGIN
UPDATE notes SET timeStamp = strftime('%Y-%m-%d %H:%M:%f', 'now')
WHERE rowid = new.rowid;
END;
CREATE TRIGGER update_notes_timeStamp AFTER UPDATE ON notes
BEGIN
UPDATE notes SET timeStamp = strftime('%Y-%m-%d %H:%M:%f', 'now')
WHERE rowid = new.rowid;
END;
Notice I used strftime to create the timestamp - TIME('now') does not return
the fractional seconds, so you could conceivably get two updates with the same
timestamp.
--
Nikki Locke, Trumphurst Ltd. PC & Unix consultancy & programming
http://www.trumphurst.com/
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
