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
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
|
#################################################################
# This file inclde tests that address the foreign key cases of
# the following requirements since they are specific to innodb.
# Other test cases for these requirements are included in the
# triggers_master.test file.
#################################################################
--disable_abort_on_error
# OBN - The following tests are disabled until triggers are supported with forign
# keys in innodb (foreign keys tests dispabled - bug 11472)
#################################################################################
#Section x.x.x.3
# Test case: Similar to 3.5.10.5 but with ten tables to see if multiple triggers
# can be executed at once
let $message= Testcase x.x.x.3:;
--source include/show_msg.inc
--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings
eval CREATE TABLE t0 (col1 char(50)) ENGINE=$engine_type;
eval CREATE TABLE t1 (id INT NOT NULL, col1 char(50),
PRIMARY KEY (id)) ENGINE=$engine_type;
eval CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
(f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
ON DELETE SET NULL) ENGINE=$engine_type;
eval CREATE TABLE t3 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
(f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
eval CREATE TABLE t4 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
(f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
eval CREATE TABLE t5 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
(f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
eval CREATE TABLE t6 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
(f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
eval CREATE TABLE t7 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
(f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
eval CREATE TABLE t8 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
(f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
eval CREATE TABLE t9 (id INT PRIMARY KEY, f_id INT, INDEX par_ind
(f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
eval CREATE TABLE t10(id INT PRIMARY KEY, f_id INT, INDEX par_ind
(f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
eval CREATE TABLE t11(id INT PRIMARY KEY, f_id INT, INDEX par_ind
(f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id)
ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
create trigger tr1 after update on t2 for each row
insert into t0 values ('tr_t2');
create trigger tr2 after update on t3 for each row
insert into t0 values ('tr_t3');
create trigger tr3 after update on t4 for each row
insert into t0 values ('tr_t4');
create trigger tr3 after update on t5 for each row
insert into t0 values ('tr_t5');
create trigger tr4 after update on t6 for each row
insert into t0 values ('tr_t6');
create trigger tr5 after update on t7 for each row
insert into t0 values ('tr_t7');
create trigger tr5 after update on t8 for each row
insert into t0 values ('tr_t8');
create trigger tr6 after update on t9 for each row
insert into t0 values ('tr_t9');
create trigger tr7 after update on t10 for each row
insert into t0 values ('tr_t10');
create trigger tr8 after update on t11 for each row
insert into t0 values ('tr_t11');
insert into t1 values (1,'Department A');
insert into t1 values (2,'Department B');
insert into t1 values (3,'Department C');
insert into t2 values (1,2,'Employee');
insert into t3 values (1,2,'Employee');
insert into t4 values (1,2,'Employee');
insert into t5 values (1,2,'Employee');
insert into t6 values (1,2,'Employee');
insert into t7 values (1,2,'Employee');
insert into t8 values (1,2,'Employee');
insert into t9 values (1,2,'Employee');
insert into t10 values (1,2,'Employee');
insert into t11 values (1,2,'Employee');
select * from t1;
select * from t2;
select * from t3;
select * from t4;
select * from t5;
select * from t6;
select * from t7;
select * from t8;
select * from t9;
select * from t10;
select * from t11;
delete from t1 where id=2;
select * from t1;
select * from t2;
select * from t3;
select * from t4;
select * from t5;
select * from t6;
select * from t7;
select * from t8;
select * from t9;
select * from t10;
select * from t11;
select * from t0;
# Cleanup
drop trigger tr1;
drop trigger tr2;
drop trigger tr3;
drop trigger tr4;
drop trigger tr5;
drop trigger tr6;
drop trigger tr7;
drop trigger tr8;
drop trigger tr9;
drop trigger tr10;
drop table t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t1,t0;
#Section 3.5.10.5
# Test case: Ensure that every trigger that should be activated by every possible
# type of implicit update of its subject table (e.g. a FOREIGN KEY SET
# DEFAULT action or an UPDATE of a view based on the subject table)
# is indeed activated correctly.
let $message= Testcase 3.5.10.5 (foreign keys):;
--source include/show_msg.inc
--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings
eval CREATE TABLE t1 (id INT NOT NULL, col1 char(50),
PRIMARY KEY (id)) ENGINE=$engine_type;
eval CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT,
INDEX par_ind (f_id), col1 char(50),
FOREIGN KEY (f_id) REFERENCES t1(id)
ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type;
create trigger tr_t2 after update on t2
for each row set @counter=@counter+1;
insert into t1 values (1,'Department A');
insert into t1 values (2,'Department B');
insert into t1 values (3,'Department C');
insert into t2 values (1,2,'Emp 1');
insert into t2 values (2,2,'Emp 2');
insert into t2 values (3,2,'Emp 3');
insert into t2 values (4,2,'Emp 4');
insert into t2 values (5,2,'Emp 5');
insert into t2 values (6,3,'Emp 6');
set @counter=0;
select * from t1;
select * from t2;
select @counter;
update t1 set id=4 where id=3;
select * from t1;
select * from t2;
select @counter;
delete from t1 where id=2;
select * from t1;
select * from t2;
select @counter;
# This is to verify that the trigger works when updated directly
update t2 set col1='Emp 5a' where id=5;
select * from t2;
select @counter;
# Cleanup
drop trigger tr_t2;
drop table t2, t1;
#Section 3.5.10.6
# Test case: Ensure that every trigger that should be activated by every possible
# type of implicit deletion from its subject table (e.g. a FOREIGN KEY
# CASCADE action or a DELETE from a view based on the subject table)
# is indeed activated correctly.
let $message= Testcase 3.5.10.6 (foreign keys):;
--source include/show_msg.inc
--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings
eval CREATE TABLE t1 (id INT NOT NULL, col1 char(50),
PRIMARY KEY (id)) ENGINE=$engine_type;
eval CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT,
INDEX par_ind (f_id), col1 char(50),
FOREIGN KEY (f_id) REFERENCES t1(id)
ON DELETE CASCADE) ENGINE=$engine_type;
create trigger tr_t2 before delete on t2
for each row set @counter=@counter+1;
insert into t1 values (1,'Department A');
insert into t1 values (2,'Department B');
insert into t1 values (3,'Department C');
insert into t2 values (1,2,'Emp 1');
insert into t2 values (2,2,'Emp 2');
insert into t2 values (3,2,'Emp 3');
insert into t2 values (4,2,'Emp 4');
insert into t2 values (5,2,'Emp 5');
insert into t2 values (6,3,'Emp 6');
set @counter=0;
select * from t1;
select * from t2;
select @counter;
delete from t1 where id=2;
select * from t1;
select * from t2;
select @counter;
# This is to verify that the trigger works when deleted directly
delete from t2 where id=6;
select * from t2;
select @counter;
# Cleanup
drop trigger tr_t2;
drop table t2, t1;
|
