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# OBM - ToDo 11############ 12# 1. Performace 13############################################### 14 15--disable_abort_on_error 16 17##################################################### 18################# Section 3.5.1 ##################### 19# Syntax checks for CREATE TRIGGER and DROP TRIGGER # 20##################################################### 21 22#Section 3.5.1.1 23# Testcase: Ensure that all clauses that should be supported are supported. 24let $message= Testcase: 3.5.1.1:; 25--source include/show_msg.inc 26# OBN - This test case tests basic trigger definition and execution 27# of INSERT/UPDATE/DELETE actions and BEFORE/AFTER timings. 28# As such it covers the equirements in sections 3.5.6.1, 3.5.6.2, 29# 3.5.6.4, 3.5.6.5, 3.5.7.1, 3.5.7.2, 3.5.7.3, 3.5.7.17 below. 30# - Note currently as a result of limitations with locking tables in 31# triggers, a specifc lockingof the tables is done. 32# Once fixed, the locking and alias referances should be removed 33 34use test; 35# Trigger Definition 36 Create trigger trg1_1 BEFORE INSERT 37 on tb3 for each row set @test_before = 2, new.f142 = @test_before; 38 Create trigger trg1_2 AFTER INSERT 39 on tb3 for each row set @test_after = 6; 40 Create trigger trg1_4 BEFORE UPDATE 41 on tb3 for each row set @test_before = 27, 42 new.f142 = @test_before, 43 new.f122 = 'Before Update Trigger'; 44 Create trigger trg1_3 AFTER UPDATE 45 on tb3 for each row set @test_after = '15'; 46 Create trigger trg1_5 BEFORE DELETE on tb3 for each row 47 select count(*) into @test_before from tb3 as tr_tb3 48 where f121 = 'Test 3.5.1.1'; 49 Create trigger trg1_6 AFTER DELETE on tb3 for each row 50 select count(*) into @test_after from tb3 as tr_tb3 51 where f121 = 'Test 3.5.1.1'; 52# Trigger Execution Insert (before and after) 53 set @test_before = 1; 54 set @test_after = 5; 55 select @test_before, @test_after; 56 Insert into tb3 (f121, f122, f142, f144, f134) 57 values ('Test 3.5.1.1', 'First Row', @test_before, @test_after, 1); 58 --sorted_result 59 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1'; 60 select @test_before, @test_after; 61 62# Trigger Execution Update (before and after) 63 set @test_before = 18; 64 set @test_after = 8; 65 select @test_before, @test_after; 66 Update tb3 set tb3.f122 = 'Update', 67 tb3.f142 = @test_before, 68 tb3.f144 = @test_after 69 where tb3.f121 = 'Test 3.5.1.1'; 70 --sorted_result 71 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1'; 72 select @test_before, @test_after; 73 74# Trigger Execution Delete (before and after) 75 Insert into tb3 (f121, f122, f142, f144, f134) 76 values ('Test 3.5.1.1', 'Second Row', 5, 6, 2); 77 set @test_before = 0; 78 set @test_after = 0; 79 --sorted_result 80 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1'; 81 select @test_before, @test_after; 82 Delete from tb3 where f121 = 'Test 3.5.1.1' and f134 = 2; 83 --sorted_result 84 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1'; 85 select @test_before, @test_after; 86 87#Cleanup 88 --disable_warnings 89 --error 0, ER_TRG_DOES_NOT_EXIST 90 drop trigger trg1_1; 91 --error 0, ER_TRG_DOES_NOT_EXIST 92 drop trigger trg1_2; 93 --error 0, ER_TRG_DOES_NOT_EXIST 94 drop trigger trg1_3; 95 --error 0, ER_TRG_DOES_NOT_EXIST 96 drop trigger trg1_4; 97 --error 0, ER_TRG_DOES_NOT_EXIST 98 drop trigger trg1_5; 99 --error 0, ER_TRG_DOES_NOT_EXIST 100 drop trigger trg1_6; 101 --enable_warnings 102 delete from tb3 where f121='Test 3.5.1.1'; 103 --enable_warnings 104 105#Section 3.5.1.2 106# Testcase: Ensure that all clauses that should not be supported are disallowed 107# with an appropriate error message. 108let $message= Testcase: 3.5.1.2:; 109--source include/show_msg.inc 110 111 --error ER_PARSE_ERROR 112 Create trigger trg_1 after insert 113 on tb3 for each statement set @x= 1; 114 115#Cleanup 116 --disable_warnings 117 --error 0, ER_TRG_DOES_NOT_EXIST 118 drop trigger trg_1; 119 --enable_warnings 120 121 122#Section 3.5.1.3 123# Testcase: Ensure that all supported clauses are supported only in the correct order. 124let $message= Testcase 3.5.1.3:; 125--source include/show_msg.inc 126 --error ER_PARSE_ERROR 127 CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't'; 128 129 --error ER_PARSE_ERROR 130 CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's'; 131 132 --error ER_PARSE_ERROR 133 CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row; 134 135 --error ER_PARSE_ERROR 136 CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row; 137 138 --error ER_PARSE_ERROR 139 CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test'; 140 141#Cleanup 142# OBN - Although none of the above should have been created we should do a cleanup 143# since if they have been created, not dropping them will affect following 144# tests. 145 --disable_warnings 146 --error 0, ER_TRG_DOES_NOT_EXIST 147 drop trigger trg3_1; 148 --error 0, ER_TRG_DOES_NOT_EXIST 149 drop trigger trg3_2; 150 --error 0, ER_TRG_DOES_NOT_EXIST 151 drop trigger trg3_3; 152 --error 0, ER_TRG_DOES_NOT_EXIST 153 drop trigger trg3_4; 154 --error 0, ER_TRG_DOES_NOT_EXIST 155 drop trigger trg3_5; 156 --enable_warnings 157 158 159#Section 3.5.1.4 160# Testcase: Ensure that an appropriate error message is returned if a clause 161# is out-of-order in an SQL statement. 162# OBN - FIXME - Missing 3.5.1.4 need to add 163 164#Section 3.5.1.5 165# Testcase: Ensure that all clauses that are defined to be mandatory are indeed 166# required to be mandatory by the MySQL server and tools 167let $message= Testcase: 3.5.1.5:; 168--source include/show_msg.inc 169 170 --error ER_PARSE_ERROR 171 CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e'; 172 173 --error ER_PARSE_ERROR 174 CREATE TRIGGER trg4_2 INSERT on tb3 for each set row new.f120 = 'f'; 175 176 --error ER_PARSE_ERROR 177 CREATE TRIGGER trg4_3 BEFORE INSERT tb3 for each row set new.f120 = 'g'; 178 179 --error ER_PARSE_ERROR 180 CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g'; 181 182 --error ER_PARSE_ERROR 183 CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g'; 184 185 --error ER_PARSE_ERROR 186 CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g'; 187 188#Cleanup 189# OBN - Although none of the above should have been created we should do a cleanup 190# since if they have been created, not dropping them will affect following 191# tests. 192 --disable_warnings 193 --error 0, ER_TRG_DOES_NOT_EXIST 194 drop trigger trg4_1; 195 --error 0, ER_TRG_DOES_NOT_EXIST 196 drop trigger trg4_2; 197 --error 0, ER_TRG_DOES_NOT_EXIST 198 drop trigger trg4_3; 199 --error 0, ER_TRG_DOES_NOT_EXIST 200 drop trigger trg4_4; 201 --error 0, ER_TRG_DOES_NOT_EXIST 202 drop trigger trg4_5; 203 --error 0, ER_TRG_DOES_NOT_EXIST 204 drop trigger trg4_6; 205 --enable_warnings 206 207#Section 3.5.1.6 208# Testcase: Ensure that any clauses that are defined to be optional are indeed 209# trated as optional by MySQL server and tools 210let $message= Testcase 3.5.1.6: - Need to fix; 211--source include/show_msg.inc 212# OBN - FIXME - Missing 3.5.1.6 need to add 213 214#Section 3.5.1.7 215# Testcase: Ensure that all valid, fully-qualified, and non-qualified, 216# trigger names are accepted, at creation time. 217let $message= Testcase 3.5.1.7: - need to fix; 218--source include/show_msg.inc 219 220 drop table if exists t1; 221 --replace_result $engine_type <engine_to_be_used> 222 eval create table t1 (f1 int, f2 char(25),f3 int) engine = $engine_type; 223 CREATE TRIGGER trg5_1 BEFORE INSERT on test.t1 224 for each row set new.f3 = '14'; 225# In 5.0 names to long (more than 64 chars) were trimed without an error 226# In 5.1 an error is returned. So adding a call with the expected error 227# and one with a shorter name to validate proper execution 228 --error ER_TOO_LONG_IDENT 229 CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ 230 BEFORE UPDATE on test.t1 for each row set new.f3 = '42'; 231 CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX 232 BEFORE UPDATE on test.t1 for each row set new.f3 = '42'; 233 234 insert into t1 (f2) values ('insert 3.5.1.7'); 235 select * from t1; 236 update t1 set f2='update 3.5.1.7'; 237 select * from t1; 238 select trigger_name from information_schema.triggers order by trigger_name; 239 240#Cleanup 241 --disable_warnings 242 --error 0, ER_TRG_DOES_NOT_EXIST 243 drop trigger trg5_1; 244 # In 5.1 the long name should generate an error that is to long 245 --error ER_TOO_LONG_IDENT 246 drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ; 247 drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX; 248 drop table t1; 249 250#Section 3.5.1.8 251# Testcase: Ensure that any invalid trigger name is never accepted, and that an 252# appropriate error message is returned when the name is rejected. 253let $message= Testcase 3.5.1.8:; 254--source include/show_msg.inc 255 256 --error ER_PARSE_ERROR 257 CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't'; 258 259 --error ER_PARSE_ERROR 260 CREATE TRIGGER trigger before insert on tb3 for each row set new.f120 = 't'; 261 262 --error ER_PARSE_ERROR 263 CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't'; 264 265 --error ER_PARSE_ERROR 266 CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't'; 267 268 --error ER_PARSE_ERROR 269 CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't'; 270 271 --error ER_TRG_IN_WRONG_SCHEMA 272 CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3 273 for each row set new.f120 ='X'; 274 275 --disable_warnings 276 drop database if exists trig_db; 277 --enable_warnings 278 create database trig_db; 279 use trig_db; 280 --replace_result $engine_type <engine_to_be_used> 281 eval create table t1 (f1 integer) engine = $engine_type; 282 283 # Can't create a trigger in a different database 284 use test; 285 --error ER_NO_SUCH_TABLE 286 CREATE TRIGGER trig_db.trg6_2 AFTER INSERT on tb3 287 for each row set @ret_trg6_2 = 5; 288 289 # Can't create a trigger refrencing a table in a different db 290 use trig_db; 291 --error ER_TRG_IN_WRONG_SCHEMA 292 CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3 293 for each row set @ret_trg6_3 = 18; 294 295 use test; 296 297#Cleanup 298 --disable_warnings 299 drop database trig_db; 300# OBN - Although none of the above should have been created we should do a cleanup 301# since if they have been created, not dropping them will affect following 302# tests. 303 --error 0, ER_TRG_DOES_NOT_EXIST 304 drop trigger trg6_1; 305 --error 0, ER_TRG_DOES_NOT_EXIST 306 drop trigger trg6_3; 307 --enable_warnings 308 309#Section 3.5.1.9 310#Testcase: Ensure that a reference to a non-existent trigger is rejected with 311# an appropriate error message. 312let $message= Testcase 3.5.1.9:(cannot be inplemented at this point); 313--source include/show_msg.inc 314 315 316#Section 3.5.1.10 317#Testcase: Ensure that it is not possible to create two triggers with the same name on 318# the same table 319let $message= Testcase 3.5.1.10:; 320--source include/show_msg.inc 321 322 CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X'; 323 324 --error ER_TRG_ALREADY_EXISTS 325 CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y'; 326 327#Cleanup 328 --disable_warnings 329 --error 0, ER_TRG_DOES_NOT_EXIST 330 drop trigger trg7_1; 331 --enable_warnings 332 333 334#Section 3.5.1.? 335# Testcase: Ensure that it is not possible to create two or more triggers with 336# the same name, provided each is associated with a different table. 337let $message= Testcase 3.5.1.?:; 338--source include/show_msg.inc 339 340 --disable_warnings 341 drop table if exists t1; 342 drop table if exists t2; 343 --enable_warnings 344 --replace_result $engine_type <engine_to_be_used> 345 eval create table t1 (f1 char(50), f2 integer) engine = $engine_type; 346 --replace_result $engine_type <engine_to_be_used> 347 eval create table t2 (f1 char(50), f2 integer) engine = $engine_type; 348 349 create trigger trig before insert on t1 350 for each row set new.f1 ='trig t1'; 351 352 --error ER_TRG_ALREADY_EXISTS 353 create trigger trig before update on t2 354 for each row set new.f1 ='trig t2'; 355 356 insert into t1 value ('insert to t1',1); 357 select * from t1; 358 update t1 set f1='update to t1'; 359 select * from t1; 360 insert into t2 value ('insert to t2',2); 361 update t2 set f1='update to t1'; 362 select * from t2; 363 364#Cleanup 365 --disable_warnings 366 drop table t1; 367 drop table t2; 368 --error 0, ER_TRG_DOES_NOT_EXIST 369 drop trigger trig; 370 --enable_warnings 371 372 373#Section 3.5.1.11 374# Testcase: Ensure that it is possible to create two or more triggers with 375# the same name, provided each resides in a different database 376let $message= Testcase 3.5.1.11:; 377--source include/show_msg.inc 378 379 --disable_warnings 380 drop database if exists trig_db1; 381 drop database if exists trig_db2; 382 drop database if exists trig_db3; 383 --enable_warnings 384 create database trig_db1; 385 create database trig_db2; 386 create database trig_db3; 387 use trig_db1; 388 --replace_result $engine_type <engine_to_be_used> 389 eval create table t1 (f1 char(50), f2 integer) engine = $engine_type; 390 create trigger trig before insert on t1 391 for each row set new.f1 ='trig1', @test_var1='trig1'; 392 use trig_db2; 393 --replace_result $engine_type <engine_to_be_used> 394 eval create table t2 (f1 char(50), f2 integer) engine = $engine_type; 395 create trigger trig before insert on t2 396 for each row set new.f1 ='trig2', @test_var2='trig2'; 397 use trig_db3; 398 --replace_result $engine_type <engine_to_be_used> 399 eval create table t1 (f1 char(50), f2 integer) engine = $engine_type; 400 create trigger trig before insert on t1 401 for each row set new.f1 ='trig3', @test_var3='trig3'; 402 403 set @test_var1= '', @test_var2= '', @test_var3= ''; 404 use trig_db1; 405 insert into t1 (f1,f2) values ('insert to db1 t1',1); 406 insert into trig_db1.t1 (f1,f2) values ('insert to db1 t1 from db1',2); 407 insert into trig_db2.t2 (f1,f2) values ('insert to db2 t2 from db1',3); 408 insert into trig_db3.t1 (f1,f2) values ('insert to db3 t1 from db1',4); 409 select @test_var1, @test_var2, @test_var3; 410 select * from t1 order by f2; 411 select * from trig_db2.t2; 412 select * from trig_db3.t1; 413 select * from t1 order by f2; 414 use test; 415 416#Cleanup 417 --disable_warnings 418 drop database trig_db1; 419 drop database trig_db2; 420 drop database trig_db3; 421 --enable_warnings 422 423########################################### 424################ Section 3.5.2 ############ 425# Check for the global nature of Triggers # 426########################################### 427 428#Section 3.5.2.1 429# Test case: Ensure that if a trigger created without a qualifying database 430# name belongs to the database in use at creation time. 431#Section 3.5.2.2 432# Test case: Ensure that if a trigger created with a qualifying database name 433# belongs to the database specified. 434#Section 3.5.2.3 435# Test case: Ensure that if a trigger created with a qualifying database name 436# does not belong to the database in use at creation time unless 437# the qualifying database name identifies the database that is 438# also in use at creation time. 439let $message= Testcase 3.5.2.1/2/3:; 440--source include/show_msg.inc 441 442 443 --disable_warnings 444 drop database if exists trig_db1; 445 drop database if exists trig_db2; 446 --enable_warnings 447 create database trig_db1; 448 create database trig_db2; 449 use trig_db1; 450 --replace_result $engine_type <engine_to_be_used> 451 eval create table t1 (f1 char(50), f2 integer) engine = $engine_type; 452 --replace_result $engine_type <engine_to_be_used> 453 eval create table trig_db2.t1 (f1 char(50), f2 integer) engine = $engine_type; 454 create trigger trig1_b before insert on t1 455 for each row set @test_var1='trig1_b'; 456 create trigger trig_db1.trig1_a after insert on t1 457 for each row set @test_var2='trig1_a'; 458 create trigger trig_db2.trig2 before insert on trig_db2.t1 459 for each row set @test_var3='trig2'; 460 select trigger_schema, trigger_name, event_object_table 461 from information_schema.triggers 462 where trigger_schema like 'trig_db%' 463 order by trigger_name; 464 465 set @test_var1= '', @test_var2= '', @test_var3= ''; 466 insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352); 467 insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352); 468 select @test_var1, @test_var2, @test_var3; 469 470#Cleanup 471 --disable_warnings 472 drop database trig_db1; 473 drop database trig_db2; 474DROP TABLE test.tb3; 475