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;
insert into temp_02 values(2.0);
insert into temp_01 values(1.0);
select * from total;
select * from row;
-1
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: stefanos sofroniou [stefanossofroniou...@yahoo.com]
Sent: Tuesday, August 30, 2011 9:42 AM
To: Black, Michael (IS)
Subject: EXT :Re: [sqlite] Clarification about Triggers
I did exactly what you have suggested but it does not work properly with my
software.
It increments my id (i replaced rownum with id, the function still is the
same), but the value is null for some reason.
From: "Black, Michael (IS)" <michael.bla...@ngc.com>
To: stefanos sofroniou <stefanossofroniou...@yahoo.com>; General Discussion of
SQLite Database <sqlite-users@sqlite.org>
Sent: Tuesday, August 30, 2011 3:19 PM
Subject: RE:[sqlite] Clarification about Triggers
There may be a more elegant way but this works:
create table temp_01(rownum integer primary key,val float);
create table temp_02(rownum integer primary key,val float);
create table total(rownum integer primary key,val float);
create trigger after insert on temp_01
begin
insert into total values(new.rownum,(select new.val+temp_02.val from temp_02
where temp_02.rownum=new.rownum));
end;
insert into temp_02 values(1,2.0);
insert into temp_01 values(1,1.0);
select * from total;
1|3.0
insert into temp_02 values(2,20.0);
insert into temp_01 values(2,30.0);
select * from total;
1|3.0
2|50.0
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 stefanos sofroniou [stefanossofroniou...@yahoo.com]
Sent: Tuesday, August 30, 2011 2:32 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Clarification about Triggers
Hello everyone.
I have successfully created a trigger for experimentation and I would like your
help for implementing the concept behind of it.
My table pollaplasiasmos (which stands for multiplication in Greek, even though
I used English characters to write it [Gree-glish]), takes 3 values:
* Quantity [int]
* amount [real(0,2)]
* VAT [real(0,2)]I have created the triggers to make the necessary
calculations for vat_value (per item), vat_included (item price), and
total_price (which includes vat).
Now what I want to do is to take two tables, (let's call them temp_01 and
temp_02) that would both have columns id and val, and I want with a trigger to
add temp_01.val with temp_02.val and put their result in a new table named
total.
I have tried something like this:
create trigger [after_insert_temp_01]
after insert on temp_01
begin
update total
set val = temp_01.val + temp_02.val
where rowid = old.rowid;
end;
I know that this is wrong of what I am doing, because there is no way to call
the temp_02 table using this method; this is not an inner join where I can
compare IDs and make the necessary adjustments on my code.
I am really out of ideas and if there's a link with more information that I
should know already about it, by all means let me know because I need to learn
these things.
Regards,
Stefanos
_______________________________________________
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
