Dooh....yes "we" missed that. But shouldn't new.rowid be undefined then rather than return -1? Much like old.rowid is undefined? That might have helped "us" in recognizing "our" mistake.
The docs say The value of NEW.rowid is undefined in a BEFORE INSERT trigger in which the rowid is not explicitly set to an integer. http://www.sqlite.org/lang_createtrigger.html And...shouldn't "after" or "before" or "instead" be mandatory? The docs don't declare a default condition either. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Dan Kennedy [danielk1...@gmail.com] Sent: Wednesday, August 31, 2011 1:19 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Clarification about Triggers On 08/30/2011 10:48 PM, Black, Michael (IS) wrote: > I found that if you used the default rowid it always gave -1 for the value. > That's why I put in it's own key. > > I don't understand why this doesn't work...perhaps somebody can point out the > error here...new.rowid contains -1. I would think that rowid ought to be > available after the insert. > This is using 3.7.4 > > create table temp_01(val float); > create table temp_02(val float); > create table total(val float); > create table row(row2 integer); > create trigger after insert on temp_01 > begin > insert into total values((select new.val+temp_02.val from temp_02 where > temp_02.rowid=new.rowid)); > insert into row values(new.rowid); > end; The error is that we are mistaking the above for an AFTER trigger. It is not. It is a BEFORE trigger named "after". And the value of new.rowid is not defined in a BEFORE trigger. Rewrite as: CREATE TRIGGER my_new_trigger AFTER INSERT ... and it should work. Dan. _______________________________________________ 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
