On Mon, 2009-08-24 at 13:27 -0400, Timo Sirainen wrote: > > CREATE TRIGGER mergeexpire BEFORE INSERT ON expires FOR EACH ROW > > BEGIN > > UPDATE expires SET expire_stamp=NEW.expire_stamp > > WHERE username = NEW.username AND mailbox = NEW.mailbox; > > SELECT raise(ignore) > > WHERE (SELECT 1 FROM expires WHERE username = NEW.username AND > > mailbox = NEW.mailbox) IS NOT NULL; > > END; > > > > This seem some kind crudely to me, but it's working. > > That looks correct to me.
Oh, except there's a small race condition if the first row is being added at the same time by two processes. That's why the PostgreSQL trigger is so much more complex. But it should be somewhat rare I guess.. Unless SQLite has some locks that prevent that? Anyway I was thinking that two processes run the UPDATE part of the trigger and then both try to INSERT. One of them succeeds and the other one fails. But the row gets added anyway and the timestamp is the same anyway, so it probably doesn't matter all that much, just logs an error.
signature.asc
Description: This is a digitally signed message part
