1: # 2009 December 29
2: #
3: # The author disclaims copyright to this source code. In place of
4: # a legal notice', here is a blessing:
5: #
6: # May you do good and not evil.
7: # May you find forgiveness for yourself and forgive others.
8: # May you share freely, never taking more than you give.
9: #
10: #***********************************************************************
11: #
12: # Verify that when columns named "rowid", "oid", and "_rowid_" appear
13: # in a table as ordinary columns (not as the INTEGER PRIMARY KEY) then
14: # the use of these columns in triggers will refer to the column and not
15: # to the actual ROWID. Ticket [34d2ae1c6d08b5271ba5e5592936d4a1d913ffe3]
16: #
17: # Also, verify that triggers created like this:
18: #
19: # CREATE TRIGGER attached.trig AFTER INSERT ON attached.tab ...
20: #
21: # can be reparsed as a main database. Ticket [d6ddba6706353915ceedc56b4e3]
22: #
23:
24: set testdir [file dirname $argv0]
25: source $testdir/tester.tcl
26: ifcapable {!trigger} {
27: finish_test
28: return
29: }
30:
31: # Triggers on tables where the table has ordinary columns named
32: # rowid, oid, and _rowid_.
33: #
34: do_test triggerD-1.1 {
35: db eval {
36: CREATE TABLE t1(rowid, oid, _rowid_, x);
37: CREATE TABLE log(a,b,c,d,e);
38: CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
39: INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
40: END;
41: CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
42: INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x);
43: END;
44: CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
45: INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x);
46: INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x);
47: END;
48: CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
49: INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x);
50: INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x);
51: END;
52: CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN
53: INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x);
54: END;
55: CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN
56: INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x);
57: END;
58: }
59: } {}
60: do_test triggerD-1.2 {
61: db eval {
62: INSERT INTO t1 VALUES(100,200,300,400);
63: SELECT * FROM log
64: }
65: } {r1 100 200 300 400 r2 100 200 300 400}
66: do_test triggerD-1.3 {
67: db eval {
68: DELETE FROM log;
69: UPDATE t1 SET rowid=rowid+1;
70: SELECT * FROM log
71: }
72: } {r3.old 100 200 300 400 r3.new 101 200 300 400 r4.old 100 200 300 400 r4.new 101 200 300 400}
73: do_test triggerD-1.4 {
74: db eval {
75: DELETE FROM log;
76: DELETE FROM t1;
77: SELECT * FROM log
78: }
79: } {r5 101 200 300 400 r6 101 200 300 400}
80:
81: # Triggers on tables where the table does not have ordinary columns named
82: # rowid, oid, and _rowid_.
83: #
84: do_test triggerD-2.1 {
85: db eval {
86: DROP TABLE t1;
87: CREATE TABLE t1(w,x,y,z);
88: CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
89: INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
90: END;
91: CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
92: INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x);
93: END;
94: CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
95: INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x);
96: INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x);
97: END;
98: CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
99: INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x);
100: INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x);
101: END;
102: CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN
103: INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x);
104: END;
105: CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN
106: INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x);
107: END;
108: }
109: } {}
110: do_test triggerD-2.2 {
111: db eval {
112: DELETE FROM log;
113: INSERT INTO t1 VALUES(100,200,300,400);
114: SELECT * FROM log;
115: }
116: } {r1 -1 -1 -1 200 r2 1 1 1 200}
117: do_test triggerD-2.3 {
118: db eval {
119: DELETE FROM log;
120: UPDATE t1 SET x=x+1;
121: SELECT * FROM log
122: }
123: } {r3.old 1 1 1 200 r3.new 1 1 1 201 r4.old 1 1 1 200 r4.new 1 1 1 201}
124: do_test triggerD-2.4 {
125: db eval {
126: DELETE FROM log;
127: DELETE FROM t1;
128: SELECT * FROM log
129: }
130: } {r5 1 1 1 201 r6 1 1 1 201}
131:
132:
133: ###########################################################################
134: #
135: # Ticket [985771e1161200ae5eac3162686ea6711c035d08]:
136: #
137: # When both a main database table and a TEMP table have the same name,
138: # and a main database trigge is created on the main table, the trigger
139: # is incorrectly bound to the TEMP table. For example:
140: #
141: # CREATE TABLE t1(x);
142: # CREATE TEMP TABLE t1(x);
143: # CREATE TABLE t2(z);
144: # CREATE TRIGGER main.r1 AFTER INSERT ON t1 BEGIN
145: # INSERT INTO t2 VALUES(10000 + new.x);
146: # END;
147: # INSERT INTO main.t1 VALUES(3);
148: # INSERT INTO temp.t1 VALUES(4);
149: # SELECT * FROM t2;
150: #
151: # The r1 trigger fires when the value 4 is inserted into the temp.t1
152: # table, rather than when value 3 is inserted into main.t1.
153: #
154: do_test triggerD-3.1 {
155: db eval {
156: CREATE TABLE t300(x);
157: CREATE TEMP TABLE t300(x);
158: CREATE TABLE t301(y);
159: CREATE TRIGGER main.r300 AFTER INSERT ON t300 BEGIN
160: INSERT INTO t301 VALUES(10000 + new.x);
161: END;
162: INSERT INTO main.t300 VALUES(3);
163: INSERT INTO temp.t300 VALUES(4);
164: SELECT * FROM t301;
165: }
166: } {10003}
167: do_test triggerD-3.2 {
168: db eval {
169: DELETE FROM t301;
170: CREATE TRIGGER temp.r301 AFTER INSERT ON t300 BEGIN
171: INSERT INTO t301 VALUES(20000 + new.x);
172: END;
173: INSERT INTO main.t300 VALUES(3);
174: INSERT INTO temp.t300 VALUES(4);
175: SELECT * FROM t301;
176: }
177: } {10003 20004}
178:
179:
180: #############################################################################
181: #
182: # Ticket [d6ddba6706353915ceedc56b4e3e72ecb4d77ba4]
183: #
184: # The following syntax really should not be allowed:
185: #
186: # CREATE TRIGGER xyz.trig BEFORE UPDATE ON xyz.tab BEGIN ...
187: #
188: # But a long-standing bug does allow it. And the "xyz.tab" slips into
189: # the sqlite_master table. We cannot fix the bug simply by disallowing
190: # "xyz.tab" since that could break legacy applications. We have to
191: # fix the system so that the "xyz." on "xyz.tab" is ignored.
192: # Verify that this is the case.
193: #
194: do_test triggerD-4.1 {
195: db close
196: forcedelete test.db test2.db
197: sqlite3 db test.db
198: db eval {
199: CREATE TABLE t1(x);
200: ATTACH 'test2.db' AS db2;
201: CREATE TABLE db2.t2(y);
202: CREATE TABLE db2.log(z);
203: CREATE TRIGGER db2.trig AFTER INSERT ON db2.t2 BEGIN
204: INSERT INTO log(z) VALUES(new.y);
205: END;
206: INSERT INTO t2 VALUES(123);
207: SELECT * FROM log;
208: }
209: } {123}
210: do_test triggerD-4.2 {
211: sqlite3 db2 test2.db
212: db2 eval {
213: INSERT INTO t2 VALUES(234);
214: SELECT * FROM log;
215: }
216: } {123 234}
217: db2 close
218:
219: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to triggerD.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test