Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_0407.inc

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