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