Alem Biscan <biscana...@gmail.com> wrote: > 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.
I don't understand this sentence. Your UPDATE MYVIEW statement changes every single row in the view. The trigger will fire for each such row, so you will end up executing as many UPDATE MYTABLE rows (complete with the WHERE clause) as there were rows in the view. If that's not what you want, then your UPDATE MYVIEW statement is wrong - you probably meant to specify a WHERE clause there, too. INSTEAD OF UPDATE trigger is not much different from a regular ON UPDATE trigger which modifies table B in response to updates on table A. Imagine that the contents of the view are copied into a temporary table, regular ON triggers are created on that temp table for each INSTEAD OF trigger on the view, the UPDATE statement is executed against that temp table, and then the temp table is dropped. In particular, when you write new.X and old.X, X refers to a column of the view (which doesn't have to be directly related to any column of any underlying table). You seem to be confused by the fact that in your particular case, the view just happens to have columns named the same as those of the only underlying table. > My question is: does sqlite engine know the *old value of primary field*? Yes of course - one for each row of the view being updated. > If yes, HOW? Is trigger executing once or for each row? Yes it is. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
