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--disable_abort_on_error 17 18############################################## 19################ Section 3.5.10 ################# 20# Check on Trigger Activation 21############################################## 22#Section 3.5.10.1 23# Test case: Ensure that every trigger that should be activated by 24# every possible type of implicit insertion into its subject 25# table (INSERT into a view based on the subject table) is 26# indeed activated correctly 27#Section 3.5.10.2 28# Test case: Ensure that every trigger that should be activated by every 29# possible type of implicit insertion into its subject table 30# (UPDATE into a view based on the subject table) is indeed 31# activated correctly 32#Section 3.5.10.3 33# Test case: Ensure that every trigger that should be activated by every 34# possible type of implicit insertion into its subject table 35# (DELETE from a view based on the subject table) is indeed 36# activated correctly 37let $message= Testcase 3.5.10.1/2/3:; 38--source include/show_msg.inc 39 40 Create view vw11 as select * from tb3 41 where f122 like 'Test 3.5.10.1/2/3%'; 42 Create trigger trg1a before insert on tb3 43 for each row set new.f163=111.11; 44 Create trigger trg1b after insert on tb3 45 for each row set @test_var='After Insert'; 46 Create trigger trg1c before update on tb3 47 for each row set new.f121='Y', new.f122='Test 3.5.10.1/2/3-Update'; 48 Create trigger trg1d after update on tb3 49 for each row set @test_var='After Update'; 50 Create trigger trg1e before delete on tb3 51 for each row set @test_var=5; 52 Create trigger trg1f after delete on tb3 53 for each row set @test_var= 2* @test_var+7; 54 55#Section 3.5.10.1 56 Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 1); 57 Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 2); 58 Insert into vw11 (f122, f151) values ('Not in View', 3); 59 select f121, f122, f151, f163 60 from tb3 where f122 like 'Test 3.5.10.1/2/3%' order by f151; 61 --sorted_result 62 select f121, f122, f151, f163 from vw11; 63 select f121, f122, f151, f163 64 from tb3 where f122 like 'Not in View'; 65 66#Section 3.5.10.2 67 Update vw11 set f163=1; 68 select f121, f122, f151, f163 from tb3 69 where f122 like 'Test 3.5.10.1/2/3%' order by f151; 70 --sorted_result 71 select f121, f122, f151, f163 from vw11; 72 73#Section 3.5.10.3 74 set @test_var=0; 75 Select @test_var as 'before delete'; 76 delete from vw11 where f151=1; 77 select f121, f122, f151, f163 from tb3 78 where f122 like 'Test 3.5.10.1/2/3%' order by f151; 79 --sorted_result 80 select f121, f122, f151, f163 from vw11; 81 Select @test_var as 'after delete'; 82 83#Cleanup 84 --disable_warnings 85 drop view vw11; 86 drop trigger trg1a; 87 drop trigger trg1b; 88 drop trigger trg1c; 89 drop trigger trg1d; 90 drop trigger trg1e; 91 drop trigger trg1f; 92 delete from tb3 where f122 like 'Test 3.5.10.1/2/3%'; 93 --enable_warnings 94 95 96#Section 3.5.10.4 97# Test case: Ensure that every trigger that should be activated by every 98# possible type of implicit insertion into its subject table 99# (LOAD into the subject table) is indeed activated correctly 100let $message= Testcase 3.5.10.4:; 101--source include/show_msg.inc 102 103 --replace_result $engine_type <engine_to_be_used> 104 eval create table tb_load (f1 int, f2 char(25),f3 int) engine = $engine_type; 105 Create trigger trg4 before insert on tb_load 106 for each row set new.f3=-(new.f1 div 5), @counter= @counter+1; 107 108 set @counter= 0; 109 select @counter as 'Rows Loaded Before'; 110 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> 111 eval load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t9.txt' into table tb_load; 112 113 select @counter as 'Rows Loaded After'; 114 Select * from tb_load order by f1 limit 10; 115 116#Cleanup 117 --disable_warnings 118 drop trigger trg4; 119 drop table tb_load; 120 --enable_warnings 121 122 123#Section 3.5.10.5 124# Testcase: Ensure that every trigger that should be activated by every possible 125# type of implicit update of its subject table (e.g.a FOREIGN KEY SET 126# DEFAULT action or an UPDATE of a view based on the subject table) is 127# indeed activated correctly 128let $message= Testcase 3.5.10.5: (implemented in trig_frkey.test); 129--source include/show_msg.inc 130 131 132#Section 3.5.10.6 133# Testcase: Ensure that every trigger that should be activated by every possible 134# type of implicit deletion from its subject table (e.g.a FOREIGN KEY 135# CASCADE action or a DELETE from a view based on the subject table) is 136# indeed activated correctly 137let $message= Testcase 3.5.10.6: (implemented in trig_frkey.test); 138--source include/show_msg.inc 139 140#Section 3.5.10.extra 141# Testcase: Ensure that every trigger that should be activated by every possible 142# type of implicit deletion from its subject table (e.g. an action performed 143# on the subject table from a stored procedure is indeed activated correctly 144let $message= Testcase 3.5.10.extra:; 145--source include/show_msg.inc 146 147 --replace_result $engine_type <engine_to_be_used> 148 eval create table t1_sp (var136 tinyint, var151 decimal) engine = $engine_type; 149 150 create trigger trg before insert on t1_sp 151 for each row set @counter=@counter+1; 152 # declare continue handler for sqlstate '01000' set done = 1; 153 154 SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 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 188################################## 189########## Section 3.5.11 ######## 190# Check on Trigger Performance # 191################################## 192#Section 3.5.11.1 193# Testcase: Ensure that a set of complicated, interlocking triggers that are activated 194# by multiple trigger events on no fewer than 50 different tables with at least 195# 500,000 rows each, all work correctly, return the correct results, and have 196# the correct effects on the database. It is expected that the Services Provider 197# will use its own skills and experience in database testing to devise tables and 198# triggers that fulfill this requirement. 199let $message= Testcase 3.5.11.1 (implemented in trig_perf.test); 200--source include/show_msg.inc 201 202 203########################################## 204# Other Scenasrios (not in requirements) # 205########################################## 206# Testcase: y.y.y.2: 207# Checking for triggers starting triggers (no direct requirement) 208let $message= Testcase y.y.y.2: Check for triggers starting triggers; 209--source include/show_msg.inc 210 211 use test; 212 --disable_warnings 213 drop table if exists t1; 214 drop table if exists t2_1; 215 drop table if exists t2_2; 216 drop table if exists t2_3; 217 drop table if exists t2_4; 218 drop table if exists t3; 219 --enable_warnings 220 221 --replace_result $engine_type <engine_to_be_used> 222 eval create table t1 (f1 integer) engine = $engine_type; 223 --replace_result $engine_type <engine_to_be_used> 224 eval create table t2_1 (f1 integer) engine = $engine_type; 225 --replace_result $engine_type <engine_to_be_used> 226 eval create table t2_2 (f1 integer) engine = $engine_type; 227 --replace_result $engine_type <engine_to_be_used> 228 eval create table t2_3 (f1 integer) engine = $engine_type; 229 --replace_result $engine_type <engine_to_be_used> 230 eval create table t2_4 (f1 integer) engine = $engine_type; 231 --replace_result $engine_type <engine_to_be_used> 232 eval create table t3 (f1 integer) engine = $engine_type; 233 234 insert into t1 values (1); 235 delimiter //; 236 create trigger tr1 after insert on t1 for each row 237 BEGIN 238 insert into t2_1 (f1) values (new.f1+1); 239 insert into t2_2 (f1) values (new.f1+1); 240 insert into t2_3 (f1) values (new.f1+1); 241 insert into t2_4 (f1) values (new.f1+1); 242 END// 243 delimiter ;// 244 245 create trigger tr2_1 after insert on t2_1 for each row 246 insert into t3 (f1) values (new.f1+10); 247 create trigger tr2_2 after insert on t2_2 for each row 248 insert into t3 (f1) values (new.f1+100); 249 create trigger tr2_3 after insert on t2_3 for each row 250 insert into t3 (f1) values (new.f1+1000); 251 create trigger tr2_4 after insert on t2_4 for each row 252 insert into t3 (f1) values (new.f1+10000); 253 254#lock tables t1 write, t2_1 write, t2_2 write, t2_3 write, t2_4 write, t3 write; 255 insert into t1 values (1); 256#unlock tables; 257 select * from t3 order by f1; 258 259#Cleanup 260 --disable_warnings 261 drop trigger tr1; 262 drop trigger tr2_1; 263 drop trigger tr2_2; 264 drop trigger tr2_3; 265 drop trigger tr2_4; 266 drop table t1, t2_1, t2_2, t2_3, t2_4, t3; 267 --enable_warnings 268 269# Testcase: y.y.y.3: 270# Checking for circular trigger definitions 271let $message= Testcase y.y.y.3: Circular trigger reference; 272--source include/show_msg.inc 273 use test; 274 --disable_warnings 275 drop table if exists t1; 276 drop table if exists t2; 277 drop table if exists t3; 278 drop table if exists t4; 279 --enable_warnings 280 --replace_result $engine_type <engine_to_be_used> 281 eval create table t1 (f1 integer) engine = $engine_type; 282 --replace_result $engine_type <engine_to_be_used> 283 eval create table t2 (f2 integer) engine = $engine_type; 284 --replace_result $engine_type <engine_to_be_used> 285 eval create table t3 (f3 integer) engine = $engine_type; 286 --replace_result $engine_type <engine_to_be_used> 287 eval create table t4 (f4 integer) engine = $engine_type; 288 289 insert into t1 values (0); 290 create trigger tr1 after insert on t1 291 for each row insert into t2 (f2) values (new.f1+1); 292 create trigger tr2 after insert on t2 293 for each row insert into t3 (f3) values (new.f2+1); 294 create trigger tr3 after insert on t3 295 for each row insert into t4 (f4) values (new.f3+1); 296 create trigger tr4 after insert on t4 297 for each row insert into t1 (f1) values (new.f4+1); 298 299 # Bug#11896 Partial locking in case of recursive trigger definittions 300 --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG 301 insert into t1 values (1); 302 select * from t1 order by f1; 303 select * from t2 order by f2; 304 select * from t3 order by f3; 305 select * from t4 order by f4; 306 307#Cleanup 308 --disable_warnings 309 drop trigger tr1; 310 drop trigger tr2; 311 drop trigger tr3; 312 drop trigger tr4; 313 drop table t1; 314 drop table t2; 315 drop table t3; 316 drop table t4; 317 --enable_warnings 318 319 320#Section y.y.y.4 321# Testcase: create recursive trigger/storedprocedures conditions 322let $message= Testcase y.y.y.4: Recursive trigger/SP references; 323--source include/show_msg.inc 324 325set @sql_mode='traditional'; 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 367 --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG 368 call trig_sp(); 369 select @counter; 370 select count(*) from tb3; 371 select count(*) from t1_sp; 372 373#Cleanup 374 --disable_warnings 375 drop procedure trig_sp; 376 drop trigger trg; 377 drop table t1_sp; 378 --enable_warnings 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# Bug#32656 NDB: Duplicate key error aborts transaction in handler. 415# Doesn't talk back to SQL 416 --error ER_WARN_DATA_OUT_OF_RANGE 417 insert into t1 values (1); 418 commit; 419 select * from t1 order by f1; 420 select * from t2 order by f2; 421 select * from t3 order by f3; 422 423#Cleanup 424 drop trigger tr1; 425 drop trigger tr2; 426 drop trigger tr3; 427 drop table t1; 428 drop table t2; 429 drop table t3; 430 drop table t4; 431DROP TABLE test.tb3; 432SET sql_mode = default; 433