On Tue, Nov 24, 2009 at 10:25 AM, Pavel Ivanov <paiva...@gmail.com> wrote:
> > 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;
>
> I guess your trigger does something different from what you wanted to
> do: it changes value of v in the whole table when you change value of
> d in any row...
>
> > in 3.6.20, trigger does the right thing and updates the table. but the
> data
> > is not reflected in the table after the update statement is complete. I
> > didn't look at the vdbe.
>
> I wonder how did you see that? If data is not in the table after
> update statement then how did you see that trigger actually updates
> the table?
>
> found this out using 2 methods (1) by capturing what is being done in
trigger - inserted data in new table to capture the actions. (2) good old
printf() statements in the code.
Maybe your issue is somehow related to recursive call of the trigger
> which became possible recently? Anyway I think you better make your
> trigger AFTER UPDATE, not BEFORE UPDATE.
>
> a little background which I should have mentioned in the original post. in
the application, ONLY the column "d" is updated. so, this trigger is
specifically to update "v" whenever "d" is changed.
ah. typo in original trigger. included "NEW" before _id. removed that, like
so
update t1 SET v=OLD.v+1 WHERE _id=OLD._id AND NEW.d!= OLD.d;
now it should update only the rows I have just updated.
played with making it AFTER trigger - that worked!
so, thats a regression from 3.6.16, wouldn't you say?
here is the working AFTER update trigger
CREATE TRIGGER t1_trig AFTER UPDATE ON t1
BEGIN
update t1 SET v=OLD.v+1 WHERE _id=OLD._id AND NEW.d!= OLD.d;
END;
Pavel
>
> On Tue, Nov 24, 2009 at 1:15 PM, 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;
> >
> > insert into t1 values(1, 1,0);
> > update t1 set d= 2 where _id = 1; <-- expected "v" = 2
> > select * FROM t1; <-- shows "v" = 1
> >
> > this works fine in versions upto 3.6.16..
> >
> > in 3.6.20, trigger does the right thing and updates the table. but the
> data
> > is not reflected in the table after the update statement is complete. I
> > didn't look at the vdbe.
> > anyone seen this behavior?
> >
> > thanks
> > _______________________________________________
> > 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
