On Tue, Nov 24, 2009 at 11:25 AM, Pavel Ivanov <paiva...@gmail.com> wrote:
> > 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. > I am not what you mean by Oracle's Before triggers have different concept? care to explain? > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users