1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
|
# 2009 December 29
#
# The author disclaims copyright to this source code. In place of
# a legal notice', here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Verify that when columns named "rowid", "oid", and "_rowid_" appear
# in a table as ordinary columns (not as the INTEGER PRIMARY KEY) then
# the use of these columns in triggers will refer to the column and not
# to the actual ROWID. Ticket [34d2ae1c6d08b5271ba5e5592936d4a1d913ffe3]
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable {!trigger} {
finish_test
return
}
# Triggers on tables where the table has ordinary columns named
# rowid, oid, and _rowid_.
#
do_test triggerD-1.1 {
db eval {
CREATE TABLE t1(rowid, oid, _rowid_, x);
CREATE TABLE log(a,b,c,d,e);
CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
END;
CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x);
END;
CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x);
INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x);
END;
CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x);
INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x);
END;
CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN
INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x);
END;
CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN
INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x);
END;
}
} {}
do_test triggerD-1.2 {
db eval {
INSERT INTO t1 VALUES(100,200,300,400);
SELECT * FROM log
}
} {r1 100 200 300 400 r2 100 200 300 400}
do_test triggerD-1.3 {
db eval {
DELETE FROM log;
UPDATE t1 SET rowid=rowid+1;
SELECT * FROM log
}
} {r3.old 100 200 300 400 r3.new 101 200 300 400 r4.old 100 200 300 400 r4.new 101 200 300 400}
do_test triggerD-1.4 {
db eval {
DELETE FROM log;
DELETE FROM t1;
SELECT * FROM log
}
} {r5 101 200 300 400 r6 101 200 300 400}
# Triggers on tables where the table does not have ordinary columns named
# rowid, oid, and _rowid_.
#
do_test triggerD-2.1 {
db eval {
DROP TABLE t1;
CREATE TABLE t1(w,x,y,z);
CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
END;
CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x);
END;
CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x);
INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x);
END;
CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x);
INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x);
END;
CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN
INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x);
END;
CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN
INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x);
END;
}
} {}
do_test triggerD-2.2 {
db eval {
DELETE FROM log;
INSERT INTO t1 VALUES(100,200,300,400);
SELECT * FROM log;
}
} {r1 -1 -1 -1 200 r2 1 1 1 200}
do_test triggerD-2.3 {
db eval {
DELETE FROM log;
UPDATE t1 SET x=x+1;
SELECT * FROM log
}
} {r3.old 1 1 1 200 r3.new 1 1 1 201 r4.old 1 1 1 200 r4.new 1 1 1 201}
do_test triggerD-2.4 {
db eval {
DELETE FROM log;
DELETE FROM t1;
SELECT * FROM log
}
} {r5 1 1 1 201 r6 1 1 1 201}
###########################################################################
#
# Ticket [985771e1161200ae5eac3162686ea6711c035d08]:
#
# When both a main database table and a TEMP table have the same name,
# and a main database trigge is created on the main table, the trigger
# is incorrectly bound to the TEMP table. For example:
#
# CREATE TABLE t1(x);
# CREATE TEMP TABLE t1(x);
# CREATE TABLE t2(z);
# CREATE TRIGGER main.r1 AFTER INSERT ON t1 BEGIN
# INSERT INTO t2 VALUES(10000 + new.x);
# END;
# INSERT INTO main.t1 VALUES(3);
# INSERT INTO temp.t1 VALUES(4);
# SELECT * FROM t2;
#
# The r1 trigger fires when the value 4 is inserted into the temp.t1
# table, rather than when value 3 is inserted into main.t1.
#
do_test triggerD-3.1 {
db eval {
CREATE TABLE t300(x);
CREATE TEMP TABLE t300(x);
CREATE TABLE t301(y);
CREATE TRIGGER main.r300 AFTER INSERT ON t300 BEGIN
INSERT INTO t301 VALUES(10000 + new.x);
END;
INSERT INTO main.t300 VALUES(3);
INSERT INTO temp.t300 VALUES(4);
SELECT * FROM t301;
}
} {10003}
do_test triggerD-3.2 {
db eval {
DELETE FROM t301;
CREATE TRIGGER temp.r301 AFTER INSERT ON t300 BEGIN
INSERT INTO t301 VALUES(20000 + new.x);
END;
INSERT INTO main.t300 VALUES(3);
INSERT INTO temp.t300 VALUES(4);
SELECT * FROM t301;
}
} {10003 20004}
finish_test
|
