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
