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 8USE test; 9--source suite/funcs_1/include/tb3.inc 10 11--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> 12eval 13load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/memory_tb3.txt' 14into 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 38let $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 101let $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 129let $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 138let $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 145let $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. 200let $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) 209let $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 272let $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 323let $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 383let $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 430DROP TABLE test.tb3; 431