IMHO your Statement should read: UPDATE MYVIEW SET ... WHERE primary = <old value>;
Not specifying a WHERE clause will affect all rows (or none or just one, depending on UNIQUE index restraints and the ON CONFLICT action). Judging from my experience with virtual tables, SQLite implements the UPDATE statement via a SELECT to find all records to be affected, replacing the SET fields on each record, and inserting the resulting fields per rowid. The trigger is executed once per row, and SQLite knows the old values from executing the SELECT. Gunter -----Ursprüngliche Nachricht----- Von: Alem Biscan [mailto:biscana...@gmail.com] Gesendet: Donnerstag, 27. Dezember 2012 13:50 An: General Discussion of SQLite Database Betreff: [sqlite] Instead of triggers Hello community, I am a little bit confused by instead of update trigger logic. Lets say there is a trigger MYTRIGGER which is an INSTEAD OF UPDATE trigger that belongs to view named MYVIEW. MYTRIGGER should update table MYTABLE with values sent from an application. UPDATE MYTABLE SET *primary = new . primary; * * field2 = new . field2*, *field3 = new . field3* WHERE *primary = old.primary;* So user decides to modify a record. He changes *primary, field2, field3*values trough textboxes, and the app sends parametarised update query to sqlite engine. UPDATE MYVIEW SET *primary = @a*, *field2 = @b*. *field3 = @c*; Since *primary* is not a surrogat key, and the user has changed it, there is no way to and including it in a where clause. My question is: does sqlite engine know the *old value of primary field*? If yes, HOW? Is trigger executing once or for each row? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
