> yes it is risky, in general. but in this specific case, I expect no > problems. > just as a reference, this works just fine on mysql and oracle.. > and used to work quite well until sqlite 3.6.16.
I don't know anything about MySQL but Oracle has completely different concept of BEFORE UPDATE triggers, so you cannot compare it with SQLite in here. And I wouldn't look at this issue as on a regression but as on some undocumented feature that you relied upon and it was changed without a warning. I guess now SQLite works like this: 1) you issue update to change d; 2) SQLite finds row that you want to update; 3) remembers its old state and new state (including all fields that you didn't update); 4) executes your trigger; 5) writes into database all new values remembered on step 3. I wouldn't say these steps are wrong but they lead to behavior you caught up. Pavel On Tue, Nov 24, 2009 at 2:04 PM, Vasu Nori <vn...@google.com> wrote: > On Tue, Nov 24, 2009 at 10:26 AM, Igor Tandetnik <itandet...@mvps.org>wrote: > >> Vasu Nori <vn...@google.com> wrote: >> > wondering if this is a known issue in 3.6.20. >> > >> > create table t1(_id integer primary key, v integer, d integer); >> > CREATE TRIGGER t1_trig BEFORE UPDATE ON t1 >> > BEGIN >> > update t1 SET v=OLD.v+1 WHERE NEW._id=OLD._id AND NEW.d!= OLD.d; >> > END; >> >> Realize that your trigger updates all records in t1, not just the record >> where t1._id = OLD._id. You probably don't want that. >> >> not quite true though.. although I did make a mistake by including NEW > infront of "_id" in trigger. > >> > insert into t1 values(1, 1,0); >> > update t1 set d= 2 where _id = 1; <-- expected "v" = 2 >> > select * FROM t1; <-- shows "v" = 1 >> >> I wonder - does a similar AFTER UPDATE trigger work? It sounds risky to try >> and modify a table ahead of another update. Though on the face of it, I >> would expect your code to work as written. >> >> yes it is risky, in general. but in this specific case, I expect no > problems. > just as a reference, this works just fine on mysql and oracle.. > and used to work quite well until sqlite 3.6.16. > > thanks for your thoughts on this issue. > > >> Igor Tandetnik >> >> _______________________________________________ >> 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
