Re: [sqlite] Clarification about Triggers
Today I came at work with cleared mind (after a long time I would say!) and within minutes have found an easy and rather silly solution to my initial request. Here's the code: CREATE TABLE tmp_name( id integer primary key, name text); CREATE TABLE [temp_01] ( [id] INTEGER PRIMARY KEY AUTOINCREMENT, [name_id] INTEGER, [val] REAL(0, 2)); CREATE TABLE [temp_02] ( [id] INTEGER PRIMARY KEY AUTOINCREMENT, [name_id] INTEGER, [val] REAL(0, 2)); CREATE VIEW [MergeTwoTableValuesAsOne] AS select tmp_name.[name], temp_01.val as [First Value], temp_02.val as [Second Value], (temp_01.val + temp_02.val) as [Addition], (temp_01.val - temp_02.val) as [Subtraction], (temp_01.val * temp_02.val) as [Multiplication], (temp_01.val / temp_02.val) as [Division], (temp_01.val % temp_02.val) as [Remainder] from temp_01, temp_02, tmp_name where temp_01.id = temp_02.id and temp_01.[name_id] = temp_02.[name_id] and temp_01.name_id = tmp_name.id and temp_02.name_id = tmp_name.id; This is way much better than passing my data to a table. Like this, I generate my information on the fly and if I want them in a table I can create a new table and insert them in: create table as select * from MergeTwoTableValuesAsOne; Simple as that :D Just wanted to share it with you guys :) Waiting for your valuable replies. > >From: "Black, Michael (IS)" >To: General Discussion of SQLite Database >Sent: Tuesday, August 30, 2011 6:48 PM >Subject: Re: [sqlite] Clarification about Triggers > >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)" >To: stefanos sofroniou ; General Discussion of >SQLite Database >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 &
Re: [sqlite] Clarification about Triggers
Not accusing you of being pompous/patronizing at all...just having some fun with it... I prefer the Pluralis Majestatis interpretation myself:-) http://en.wikipedia.org/wiki/We Shall we agree? 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 8:57 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Clarification about Triggers On 08/31/2011 06:34 PM, Black, Michael (IS) wrote: > Doohyes "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. Fair enough. Sounded pompous. I say "we" because I only realized what was happening after trying to debug the script as if it were an SQLite bug in AFTER triggers. > 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. I think it's an SQL thing. BEFORE is the default. SQLite docs don't say that though. ___ 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
Re: [sqlite] Clarification about Triggers
On 08/31/2011 06:34 PM, Black, Michael (IS) wrote: Doohyes "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. Fair enough. Sounded pompous. I say "we" because I only realized what was happening after trying to debug the script as if it were an SQLite bug in AFTER triggers. 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. I think it's an SQL thing. BEFORE is the default. SQLite docs don't say that though. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clarification about Triggers
Doohyes "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
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
Re: [sqlite] Clarification about Triggers
old.rowid was an error of mine during typing; my apologies. Even though I have kind of managed to comprehend the logic behind of it, and made it partially work, I have decided to stick with a view which does what I want it to do. The only problem I have now is how can to update an individual value from a row of my choice. From: Igor Tandetnik To: sqlite-users@sqlite.org Sent: Tuesday, August 30, 2011 3:18 PM Subject: Re: [sqlite] Clarification about Triggers stefanos sofroniou wrote: > 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. With a trigger firing on which event? A value from which row in temp_01? Add to a value from which row of temp_02? Put the result into which row of total? You talk about these tables as if they were scalars. But they potentially contain many rows with many values. > 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; old.rowid is not defined in an "after insert" trigger. You are inserting a new row, there's no old row involved. -- 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
Re: [sqlite] Clarification about Triggers
stefanos sofroniou wrote: > 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. With a trigger firing on which event? A value from which row in temp_01? Add to a value from which row of temp_02? Put the result into which row of total? You talk about these tables as if they were scalars. But they potentially contain many rows with many values. > 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; old.rowid is not defined in an "after insert" trigger. You are inserting a new row, there's no old row involved. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clarification about Triggers
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)" To: stefanos sofroniou ; General Discussion of SQLite Database 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
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
