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

Reply via email to