On Tue, Nov 24, 2009 at 12:28 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
> > I am not what you mean by Oracle's Before triggers have different > concept? > > care to explain? > > Sure. When Oracle calls your before update trigger it provides you old > values of the row and storage for new values of the row. You can > change whatever you like in this storage and be sure that it will make > its way into the table. You don't change these values with update > statement, you use simple assignment as to some pl/sql variable, don't > you? And in this way you can even change values that were provided in > update statement which could lead to somewhat "strange" behavior from > the point of view of the user not knowing about trigger existence. > Also in Oracle you cannot change other rows in the same table trigger > was called on at all. > In SQLite on the other hand you can change whatever table and whatever > rows you like (except the same rows before update trigger was called > on) but you have to issue update statement for that which will work > independently from the statement caused trigger to work in the first > place. Because of this very concept limitation you've got undefined > behavior if you change the same row... > > > This specific trigger and the conditions under which it operates do not > > cause any such problems. > > > > but it is not... > > You're wrong. Just look closer on the statement drh pointed to: your > BEFORE UPDATE trigger modifies a row that should have been updated by > UPDATE statement which called the trigger (you're updating the row > with the same _id). So you're hitting exactly this explained undefined > behavior. > > thanks for the email. you are right. it is the same row. but my point is a bit different. let me outline 2 different approaches to this. 1. backward compatibility. It worked before upto 3.6.16. so, probably it should work the same now. 2. maybe an error should be thrown instead of silently failing (like oracle does, as you mentioned..). this will alert the apps using the triggers incorrectly but have been working with versions <= 3.6.16. in both before and after triggers, don't allow modifications to the origin tables. (origin table = the table this trigger is on) throw error at execution time of the trigger, if this condition occurs. If there is enough encouragement on one or the other, I could do a patch.. and thanks once again for your responses. sqlite is great and I would like to help make it better by bringing issues to the forum.. > > Pavel > > On Tue, Nov 24, 2009 at 3:06 PM, Vasu Nori <vn...@google.com> wrote: > > 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 > > > _______________________________________________ > 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
