1#====================================================================== 2# 3# Trigger Tests 4# (test case numbering refer to requirement document TP v1.1) 5#====================================================================== 6 7USE test; 8--source suite/funcs_1/include/tb3.inc 9 10--disable_abort_on_error 11 12# General setup for Trigger tests 13let $message= Testcase: 3.5:; 14--source include/show_msg.inc 15 16--disable_abort_on_error 17 18 create User test_general@localhost; 19 set password for test_general@localhost = password('PWD'); 20 revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost; 21 22 create User test_super@localhost; 23 set password for test_super@localhost = password('PWD'); 24 grant ALL on *.* to test_super@localhost with grant OPTION; 25 connect (con1_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); 26 connect (con1_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); 27 connection default; 28 29#################################### 30############ Section 3.5.4 ######### 31# Drop Trigger Checkes: # 32#################################### 33let $message= Testcase 3.5.4:; 34--source include/show_msg.inc 35 36 connection default; 37 use test; 38 39#Section 3.5.4.1 40# Testcase: Ensure that the DROP TRIGGER statement cleanly drops its target trigger. 41let $message= Testcase 3.5.4.1:; 42--source include/show_msg.inc 43 44 connection con1_super; 45 create database db_drop; 46 Use db_drop; 47 --replace_result $engine_type <engine_to_be_used> 48 eval create table t1 (f1 char(30)) engine = $engine_type; 49 grant INSERT, SELECT on db_drop.t1 to test_general; 50 Use db_drop; 51 Create trigger trg1 BEFORE INSERT on t1 52 for each row set new.f1='Trigger 3.5.4.1'; 53 connection con1_general; 54 Use db_drop; 55 Insert into t1 values ('Insert error 3.5.4.1'); 56 Select * from t1 order by f1; 57 connection con1_super; 58 drop trigger trg1; 59 select trigger_schema, trigger_name, event_object_table 60 from information_schema.triggers 61 where trigger_schema = 'db_drop' 62 order by trigger_name; 63 connection con1_general; 64 Insert into t1 values ('Insert no trigger 3.5.4.1'); 65 Select * from t1 order by f1; 66 67#Cleanup 68 --disable_warnings 69 connection con1_super; 70 --disable_warnings 71 --error 0,ER_TRG_DOES_NOT_EXIST 72 drop trigger trg1; 73 drop database if exists db_drop; 74 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; 75 --enable_warnings 76 77#Section 3.5.4.2 78# Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate error 79# message, if the trigger name does not exist. 80let $message= Testcase 3.5.4.2:; 81--source include/show_msg.inc 82 83 connection con1_super; 84 create database db_drop2; 85 Use db_drop2; 86 --disable_warnings 87 drop table if exists t1_432 ; 88 --enable_warnings 89 --replace_result $engine_type <engine_to_be_used> 90 eval create table t1_432 (f1 char (30)) engine = $engine_type; 91 --error ER_TRG_DOES_NOT_EXIST 92 Drop trigger tr_does_not_exit; 93#cleanup 94 --disable_warnings 95 drop table if exists t1_432 ; 96 drop database if exists db_drop2; 97 --enable_warnings 98 99#Section 3.5.4.3 100# Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate 101# error message, if <trigger name> is not a qualified name. 102let $message= Testcase 3.5.4.3:; 103--source include/show_msg.inc 104 105 connection con1_super; 106 create database db_drop3; 107 Use db_drop3; 108 --disable_warnings 109 drop table if exists t1_433 ; 110 drop table if exists t1_433a ; 111 --enable_warnings 112 --replace_result $engine_type <engine_to_be_used> 113 eval create table t1_433 (f1 char (30)) engine = $engine_type; 114 --replace_result $engine_type <engine_to_be_used> 115 eval create table t1_433a (f1a char (5)) engine = $engine_type; 116 117 CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row 118 set new.f1 = 'Trigger 3.5.4.3'; 119 120# Using table 121 --error ER_PARSE_ERROR 122 Drop trigger t1.433.trg3; 123 124# Using database.table 125 --error ER_PARSE_ERROR 126 Drop trigger db_drop3.t1.433.trg3; 127 128# wrong database 129 --error ER_TRG_DOES_NOT_EXIST 130 Drop trigger mysql.trg3; 131 132# database does not exist 133 --error ER_TRG_DOES_NOT_EXIST 134 Drop trigger tbx.trg3; 135 136#cleanup 137 Drop trigger db_drop3.trg3; 138 drop table if exists t1_433; 139 drop table if exists t1_433a; 140 drop database if exists db_drop3; 141 142#Section 3.5.4.4 143# Test case: Ensure that when a database is dropped, all triggers created within 144# that database are also cleanly dropped. 145let $message= Testcase 3.5.4.4:; 146--source include/show_msg.inc 147 148 connection con1_super; 149 create database db_drop4; 150 Use db_drop4; 151 --replace_result $engine_type <engine_to_be_used> 152 eval create table t1 (f1 char(30)) engine = $engine_type; 153 grant INSERT, SELECT on db_drop4.t1 to test_general; 154 Create trigger trg4 BEFORE INSERT on t1 155 for each row set new.f1='Trigger 3.5.4.4'; 156 connection con1_general; 157 Use db_drop4; 158 Insert into t1 values ('Insert 3.5.4.4'); 159 Select * from t1; 160 connection con1_super; 161 Drop database db_drop4; 162 Show databases like 'db_drop4'; 163 select trigger_schema, trigger_name, event_object_table 164 from information_schema.triggers 165 where information_schema.triggers.trigger_name='trg4'; 166 create database db_drop4; 167 Use db_drop4; 168 --replace_result $engine_type <engine_to_be_used> 169 eval create table t1 (f1 char(30)) engine = $engine_type; 170 grant INSERT, SELECT on db_drop4.t1 to test_general; 171 connection con1_general; 172 Insert into t1 values ('2nd Insert 3.5.4.4'); 173 Select * from t1; 174 175#Cleanup 176 connection con1_super; 177 --disable_warnings 178 --error ER_TRG_DOES_NOT_EXIST 179 drop trigger trg4; 180 drop database if exists db_drop4; 181 --enable_warnings 182 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; 183 184#Section 3.5.4.5 185# Test case: Ensure that when a table is dropped, all triggers for which it is the 186# subject table are also cleanly dropped. 187let $message= Testcase 3.5.4.5:; 188--source include/show_msg.inc 189 190 connection con1_super; 191 create database db_drop5; 192 Use db_drop5; 193 --replace_result $engine_type <engine_to_be_used> 194 eval create table t1 (f1 char(50)) engine = $engine_type; 195 grant INSERT, SELECT on t1 to test_general; 196 Create trigger trg5 BEFORE INSERT on t1 197 for each row set new.f1='Trigger 3.5.4.5'; 198 connection con1_general; 199 Use db_drop5; 200 Insert into t1 values ('Insert 3.5.4.5'); 201 Select * from t1; 202 connection con1_super; 203 Drop table t1; 204 Show tables; 205 select trigger_schema, trigger_name, event_object_table 206 from information_schema.triggers 207 where information_schema.triggers.trigger_name='trg5'; 208 --replace_result $engine_type <engine_to_be_used> 209 eval create table t1 (f1 char(50)) engine = $engine_type; 210 grant INSERT, SELECT on t1 to test_general; 211 connection con1_general; 212 Insert into t1 values ('2nd Insert 3.5.4.5'); 213 Select * from t1; 214 215#Cleanup 216 connection con1_super; 217 --disable_warnings 218 --error ER_TRG_DOES_NOT_EXIST 219 drop trigger trg5; 220 drop database if exists db_drop5; 221 --enable_warnings 222 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; 223 224 225################################## 226######### Section 3.5.5 ########## 227# Checks on the Subject Table # 228################################## 229 230let $message= Testcase 3.5.5:; 231--source include/show_msg.inc 232 233 connection default; 234 use test; 235 236#Section 3.5.5.1 237# Test case: Ensure that, if CREATE TRIGGER is executed with a non-existent 238# subject table, the statement fails with an appropriate error message. 239let $message= Testcase 3.5.5.1:; 240--source include/show_msg.inc 241 242 --error ER_NO_SUCH_TABLE 243 Create trigger trg1 before INSERT on t100 for each row set new.f2=1000; 244 245 246#Section 3.5.5.2 247# Test case: Ensure that, if CREATE TRIGGER is executed with a temporary table 248# as the subject table, the statement fails with an appropriate error message. 249let $message= Testcase 3.5.5.2:; 250--source include/show_msg.inc 251 252 Create temporary table t1_temp (f1 bigint signed, f2 bigint unsigned); 253 254 --error ER_TRG_ON_VIEW_OR_TEMP_TABLE 255 Create trigger trg2 before INSERT 256 on t1_temp for each row set new.f2=9999; 257 258#Cleanup 259 --disable_warnings 260 drop table t1_temp; 261 --enable_warnings 262 263 264#Section 3.5.5.3 265# Test case: Ensure that, if CREATE TRIGGER is executed with a view as the subject 266# table, the statement fails with an appropriate error message. 267let $message= Testcase 3.5.5.3:; 268--source include/show_msg.inc 269 270 Create view vw3 as select f118 from tb3; 271 272# OBN Not sure why the server is returning error ER_WRONG_OBJECT 273 --error ER_WRONG_OBJECT 274 Create trigger trg3 before INSERT 275 on vw3 for each row set new.f118='s'; 276 277#Cleanup 278 --disable_warnings 279 drop view vw3; 280 --enable_warnings 281 282 283#Section 3.5.5.4 284# Test case: Ensure that, if CREATE TRIGGER is executed with a table that resides 285# in a different database than in which the trigger will reside, the 286# statement fails with an appropriate error message; that is, ensure that 287# the trigger and its subject table must reside in the same database. 288let $message= Testcase 3.5.5.4:; 289--source include/show_msg.inc 290 291 connection con1_super; 292 create database dbtest_one; 293 create database dbtest_two; 294 use dbtest_two; 295 --replace_result $engine_type <engine_to_be_used> 296 eval create table t2 (f1 char(15)) engine = $engine_type; 297 use dbtest_one; 298 --error ER_TRG_IN_WRONG_SCHEMA 299 create trigger trg4 before INSERT 300 on dbtest_two.t2 for each row set new.f1='trig 3.5.5.4'; 301 grant INSERT, SELECT on dbtest_two.t2 to test_general; 302 grant SELECT on dbtest_one.* to test_general; 303 connection con1_general; 304 use dbtest_two; 305 Insert into t2 values ('1st Insert 3.5.5.4'); 306 Select * from t2; 307 use dbtest_one; 308 Insert into dbtest_two.t2 values ('2nd Insert 3.5.5.4'); 309 Select * from dbtest_two.t2 order by f1; 310 311#Cleanup 312 connection con1_super; 313 --disable_warnings 314 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; 315 DROP DATABASE if exists dbtest_one; 316 drop database if EXISTS dbtest_two; 317 --enable_warnings 318 319##################################### 320########### Section 3.5.6 ########### 321# Check on the Trigger Action Time # 322##################################### 323 324let $message= Testcase 3.5.6:; 325--source include/show_msg.inc 326 327 connection default; 328 use test; 329 330#Section 3.5.6.1 331# Test case: Ensure that a trigger definition can specify a trigger action time of BEFORE. 332# See section 3.5.1.1 333let $message= Testcase 3.5.6.1 (see Testcase 3.5.1.1); 334--source include/show_msg.inc 335 336#Section 3.5.6.2 337# Test case: Ensure that a trigger definition can specify a trigger action time of AFTER. 338# See section 3.5.1.1 339let $message= Testcase 3.5.6.2 (see Testcase 3.5.1.1); 340--source include/show_msg.inc 341 342#Section 3.5.6.3 343# Test case: Ensure that a trigger definition that specifies a trigger action 344# time that is not either BEFORE or AFTER fails, with an appropriate 345# error message, at CREATE TRIGGER time. 346let $message= Testcase 3.5.6.3:; 347--source include/show_msg.inc 348 349 --error ER_PARSE_ERROR 350 Create trigger trg3_1 DURING UPDATE on tb3 for each row set new.f132=25; 351 --error ER_PARSE_ERROR 352 Create trigger trg3_2 TIME INSERT on tb3 for each row set new.f132=15; 353 354#Cleanup 355# OBN - Although none of the above should have been created we should do a cleanup 356# since if they have been created, not dropping them will affect following 357# tests. 358 --disable_warnings 359 --error 0, ER_TRG_DOES_NOT_EXIST 360 drop trigger tb3.trg3_1; 361 --error 0, ER_TRG_DOES_NOT_EXIST 362 drop trigger tb3.trg3_2; 363 --enable_warnings 364 365#Section 3.5.6.4 366# Test case: Ensure that a trigger defined with a trigger action time of BEFORE 367# always executes its triggered action immediately before the trigger event. 368# See section 3.5.1.1 369let $message= Testcase 3.5.6.4 (see Testcase 3.5.1.1); 370--source include/show_msg.inc 371 372#Section 3.5.6.5 373# Test case: Ensure that a trigger defined with a trigger action time of AFTER 374# always executes its triggered action immediately after the trigger event. 375let $message= Testcase 3.5.6.5 (see Testcase 3.5.1.1); 376--source include/show_msg.inc 377 378############################# 379####### Section 3.5.7 ####### 380# Check on Trigger Event # 381############################# 382 383#Section 3.5.7.1 384#Test case: Ensure that a trigger definition can specify a trigger event of INSERT. 385let $message= Testcase 3.5.7.1 (see Testcase 3.5.1.1); 386--source include/show_msg.inc 387 388#Section 3.5.7.2 389# Test case: Ensure that a trigger definition can specify a trigger event of UPDATE. 390let $message= Testcase 3.5.7.2 (see Testcase 3.5.1.1); 391--source include/show_msg.inc 392 393#Section 3.5.7.3 394# Test case: Ensure that a trigger definition can specify a trigger event of DELETE. 395let $message= Testcase 3.5.7.3 (see Testcase 3.5.1.1); 396--source include/show_msg.inc 397 398#Section 3.5.7.4 399# Test case: Ensure that a trigger definition that specifies a trigger event that 400# is not either INSERT, UPDATE or DELETE fails, with an appropriate error 401# message, at CREATE TRIGGER time. 402let $message= Testcase 3.5.7.4:; 403--source include/show_msg.inc 404 405 --error ER_PARSE_ERROR 406 Create trigger trg4_1 BEFORE SELECT on tb3 for each row set new.f132=5; 407 --error ER_PARSE_ERROR 408 Create trigger trg4_2 AFTER VALUE on tb3 for each row set new.f132=1; 409 410#Cleanup 411# OBN - Although none of the above should have been created we should do a cleanup 412# since if they have been created, not dropping them will affect following 413# tests. 414 --disable_warnings 415 --error 0, ER_TRG_DOES_NOT_EXIST 416 drop trigger tb3.trg4_1; 417 --error 0, ER_TRG_DOES_NOT_EXIST 418 drop trigger tb3.trg4_2; 419 --enable_warnings 420 421#Section 3.5.7.5 / 3.5.7.6 422# Test case: Ensure that it is not possible to create multiple BEFORE INSERT triggers 423# on the same table, even if the triggers have different names / different 424# triggered actions. 425let $message= Testcase 3.5.7.5 / 3.5.7.6:; 426--source include/show_msg.inc 427 428 Create trigger trg5_1 BEFORE INSERT 429 on tb3 for each row set new.f122='Trigger1 3.5.7.5/6'; 430 431 --error ER_NOT_SUPPORTED_YET 432 Create trigger trg5_2 BEFORE INSERT 433 on tb3 for each row set new.f122='Trigger2 3.5.7.5'; 434 435 Insert into tb3 (f121,f122) values ('Test 3.5.7.5/6','Insert 3.5.7.5'); 436 Select f121,f122 from tb3 where f121='Test 3.5.7.5/6'; 437 update tb3 set f122='Update 3.5.7.6' where f121= 'Test 3.5.7.5/6'; 438 Select f121,f122 from tb3 where f121='Test 3.5.7.5/6'; 439 440#Cleanup 441 --disable_warnings 442 drop trigger trg5_1; 443 --error 0, ER_TRG_DOES_NOT_EXIST 444 drop trigger trg5_2; 445 delete from tb3 where f121='Test 3.5.7.5/6'; 446 --enable_warnings 447 448 449#Section 3.5.7.7 / 3.5.7.8 450# Test case: Ensure that it is not possible to create multiple AFTER INSERT triggers 451# on the same table, even if the triggers have different names / different 452# triggered actions. 453let $message= Testcase 3.5.7.7 / 3.5.7.8:; 454--source include/show_msg.inc 455 456 set @test_var='Before trig 3.5.7.7'; 457 Create trigger trg6_1 AFTER INSERT 458 on tb3 for each row set @test_var='Trigger1 3.5.7.7/8'; 459 460 --error ER_NOT_SUPPORTED_YET 461 Create trigger trg6_2 AFTER INSERT 462 on tb3 for each row set @test_var='Trigger2 3.5.7.7'; 463 464 select @test_var; 465 Insert into tb3 (f121,f122) values ('Test 3.5.7.7/8','Insert 3.5.7.7'); 466 Select f121,f122 from tb3 where f121='Test 3.5.7.7/8'; 467 select @test_var; 468 update tb3 set f122='Update 3.5.7.8' where f121= 'Test 3.5.7.7/8'; 469 Select f121,f122 from tb3 where f121='Test 3.5.7.7/8'; 470 select @test_var; 471 472#Cleanup 473 --disable_warnings 474 drop trigger trg6_1; 475 --error 0, ER_TRG_DOES_NOT_EXIST 476 drop trigger trg6_2; 477 delete from tb3 where f121='Test 3.5.7.7/8'; 478 --enable_warnings 479 480 481#Section 3.5.7.9 / 3.5.7.10 482# Test case: Ensure that it is not possible to create multiple BEFORE UPDATE triggers 483# on the same table, even if the triggers have different names / different 484# triggered actions. 485let $message= Testcase 3.5.7.9/10:; 486--source include/show_msg.inc 487 488 Create trigger trg7_1 BEFORE UPDATE 489 on tb3 for each row set new.f122='Trigger1 3.5.7.9/10'; 490 491 --error ER_NOT_SUPPORTED_YET 492 Create trigger trg7_2 BEFORE UPDATE 493 on tb3 for each row set new.f122='Trigger2 3.5.7.9'; 494 495 Insert into tb3 (f121,f122) values ('Test 3.5.7.9/10','Insert 3.5.7.9'); 496 Select f121,f122 from tb3 where f121='Test 3.5.7.9/10'; 497 update tb3 set f122='update 3.5.7.10' where f121='Test 3.5.7.9/10'; 498 Select f121,f122 from tb3 where f121='Test 3.5.7.9/10'; 499 500#Cleanup 501 --disable_warnings 502 drop trigger trg7_1; 503 --error 0, ER_TRG_DOES_NOT_EXIST 504 drop trigger trg7_2; 505 delete from tb3 where f121='Test 3.5.7.9/10'; 506 507#Section 3.5.7.11 / 3.5.7.12 508# Test case: Ensure that it is not possible to create multiple AFTER UPDATE triggers 509# on the same table, even if the triggers have different names / different 510# triggered actions. 511let $message= Testcase 3.5.7.11/12:; 512--source include/show_msg.inc 513 514 set @test_var='Before trig 3.5.7.11'; 515 Create trigger trg8_1 AFTER UPDATE 516 on tb3 for each row set @test_var='Trigger 3.5.7.11/12'; 517 518 --error ER_NOT_SUPPORTED_YET 519 Create trigger trg8_2 AFTER UPDATE 520 on tb3 for each row set @test_var='Trigger2 3.5.7.11'; 521 522 523 select @test_var; 524 Insert into tb3 (f121,f122) values ('Test 3.5.7.11/12','Insert 3.5.7.11/12'); 525 select @test_var; 526 Select f121,f122 from tb3 where f121='Test 3.5.7.11/12'; 527 update tb3 set f122='update 3.5.7.12' where f121='Test 3.5.7.11/12'; 528 Select f121,f122 from tb3 where f121='Test 3.5.7.11/12'; 529 select @test_var; 530 delete from tb3 where f121='Test 3.5.7.11/12'; 531 532#Cleanup 533 --disable_warnings 534 drop trigger trg8_1; 535 --error 0, ER_TRG_DOES_NOT_EXIST 536 drop trigger trg8_2; 537 delete from tb3 where f121='Test 3.5.7.11/12'; 538 539#Section 3.5.7.13 / 3.5.7.14 540# Test case: Ensure that it is not possible to create multiple BEFORE DELETE triggers 541# on the same table, even if the triggers have different names / different 542# triggered actions. 543let $message= Testcase 3.5.7.13/14:; 544--source include/show_msg.inc 545 546 set @test_var=1; 547 Create trigger trg9_1 BEFORE DELETE 548 on tb3 for each row set @test_var=@test_var+1; 549 550 --error ER_NOT_SUPPORTED_YET 551 Create trigger trg9_2 BEFORE DELETE 552 on tb3 for each row set @test_var=@test_var+10; 553 554 select @test_var; 555 Insert into tb3 (f121,f122) values ('Test 3.5.7.13/14','Insert 3.5.7.13'); 556 Select f121,f122 from tb3 where f121='Test 3.5.7.13/14'; 557 select @test_var; 558 delete from tb3 where f121='Test 3.5.7.13/14'; 559 Select f121,f122 from tb3 where f121='Test 3.5.7.13/14'; 560 select @test_var; 561 delete from tb3 where f121='Test 3.5.7.13/14'; 562 select @test_var; 563 564#Cleanup 565 --disable_warnings 566 drop trigger trg9_1; 567 --error 0, ER_TRG_DOES_NOT_EXIST 568 drop trigger trg9_2; 569 delete from tb3 where f121='Test 3.5.7.13/14'; 570 571#Section 3.5.7.15 / 3.5.7.16 572# Test case: Ensure that it is not possible to create multiple AFTER DELETE triggers 573# on the same table, even if the triggers have different names / different 574# triggered actions. 575let $message= Testcase 3.5.7.15/16:; 576--source include/show_msg.inc 577 578 set @test_var=1; 579 Create trigger trg_3_406010_1 AFTER DELETE 580 on tb3 for each row set @test_var=@test_var+5; 581 582 --error ER_NOT_SUPPORTED_YET 583 Create trigger trg_3_406010_2 AFTER DELETE 584 on tb3 for each row set @test_var=@test_var+50; 585 586 --error ER_TRG_ALREADY_EXISTS 587 Create trigger trg_3_406010_1 AFTER INSERT 588 on tb3 for each row set @test_var=@test_var+1; 589 590 select @test_var; 591 Insert into tb3 (f121,f122) values ('Test 3.5.7.15/16','Insert 3.5.7.15/16'); 592 Select f121,f122 from tb3 where f121='Test 3.5.7.15/16'; 593 select @test_var; 594 delete from tb3 where f121='Test 3.5.7.15/16'; 595 Select f121,f122 from tb3 where f121='Test 3.5.7.15/16'; 596 select @test_var; 597 delete from tb3 where f121='Test 3.5.7.15/16'; 598 select @test_var; 599 600#Cleanup 601 --disable_warnings 602 drop trigger trg_3_406010_1; 603 --error 0, ER_TRG_DOES_NOT_EXIST 604 drop trigger trg_3_406010_2; 605 delete from tb3 where f121='Test 3.5.7.15/16'; 606 --enable_warnings 607 608 609#Section 3.5.7.17 610# Test case: Ensure that it is possible to have a BEFORE INSERT, an AFTER INSERT, 611# a BEFORE UPDATE, an AFTER UPDATE, a BEFORE DELETE, and an AFTER DELETE 612# trigger on the same table; that is, ensure that every persistent base 613# table may be the subject table for exactly six triggers 614let $message= Testcase 3.5.7.17 (see Testcase 3.5.1.1); 615--source include/show_msg.inc 616 617 618# Cleanup section 3.5 619 connection default; 620 drop user test_general@localhost; 621 drop user test_general; 622 drop user test_super@localhost; 623 624DROP TABLE test.tb3; 625