UPSERT (really means update the record if it exists otherwise insert the record)
AFTER INSERT (I meant a trigger after an insert. In this case I have access to the new value but not the "old" values in the record.) If UPDATE would insert the record if it didn't exist then I could use the trigger you have suggested. ________________________________ From: Simon Slavin <slav...@bigfraud.org> To: Tony Caras <tca...@yahoo.com>; General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Thursday, January 12, 2012 4:40 PM Subject: Re: [sqlite] UPSERT again On 12 Jan 2012, at 11:20pm, Tony Caras wrote: > The problem is, that if I create AFTER INSERT then I don't know what the > original state of the blocked field was so I don't whether to increment or > decrement or leave the counter alone. I don't understand what you mean by 'UPSERT' or 'create AFTER INSERT'. If you're doing an INSERT then the field doesn't have an original state: it's a new row. If you're doing an UPDATE a TRIGGER does indeed have access to the old value of a field changed by UPDATE: http://www.sqlite.org/lang_createtrigger.html So you can do CREATE TRIGGER ... AFTER UPDATE ... BEGIN UPDATE ... SET totalBlocked = totalBlocked - 1 WHERE old.blocked; UPDATE ... SET totalBlocked = totalBlocked + 1 WHERE new.blocked; END; Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
