I know there have been discussions in the past about workarounds for UPSERT but I recently encountered another one and I haven't figure out a good workaround. Any assistance would be greatly appreciated.
I have a situation where I need to increment a counter in another table every time a record is added or specific field is modified to a certain value. For example same my record is message and it has a field called blocked. The record can be inserted with blocked field set to true or false. The fields in the record may get changed/updated many times but if the blocked field gets changed to true I need to increment a counter (conversely if it goes from true to false i need to decrement the counter). I can create a trigger that will increment and decrement the counter but the record changes are currently done doing INSERT OR REPLACE so record will get added if it doesn't exist. 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. If I just increment when blocked=true then multiple inserts with the same value will cause my counter to increment multiple times for the same record. This isn't what I need. Does anyone have any suggestions? I tried some of the UPSERT type of workarounds but so far they haven't worked for me. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
