Igor Tandetnik wrote:
nbiggs wrote:
Can an insert trigger cause an update trigger to be fired also? My
triggers are listed below. I want tgr_on_insert to fire
tgr_on_update.
SQLite does not support cascading triggers. Operations performed by a
trigger never cause other triggers to run.
Igor,
This is not true. SQLite supports multiple triggers on the same event
(insert, update, or delete) on a single table and it fires all triggers
associated with any inserts, updates, or deletes that are done by a trigger.
The following sample demonstrates an insert trigger firing an update
trigger on the same table. The ti trigger fires on insert and updates
the f1 field. The second trigger, tu, fires on update and sets the f2
field. The explain output shows that both triggers will fire on an
insert (i.e. they are cascaded), and the select demonstrates that the
date function in the update trigger is executed as expected.
SQLite version 3.3.2
Enter ".help" for instructions
sqlite> create table t(a, f1, f2);
sqlite> create trigger ti after insert on t begin
...> update t set f1 = 1 where a = new.a;
...> end;
sqlite> create trigger tu after update on t begin
...> update t set f2 = date('now') where a = new.a;
...> end;
sqlite> .explain on
sqlite> explain insert into t values(1, NULL, NULL);
addr opcode p1 p2 p3
---- -------------- ---------- ----------
---------------------------------
0 Goto 0 98
1 Noop 0 0
2 OpenPseudo 0 0
3 SetNumColumns 0 3
4 Integer 0 0
5 OpenWrite 1 2
6 SetNumColumns 1 3
7 NewRowid 1 0
8 Integer 1 0
9 Null 0 0
10 Null 0 0
11 MakeRecord 3 0 bbb
12 Dup 1 0
13 Dup 1 0
14 Insert 0 0
15 Insert 1 3 t
16 Close 1 0
17 ContextPush 0 0
18 ResetCount 0 0
19 Noop 0 0
20 Integer 0 0
21 OpenRead 4 2
22 SetNumColumns 4 1
23 Rewind 4 30
24 Column 4 0
25 Column 0 0
26 Ne 354 29 collseq(BINARY)
27 Rowid 4 0
28 FifoWrite 0 0
29 Next 4 24
30 Close 4 0
31 OpenPseudo 3 0
32 SetNumColumns 3 3
33 OpenPseudo 2 0
34 SetNumColumns 2 3
35 FifoRead 0 93
36 Dup 0 0
37 Dup 0 0
38 Integer 0 0
39 OpenRead 4 2
40 SetNumColumns 4 3
41 MoveGe 4 0
42 Rowid 4 0
43 RowData 4 0
44 Insert 3 0
45 Rowid 4 0
46 Column 4 0
47 Integer 1 0
48 Column 4 2
49 MakeRecord 3 0 bbb
50 Insert 2 0
51 Close 4 0
52 Integer 0 0
53 OpenWrite 4 2
54 SetNumColumns 4 3
55 NotExists 4 35
56 Column 4 0
57 Integer 1 0
58 Column 4 2
59 MakeRecord 3 0 bbb
60 Insert 4 5 t
61 Close 4 0
62 ContextPush 0 0
63 ResetCount 0 0
64 Noop 0 0
65 Integer 0 0
66 OpenRead 5 2
67 SetNumColumns 5 1
68 Rewind 5 75
69 Column 5 0
70 Column 2 0
71 Ne 354 74 collseq(BINARY)
72 Rowid 5 0
73 FifoWrite 0 0
74 Next 5 69
75 Close 5 0
76 Integer 0 0
77 OpenWrite 5 2
78 SetNumColumns 5 3
79 FifoRead 0 89
80 Dup 0 0
81 NotExists 5 79
82 Column 5 0
83 Column 5 1
84 String8 0 0 now
85 Function 1 1 date(-1)
86 MakeRecord 3 0 bbb
87 Insert 5 5 t
88 Goto 0 79
89 Close 5 0
90 ResetCount 1 0
91 ContextPop 0 0
92 Goto 0 35
93 Close 2 0
94 Close 3 0
95 ResetCount 1 0
96 ContextPop 0 0
97 Halt 0 0
98 Transaction 0 1
99 VerifyCookie 0 3
100 Goto 0 1
101 Noop 0 0
sqlite> insert into t values(1, NULL, NULL);
sqlite> select * from t;
a f1 f2
---- -------------- ----------
1 1 2006-01-24
sqlite>
HTH
Dennis Cote
