This is a question about triggers in version 3.5.9...if anyone remembers it.
The reason I am asking is that I want to support
Android 2.1, which has 3.5.9 installed by default.
I have a set of triggers on a table:
tg1 before insert on sometable when new.f1 = 3
insert into sometable(f1, ...) value (2, ...);
tg2 before insert on sometable when new.f1 = 2
insert into sometable(f1, ...) value (1, ...);
basically, it is a cheesy way of building summary rows in a table before a row
is inserted.
The end result of inserting, for eaxmple, (3, a, b, c, d) would be:
(1,a, null...)
(2, a, b, null,...)
(3, a, b, c, d);
I also build this table using an 'insert...select' statement. So one statement
is executed.
What I am seeing in 3.5.9 on Android is that the triggers are executed
precisely once each, rather than once for each row.
I have tested this using a 3.5.8 exe under windows and got a different result;
only *some* of the trigger rows are created and they
are created after the statement has run, despite being *before* triggers.
I have appended some text code at the end.
But my main question is:
*Is there a work-around under 3.5.8/9 to fix this problem? Was it a known
problem?*
Comments from people with long memories would be appreciated!
----------------------- Sample Code ----------------------
|drop table t1;||
||create table t1 (f1, f2, f3, f4);||
||
||drop trigger t1_l3;||
||
||create trigger t1_l3 before insert on t1 for each row when new.f1 = 3||
|| begin||
|| insert into t1 values(2, new.f2, new.f3, '-');||
|| end;||
||
||drop trigger t1_l2;||
||create trigger t1_l2 before insert on t1 for each row when new.f1 = 2||
|| begin||
|| insert into t1 values(1, new.f2, '-', '-');||
|| end;||
||
||
||drop table t2;||
||create table t2(f1, f2, f3, f4);||
||insert into t2 values(3, 'a', 'b', 'c');||
||insert into t2 values(3, 'aa', 'ba', 'ca');||
||insert into t2 values(3, 'ab', 'bb', 'cb');||
||insert into t2 values(3, 'ac', 'bc', 'cc');||
||insert into t2 values(3, 'ad', 'bd', 'cd');||
||insert into t2 values(3, 'ae', 'be', 'ce');||
||
||
||insert into t1 select * from t2;||
||
||select rowid,* from t1;||
||
<-- For me, in 3.5.8, this produces :
1|1|a|-|-
2|2|a|b|-
3|3|a|b|c
4|3|aa|ba|ca
5|3|ab|bb|cb
6|3|ac|bc|cc
7|3|ad|bd|cd
8|3|ae|be|ce
9|2|ac|bc|-
10|1|ad|-|-
11|2|ad|bd|-
12|1|ae|-|-
13|2|ae|be|-
Whereas in 3.7.14 it produces the expected results:
1|1|a|-|-
2|2|a|b|-
3|3|a|b|c
4|1|aa|-|-
5|2|aa|ba|-
6|3|aa|ba|ca
7|1|ab|-|-
8|2|ab|bb|-
9|3|ab|bb|cb
10|1|ac|-|-
11|2|ac|bc|-
12|3|ac|bc|cc
13|1|ad|-|-
14|2|ad|bd|-
15|3|ad|bd|cd
16|1|ae|-|-
17|2|ae|be|-
18|3|ae|be|ce
||
||
|| ||
||
||||
|
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
