> 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. 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
