Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc

raw
1 #======================================================================
2 #
3 # Trigger Tests
4 # (test case numbering refer to requirement document TP v1.1)
5 #======================================================================
6 # WL#4084: enable disabled parts, 2007-11-15, hhunger
7
8 USE test;
9 --source suite/funcs_1/include/tb3.inc
10
11 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
12 eval
13 load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/memory_tb3.txt'
14 into table tb3;
15
16
17 --disable_abort_on_error
18
19 ##############################################
20 ################ Section 3.5.10 #################
21 # Check on Trigger Activation
22 ##############################################
23 #Section 3.5.10.1
24 # Test case: Ensure that every trigger that should be activated by
25 # every possible type of implicit insertion into its subject
26 # table (INSERT into a view based on the subject table) is
27 # indeed activated correctly
28 #Section 3.5.10.2
29 # Test case: Ensure that every trigger that should be activated by every
30 # possible type of implicit insertion into its subject table
31 # (UPDATE into a view based on the subject table) is indeed
32 # activated correctly
33 #Section 3.5.10.3
34 # Test case: Ensure that every trigger that should be activated by every
35 # possible type of implicit insertion into its subject table
36 # (DELETE from a view based on the subject table) is indeed
37 # activated correctly
38 let $message= Testcase 3.5.10.1/2/3:;
39 --source include/show_msg.inc
40
41 Create view vw11 as select * from tb3
42 where f122 like 'Test 3.5.10.1/2/3%';
43 Create trigger trg1a before insert on tb3
44 for each row set new.f163=111.11;
45 Create trigger trg1b after insert on tb3
46 for each row set @test_var='After Insert';
47 Create trigger trg1c before update on tb3
48 for each row set new.f121='Y', new.f122='Test 3.5.10.1/2/3-Update';
49 Create trigger trg1d after update on tb3
50 for each row set @test_var='After Update';
51 Create trigger trg1e before delete on tb3
52 for each row set @test_var=5;
53 Create trigger trg1f after delete on tb3
54 for each row set @test_var= 2* @test_var+7;
55
56 #Section 3.5.10.1
57 Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 1);
58 Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 2);
59 Insert into vw11 (f122, f151) values ('Not in View', 3);
60 select f121, f122, f151, f163
61 from tb3 where f122 like 'Test 3.5.10.1/2/3%' order by f151;
62 --sorted_result
63 select f121, f122, f151, f163 from vw11;
64 select f121, f122, f151, f163
65 from tb3 where f122 like 'Not in View';
66
67 #Section 3.5.10.2
68 Update vw11 set f163=1;
69 select f121, f122, f151, f163 from tb3
70 where f122 like 'Test 3.5.10.1/2/3%' order by f151;
71 --sorted_result
72 select f121, f122, f151, f163 from vw11;
73
74 #Section 3.5.10.3
75 set @test_var=0;
76 Select @test_var as 'before delete';
77 delete from vw11 where f151=1;
78 select f121, f122, f151, f163 from tb3
79 where f122 like 'Test 3.5.10.1/2/3%' order by f151;
80 --sorted_result
81 select f121, f122, f151, f163 from vw11;
82 Select @test_var as 'after delete';
83
84 #Cleanup
85 --disable_warnings
86 drop view vw11;
87 drop trigger trg1a;
88 drop trigger trg1b;
89 drop trigger trg1c;
90 drop trigger trg1d;
91 drop trigger trg1e;
92 drop trigger trg1f;
93 delete from tb3 where f122 like 'Test 3.5.10.1/2/3%';
94 --enable_warnings
95
96
97 #Section 3.5.10.4
98 # Test case: Ensure that every trigger that should be activated by every
99 # possible type of implicit insertion into its subject table
100 # (LOAD into the subject table) is indeed activated correctly
101 let $message= Testcase 3.5.10.4:;
102 --source include/show_msg.inc
103
104 --replace_result $engine_type <engine_to_be_used>
105 eval create table tb_load (f1 int, f2 char(25),f3 int) engine = $engine_type;
106 Create trigger trg4 before insert on tb_load
107 for each row set new.f3=-(new.f1 div 5), @counter= @counter+1;
108
109 set @counter= 0;
110 select @counter as 'Rows Loaded Before';
111 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
112 eval load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t9.txt' into table tb_load;
113
114 select @counter as 'Rows Loaded After';
115 Select * from tb_load order by f1 limit 10;
116
117 #Cleanup
118 --disable_warnings
119 drop trigger trg4;
120 drop table tb_load;
121 --enable_warnings
122
123
124 #Section 3.5.10.5
125 # Testcase: Ensure that every trigger that should be activated by every possible
126 # type of implicit update of its subject table (e.g.a FOREIGN KEY SET
127 # DEFAULT action or an UPDATE of a view based on the subject table) is
128 # indeed activated correctly
129 let $message= Testcase 3.5.10.5: (implemented in trig_frkey.test);
130 --source include/show_msg.inc
131
132
133 #Section 3.5.10.6
134 # Testcase: Ensure that every trigger that should be activated by every possible
135 # type of implicit deletion from its subject table (e.g.a FOREIGN KEY
136 # CASCADE action or a DELETE from a view based on the subject table) is
137 # indeed activated correctly
138 let $message= Testcase 3.5.10.6: (implemented in trig_frkey.test);
139 --source include/show_msg.inc
140
141 #Section 3.5.10.extra
142 # Testcase: Ensure that every trigger that should be activated by every possible
143 # type of implicit deletion from its subject table (e.g. an action performed
144 # on the subject table from a stored procedure is indeed activated correctly
145 let $message= Testcase 3.5.10.extra:;
146 --source include/show_msg.inc
147 set sql_mode = 'NO_ENGINE_SUBSTITUTION';
148 --replace_result $engine_type <engine_to_be_used>
149 eval create table t1_sp (var136 tinyint, var151 decimal) engine = $engine_type;
150
151 create trigger trg before insert on t1_sp
152 for each row set @counter=@counter+1;
153 # declare continue handler for sqlstate '01000' set done = 1;
154
155 delimiter //;
156 create procedure trig_sp()
157 begin
158 declare done int default 0;
159 declare var151 decimal;
160 declare var136 tinyint;
161 declare cur1 cursor for select f136, f151 from tb3;
162 declare continue handler for sqlstate '01000' set done = 1;
163 open cur1;
164 fetch cur1 into var136, var151;
165 wl_loop: WHILE NOT done DO
166 insert into t1_sp values (var136, var151);
167 fetch cur1 into var136, var151;
168 END WHILE wl_loop;
169 close cur1;
170 end//
171 delimiter ;//
172
173 set @counter=0;
174 select @counter;
175 --error ER_SP_FETCH_NO_DATA
176 call trig_sp();
177 select @counter;
178 select count(*) from tb3;
179 select count(*) from t1_sp;
180
181 #Cleanup
182 --disable_warnings
183 drop procedure trig_sp;
184 drop trigger trg;
185 drop table t1_sp;
186 --enable_warnings
187 set sql_mode = default;
188
189 ##################################
190 ########## Section 3.5.11 ########
191 # Check on Trigger Performance #
192 ##################################
193 #Section 3.5.11.1
194 # Testcase: Ensure that a set of complicated, interlocking triggers that are activated
195 # by multiple trigger events on no fewer than 50 different tables with at least
196 # 500,000 rows each, all work correctly, return the correct results, and have
197 # the correct effects on the database. It is expected that the Services Provider
198 # will use its own skills and experience in database testing to devise tables and
199 # triggers that fulfill this requirement.
200 let $message= Testcase 3.5.11.1 (implemented in trig_perf.test);
201 --source include/show_msg.inc
202
203
204 ##########################################
205 # Other Scenasrios (not in requirements) #
206 ##########################################
207 # Testcase: y.y.y.2:
208 # Checking for triggers starting triggers (no direct requirement)
209 let $message= Testcase y.y.y.2: Check for triggers starting triggers;
210 --source include/show_msg.inc
211
212 use test;
213 --disable_warnings
214 drop table if exists t1;
215 drop table if exists t2_1;
216 drop table if exists t2_2;
217 drop table if exists t2_3;
218 drop table if exists t2_4;
219 drop table if exists t3;
220 --enable_warnings
221
222 --replace_result $engine_type <engine_to_be_used>
223 eval create table t1 (f1 integer) engine = $engine_type;
224 --replace_result $engine_type <engine_to_be_used>
225 eval create table t2_1 (f1 integer) engine = $engine_type;
226 --replace_result $engine_type <engine_to_be_used>
227 eval create table t2_2 (f1 integer) engine = $engine_type;
228 --replace_result $engine_type <engine_to_be_used>
229 eval create table t2_3 (f1 integer) engine = $engine_type;
230 --replace_result $engine_type <engine_to_be_used>
231 eval create table t2_4 (f1 integer) engine = $engine_type;
232 --replace_result $engine_type <engine_to_be_used>
233 eval create table t3 (f1 integer) engine = $engine_type;
234
235 insert into t1 values (1);
236 delimiter //;
237 create trigger tr1 after insert on t1 for each row
238 BEGIN
239 insert into t2_1 (f1) values (new.f1+1);
240 insert into t2_2 (f1) values (new.f1+1);
241 insert into t2_3 (f1) values (new.f1+1);
242 insert into t2_4 (f1) values (new.f1+1);
243 END//
244 delimiter ;//
245
246 create trigger tr2_1 after insert on t2_1 for each row
247 insert into t3 (f1) values (new.f1+10);
248 create trigger tr2_2 after insert on t2_2 for each row
249 insert into t3 (f1) values (new.f1+100);
250 create trigger tr2_3 after insert on t2_3 for each row
251 insert into t3 (f1) values (new.f1+1000);
252 create trigger tr2_4 after insert on t2_4 for each row
253 insert into t3 (f1) values (new.f1+10000);
254
255 #lock tables t1 write, t2_1 write, t2_2 write, t2_3 write, t2_4 write, t3 write;
256 insert into t1 values (1);
257 #unlock tables;
258 select * from t3 order by f1;
259
260 #Cleanup
261 --disable_warnings
262 drop trigger tr1;
263 drop trigger tr2_1;
264 drop trigger tr2_2;
265 drop trigger tr2_3;
266 drop trigger tr2_4;
267 drop table t1, t2_1, t2_2, t2_3, t2_4, t3;
268 --enable_warnings
269
270 # Testcase: y.y.y.3:
271 # Checking for circular trigger definitions
272 let $message= Testcase y.y.y.3: Circular trigger reference;
273 --source include/show_msg.inc
274 use test;
275 --disable_warnings
276 drop table if exists t1;
277 drop table if exists t2;
278 drop table if exists t3;
279 drop table if exists t4;
280 --enable_warnings
281 --replace_result $engine_type <engine_to_be_used>
282 eval create table t1 (f1 integer) engine = $engine_type;
283 --replace_result $engine_type <engine_to_be_used>
284 eval create table t2 (f2 integer) engine = $engine_type;
285 --replace_result $engine_type <engine_to_be_used>
286 eval create table t3 (f3 integer) engine = $engine_type;
287 --replace_result $engine_type <engine_to_be_used>
288 eval create table t4 (f4 integer) engine = $engine_type;
289
290 insert into t1 values (0);
291 create trigger tr1 after insert on t1
292 for each row insert into t2 (f2) values (new.f1+1);
293 create trigger tr2 after insert on t2
294 for each row insert into t3 (f3) values (new.f2+1);
295 create trigger tr3 after insert on t3
296 for each row insert into t4 (f4) values (new.f3+1);
297 create trigger tr4 after insert on t4
298 for each row insert into t1 (f1) values (new.f4+1);
299
300 # Bug#11896 Partial locking in case of recursive trigger definittions
301 --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
302 insert into t1 values (1);
303 select * from t1 order by f1;
304 select * from t2 order by f2;
305 select * from t3 order by f3;
306 select * from t4 order by f4;
307
308 #Cleanup
309 --disable_warnings
310 drop trigger tr1;
311 drop trigger tr2;
312 drop trigger tr3;
313 drop trigger tr4;
314 drop table t1;
315 drop table t2;
316 drop table t3;
317 drop table t4;
318 --enable_warnings
319
320
321 #Section y.y.y.4
322 # Testcase: create recursive trigger/storedprocedures conditions
323 let $message= Testcase y.y.y.4: Recursive trigger/SP references;
324 --source include/show_msg.inc
325 set sql_mode = 'NO_ENGINE_SUBSTITUTION';
326 --replace_result $engine_type <engine_to_be_used>
327 eval create table t1_sp (
328 count integer,
329 var136 tinyint,
330 var151 decimal) engine = $engine_type;
331
332 delimiter //;
333 create procedure trig_sp()
334 begin
335 declare done int default 0;
336 declare var151 decimal;
337 declare var136 tinyint;
338 declare cur1 cursor for select f136, f151 from tb3;
339 declare continue handler for sqlstate '01000' set done = 1;
340 set @counter= @counter+1;
341 open cur1;
342 fetch cur1 into var136, var151;
343 wl_loop: WHILE NOT done DO
344 insert into t1_sp values (@counter, var136, var151);
345 fetch cur1 into var136, var151;
346 END WHILE wl_loop;
347 close cur1;
348 end//
349 delimiter ;//
350
351 create trigger trg before insert on t1_sp
352 for each row call trig_sp();
353
354 set @counter=0;
355 select @counter;
356 --error ER_SP_RECURSION_LIMIT
357 call trig_sp();
358 select @counter;
359 select count(*) from tb3;
360 select count(*) from t1_sp;
361
362 # check recursion will not work here:
363 set @@max_sp_recursion_depth= 10;
364 set @counter=0;
365 select @counter;
366 --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
367 call trig_sp();
368 select @counter;
369 select count(*) from tb3;
370 select count(*) from t1_sp;
371
372 #Cleanup
373 --disable_warnings
374 drop procedure trig_sp;
375 drop trigger trg;
376 drop table t1_sp;
377 --enable_warnings
378 set sql_mode = default;
379
380
381 # Testcase: y.y.y.5:
382 # Checking rollback of nested trigger definitions
383 let $message= Testcase y.y.y.5: Rollback of nested trigger references;
384 --source include/show_msg.inc
385
386 set @@sql_mode='traditional';
387 use test;
388 --disable_warnings
389 drop table if exists t1;
390 drop table if exists t2;
391 drop table if exists t3;
392 drop table if exists t4;
393 --enable_warnings
394 --replace_result $engine_type <engine_to_be_used>
395 eval create table t1 (f1 integer) engine = $engine_type;
396 --replace_result $engine_type <engine_to_be_used>
397 eval create table t2 (f2 integer) engine = $engine_type;
398 --replace_result $engine_type <engine_to_be_used>
399 eval create table t3 (f3 integer) engine = $engine_type;
400 --replace_result $engine_type <engine_to_be_used>
401 eval create table t4 (f4 tinyint) engine = $engine_type;
402 --replace_result $engine_type <engine_to_be_used>
403 show create table t1;
404 insert into t1 values (1);
405 create trigger tr1 after insert on t1
406 for each row insert into t2 (f2) values (new.f1+1);
407 create trigger tr2 after insert on t2
408 for each row insert into t3 (f3) values (new.f2+1);
409 create trigger tr3 after insert on t3
410 for each row insert into t4 (f4) values (new.f3+1000);
411
412 set autocommit=0;
413 start transaction;
414 --error ER_WARN_DATA_OUT_OF_RANGE
415 insert into t1 values (1);
416 commit;
417 select * from t1 order by f1;
418 select * from t2 order by f2;
419 select * from t3 order by f3;
420
421 #Cleanup
422 drop trigger tr1;
423 drop trigger tr2;
424 drop trigger tr3;
425 drop table t1;
426 drop table t2;
427 drop table t3;
428 drop table t4;
429
430 DROP TABLE test.tb3;