No, the insert I referred to actually is an INSERT OR REPLACE, but it doesn't work because a REPLACE really is a DELETE the original and INSERT the new. So in the example if the blocked field was true in the original record and true in the replacement record, then the counter will get incremented twice (once for the original insert and again for the insert associated with the replace).
________________________________ From: "Black, Michael (IS)" <michael.bla...@ngc.com> To: Tony Caras <tca...@yahoo.com>; General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Thursday, January 12, 2012 5:37 PM Subject: Re: [sqlite] UPSERT again Does REPLACE do what you want? http://www.sqlite.org/lang_replace.html Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Tony Caras [tca...@yahoo.com] Sent: Thursday, January 12, 2012 6:17 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] UPSERT again 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
