1
#======================================================================
4
# (test case numbering refer to requirement document TP v1.1)
5
#======================================================================
8
--source suite/funcs_1/include/tb3.inc
10
--disable_abort_on_error
12
# General setup for Trigger tests
13
let $message= Testcase: 3.5:;
14
--source include/show_msg.inc
16
--disable_abort_on_error
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;
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
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
26
connect (con1_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
27
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
28
connect (con1_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
31
####################################
32
############ Section 3.5.4 #########
33
# Drop Trigger Checkes: #
34
####################################
35
let $message= Testcase 3.5.4:;
36
--source include/show_msg.inc
42
# Testcase: Ensure that the DROP TRIGGER statement cleanly drops its target trigger.
43
let $message= Testcase 3.5.4.1:;
44
--source include/show_msg.inc
46
connection con1_super;
47
create database db_drop;
49
--replace_result $engine_type <engine_to_be_used>
50
eval create table t1 (f1 char(30)) engine = $engine_type;
51
grant INSERT, SELECT on db_drop.t1 to test_general;
53
Create trigger trg1 BEFORE INSERT on t1
54
for each row set new.f1='Trigger 3.5.4.1';
55
connection con1_general;
57
Insert into t1 values ('Insert error 3.5.4.1');
58
Select * from t1 order by f1;
59
connection con1_super;
61
select trigger_schema, trigger_name, event_object_table
62
from information_schema.triggers
63
where trigger_schema = 'db_drop'
64
order by trigger_name;
65
connection con1_general;
66
Insert into t1 values ('Insert no trigger 3.5.4.1');
67
Select * from t1 order by f1;
71
connection con1_super;
73
--error 0,ER_TRG_DOES_NOT_EXIST
75
drop database if exists db_drop;
76
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
80
# Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate error
81
# message, if the trigger name does not exist.
82
let $message= Testcase 3.5.4.2:;
83
--source include/show_msg.inc
85
connection con1_super;
86
create database db_drop2;
89
drop table if exists t1_432 ;
91
--replace_result $engine_type <engine_to_be_used>
92
eval create table t1_432 (f1 char (30)) engine = $engine_type;
93
--error ER_TRG_DOES_NOT_EXIST
94
Drop trigger tr_does_not_exit;
97
drop table if exists t1_432 ;
98
drop database if exists db_drop2;
102
# Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate
103
# error message, if <trigger name> is not a qualified name.
104
let $message= Testcase 3.5.4.3:;
105
--source include/show_msg.inc
107
connection con1_super;
108
create database db_drop3;
111
drop table if exists t1_433 ;
112
drop table if exists t1_433a ;
114
--replace_result $engine_type <engine_to_be_used>
115
eval create table t1_433 (f1 char (30)) engine = $engine_type;
116
--replace_result $engine_type <engine_to_be_used>
117
eval create table t1_433a (f1a char (5)) engine = $engine_type;
119
CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row
120
set new.f1 = 'Trigger 3.5.4.3';
123
--error ER_PARSE_ERROR
124
Drop trigger t1.433.trg3;
126
# Using database.table
127
--error ER_PARSE_ERROR
128
Drop trigger db_drop3.t1.433.trg3;
131
--error ER_TRG_DOES_NOT_EXIST
132
Drop trigger mysql.trg3;
134
# database does not exist
135
--error ER_TRG_DOES_NOT_EXIST
136
Drop trigger tbx.trg3;
139
Drop trigger db_drop3.trg3;
140
drop table if exists t1_433;
141
drop table if exists t1_433a;
142
drop database if exists db_drop3;
145
# Test case: Ensure that when a database is dropped, all triggers created within
146
# that database are also cleanly dropped.
147
let $message= Testcase 3.5.4.4:;
148
--source include/show_msg.inc
150
connection con1_super;
151
create database db_drop4;
153
--replace_result $engine_type <engine_to_be_used>
154
eval create table t1 (f1 char(30)) engine = $engine_type;
155
grant INSERT, SELECT on db_drop4.t1 to test_general;
156
Create trigger trg4 BEFORE INSERT on t1
157
for each row set new.f1='Trigger 3.5.4.4';
158
connection con1_general;
160
Insert into t1 values ('Insert 3.5.4.4');
162
connection con1_super;
163
Drop database db_drop4;
164
Show databases like 'db_drop4';
165
select trigger_schema, trigger_name, event_object_table
166
from information_schema.triggers
167
where information_schema.triggers.trigger_name='trg4';
168
create database db_drop4;
170
--replace_result $engine_type <engine_to_be_used>
171
eval create table t1 (f1 char(30)) engine = $engine_type;
172
grant INSERT, SELECT on db_drop4.t1 to test_general;
173
connection con1_general;
174
Insert into t1 values ('2nd Insert 3.5.4.4');
178
connection con1_super;
180
--error ER_TRG_DOES_NOT_EXIST
182
drop database if exists db_drop4;
184
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
187
# Test case: Ensure that when a table is dropped, all triggers for which it is the
188
# subject table are also cleanly dropped.
189
let $message= Testcase 3.5.4.5:;
190
--source include/show_msg.inc
192
connection con1_super;
193
create database db_drop5;
195
--replace_result $engine_type <engine_to_be_used>
196
eval create table t1 (f1 char(50)) engine = $engine_type;
197
grant INSERT, SELECT on t1 to test_general;
198
Create trigger trg5 BEFORE INSERT on t1
199
for each row set new.f1='Trigger 3.5.4.5';
200
connection con1_general;
202
Insert into t1 values ('Insert 3.5.4.5');
204
connection con1_super;
207
select trigger_schema, trigger_name, event_object_table
208
from information_schema.triggers
209
where information_schema.triggers.trigger_name='trg5';
210
--replace_result $engine_type <engine_to_be_used>
211
eval create table t1 (f1 char(50)) engine = $engine_type;
212
grant INSERT, SELECT on t1 to test_general;
213
connection con1_general;
214
Insert into t1 values ('2nd Insert 3.5.4.5');
218
connection con1_super;
220
--error ER_TRG_DOES_NOT_EXIST
222
drop database if exists db_drop5;
224
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
227
##################################
228
######### Section 3.5.5 ##########
229
# Checks on the Subject Table #
230
##################################
232
let $message= Testcase 3.5.5:;
233
--source include/show_msg.inc
239
# Test case: Ensure that, if CREATE TRIGGER is executed with a non-existent
240
# subject table, the statement fails with an appropriate error message.
241
let $message= Testcase 3.5.5.1:;
242
--source include/show_msg.inc
244
--error ER_NO_SUCH_TABLE
245
Create trigger trg1 before INSERT on t100 for each row set new.f2=1000;
249
# Test case: Ensure that, if CREATE TRIGGER is executed with a temporary table
250
# as the subject table, the statement fails with an appropriate error message.
251
let $message= Testcase 3.5.5.2:;
252
--source include/show_msg.inc
254
Create temporary table t1_temp (f1 bigint signed, f2 bigint unsigned);
256
--error ER_TRG_ON_VIEW_OR_TEMP_TABLE
257
Create trigger trg2 before INSERT
258
on t1_temp for each row set new.f2=9999;
267
# Test case: Ensure that, if CREATE TRIGGER is executed with a view as the subject
268
# table, the statement fails with an appropriate error message.
269
let $message= Testcase 3.5.5.3:;
270
--source include/show_msg.inc
272
Create view vw3 as select f118 from tb3;
274
# OBN Not sure why the server is returning error ER_WRONG_OBJECT
275
--error ER_WRONG_OBJECT
276
Create trigger trg3 before INSERT
277
on vw3 for each row set new.f118='s';
286
# Test case: Ensure that, if CREATE TRIGGER is executed with a table that resides
287
# in a different database than in which the trigger will reside, the
288
# statement fails with an appropriate error message; that is, ensure that
289
# the trigger and its subject table must reside in the same database.
290
let $message= Testcase 3.5.5.4:;
291
--source include/show_msg.inc
293
connection con1_super;
294
create database dbtest_one;
295
create database dbtest_two;
297
--replace_result $engine_type <engine_to_be_used>
298
eval create table t2 (f1 char(15)) engine = $engine_type;
300
--error ER_TRG_IN_WRONG_SCHEMA
301
create trigger trg4 before INSERT
302
on dbtest_two.t2 for each row set new.f1='trig 3.5.5.4';
303
grant INSERT, SELECT on dbtest_two.t2 to test_general;
304
grant SELECT on dbtest_one.* to test_general;
305
connection con1_general;
307
Insert into t2 values ('1st Insert 3.5.5.4');
310
Insert into dbtest_two.t2 values ('2nd Insert 3.5.5.4');
311
Select * from dbtest_two.t2 order by f1;
314
connection con1_super;
316
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
317
DROP DATABASE if exists dbtest_one;
318
drop database if EXISTS dbtest_two;
321
#####################################
322
########### Section 3.5.6 ###########
323
# Check on the Trigger Action Time #
324
#####################################
326
let $message= Testcase 3.5.6:;
327
--source include/show_msg.inc
333
# Test case: Ensure that a trigger definition can specify a trigger action time of BEFORE.
334
# See section 3.5.1.1
335
let $message= Testcase 3.5.6.1 (see Testcase 3.5.1.1);
336
--source include/show_msg.inc
339
# Test case: Ensure that a trigger definition can specify a trigger action time of AFTER.
340
# See section 3.5.1.1
341
let $message= Testcase 3.5.6.2 (see Testcase 3.5.1.1);
342
--source include/show_msg.inc
345
# Test case: Ensure that a trigger definition that specifies a trigger action
346
# time that is not either BEFORE or AFTER fails, with an appropriate
347
# error message, at CREATE TRIGGER time.
348
let $message= Testcase 3.5.6.3:;
349
--source include/show_msg.inc
351
--error ER_PARSE_ERROR
352
Create trigger trg3_1 DURING UPDATE on tb3 for each row set new.f132=25;
353
--error ER_PARSE_ERROR
354
Create trigger trg3_2 TIME INSERT on tb3 for each row set new.f132=15;
357
# OBN - Although none of the above should have been created we should do a cleanup
358
# since if they have been created, not dropping them will affect following
361
--error 0, ER_TRG_DOES_NOT_EXIST
362
drop trigger tb3.trg3_1;
363
--error 0, ER_TRG_DOES_NOT_EXIST
364
drop trigger tb3.trg3_2;
368
# Test case: Ensure that a trigger defined with a trigger action time of BEFORE
369
# always executes its triggered action immediately before the trigger event.
370
# See section 3.5.1.1
371
let $message= Testcase 3.5.6.4 (see Testcase 3.5.1.1);
372
--source include/show_msg.inc
375
# Test case: Ensure that a trigger defined with a trigger action time of AFTER
376
# always executes its triggered action immediately after the trigger event.
377
let $message= Testcase 3.5.6.5 (see Testcase 3.5.1.1);
378
--source include/show_msg.inc
380
#############################
381
####### Section 3.5.7 #######
382
# Check on Trigger Event #
383
#############################
386
#Test case: Ensure that a trigger definition can specify a trigger event of INSERT.
387
let $message= Testcase 3.5.7.1 (see Testcase 3.5.1.1);
388
--source include/show_msg.inc
391
# Test case: Ensure that a trigger definition can specify a trigger event of UPDATE.
392
let $message= Testcase 3.5.7.2 (see Testcase 3.5.1.1);
393
--source include/show_msg.inc
396
# Test case: Ensure that a trigger definition can specify a trigger event of DELETE.
397
let $message= Testcase 3.5.7.3 (see Testcase 3.5.1.1);
398
--source include/show_msg.inc
401
# Test case: Ensure that a trigger definition that specifies a trigger event that
402
# is not either INSERT, UPDATE or DELETE fails, with an appropriate error
403
# message, at CREATE TRIGGER time.
404
let $message= Testcase 3.5.7.4:;
405
--source include/show_msg.inc
407
--error ER_PARSE_ERROR
408
Create trigger trg4_1 BEFORE SELECT on tb3 for each row set new.f132=5;
409
--error ER_PARSE_ERROR
410
Create trigger trg4_2 AFTER VALUE on tb3 for each row set new.f132=1;
413
# OBN - Although none of the above should have been created we should do a cleanup
414
# since if they have been created, not dropping them will affect following
417
--error 0, ER_TRG_DOES_NOT_EXIST
418
drop trigger tb3.trg4_1;
419
--error 0, ER_TRG_DOES_NOT_EXIST
420
drop trigger tb3.trg4_2;
423
#Section 3.5.7.5 / 3.5.7.6
424
# Test case: Ensure that it is not possible to create multiple BEFORE INSERT triggers
425
# on the same table, even if the triggers have different names / different
427
let $message= Testcase 3.5.7.5 / 3.5.7.6:;
428
--source include/show_msg.inc
430
Create trigger trg5_1 BEFORE INSERT
431
on tb3 for each row set new.f122='Trigger1 3.5.7.5/6';
433
--error ER_NOT_SUPPORTED_YET
434
Create trigger trg5_2 BEFORE INSERT
435
on tb3 for each row set new.f122='Trigger2 3.5.7.5';
437
Insert into tb3 (f121,f122) values ('Test 3.5.7.5/6','Insert 3.5.7.5');
438
Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
439
update tb3 set f122='Update 3.5.7.6' where f121= 'Test 3.5.7.5/6';
440
Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
445
--error 0, ER_TRG_DOES_NOT_EXIST
447
delete from tb3 where f121='Test 3.5.7.5/6';
451
#Section 3.5.7.7 / 3.5.7.8
452
# Test case: Ensure that it is not possible to create multiple AFTER INSERT triggers
453
# on the same table, even if the triggers have different names / different
455
let $message= Testcase 3.5.7.7 / 3.5.7.8:;
456
--source include/show_msg.inc
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';
462
--error ER_NOT_SUPPORTED_YET
463
Create trigger trg6_2 AFTER INSERT
464
on tb3 for each row set @test_var='Trigger2 3.5.7.7';
467
Insert into tb3 (f121,f122) values ('Test 3.5.7.7/8','Insert 3.5.7.7');
468
Select f121,f122 from tb3 where f121='Test 3.5.7.7/8';
470
update tb3 set f122='Update 3.5.7.8' where f121= 'Test 3.5.7.7/8';
471
Select f121,f122 from tb3 where f121='Test 3.5.7.7/8';
477
--error 0, ER_TRG_DOES_NOT_EXIST
479
delete from tb3 where f121='Test 3.5.7.7/8';
483
#Section 3.5.7.9 / 3.5.7.10
484
# Test case: Ensure that it is not possible to create multiple BEFORE UPDATE triggers
485
# on the same table, even if the triggers have different names / different
487
let $message= Testcase 3.5.7.9/10:;
488
--source include/show_msg.inc
490
Create trigger trg7_1 BEFORE UPDATE
491
on tb3 for each row set new.f122='Trigger1 3.5.7.9/10';
493
--error ER_NOT_SUPPORTED_YET
494
Create trigger trg7_2 BEFORE UPDATE
495
on tb3 for each row set new.f122='Trigger2 3.5.7.9';
497
Insert into tb3 (f121,f122) values ('Test 3.5.7.9/10','Insert 3.5.7.9');
498
Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
499
update tb3 set f122='update 3.5.7.10' where f121='Test 3.5.7.9/10';
500
Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
505
--error 0, ER_TRG_DOES_NOT_EXIST
507
delete from tb3 where f121='Test 3.5.7.9/10';
509
#Section 3.5.7.11 / 3.5.7.12
510
# Test case: Ensure that it is not possible to create multiple AFTER UPDATE triggers
511
# on the same table, even if the triggers have different names / different
513
let $message= Testcase 3.5.7.11/12:;
514
--source include/show_msg.inc
516
set @test_var='Before trig 3.5.7.11';
517
Create trigger trg8_1 AFTER UPDATE
518
on tb3 for each row set @test_var='Trigger 3.5.7.11/12';
520
--error ER_NOT_SUPPORTED_YET
521
Create trigger trg8_2 AFTER UPDATE
522
on tb3 for each row set @test_var='Trigger2 3.5.7.11';
526
Insert into tb3 (f121,f122) values ('Test 3.5.7.11/12','Insert 3.5.7.11/12');
528
Select f121,f122 from tb3 where f121='Test 3.5.7.11/12';
529
update tb3 set f122='update 3.5.7.12' where f121='Test 3.5.7.11/12';
530
Select f121,f122 from tb3 where f121='Test 3.5.7.11/12';
532
delete from tb3 where f121='Test 3.5.7.11/12';
537
--error 0, ER_TRG_DOES_NOT_EXIST
539
delete from tb3 where f121='Test 3.5.7.11/12';
541
#Section 3.5.7.13 / 3.5.7.14
542
# Test case: Ensure that it is not possible to create multiple BEFORE DELETE triggers
543
# on the same table, even if the triggers have different names / different
545
let $message= Testcase 3.5.7.13/14:;
546
--source include/show_msg.inc
549
Create trigger trg9_1 BEFORE DELETE
550
on tb3 for each row set @test_var=@test_var+1;
552
--error ER_NOT_SUPPORTED_YET
553
Create trigger trg9_2 BEFORE DELETE
554
on tb3 for each row set @test_var=@test_var+10;
557
Insert into tb3 (f121,f122) values ('Test 3.5.7.13/14','Insert 3.5.7.13');
558
Select f121,f122 from tb3 where f121='Test 3.5.7.13/14';
560
delete from tb3 where f121='Test 3.5.7.13/14';
561
Select f121,f122 from tb3 where f121='Test 3.5.7.13/14';
563
delete from tb3 where f121='Test 3.5.7.13/14';
569
--error 0, ER_TRG_DOES_NOT_EXIST
571
delete from tb3 where f121='Test 3.5.7.13/14';
573
#Section 3.5.7.15 / 3.5.7.16
574
# Test case: Ensure that it is not possible to create multiple AFTER DELETE triggers
575
# on the same table, even if the triggers have different names / different
577
let $message= Testcase 3.5.7.15/16:;
578
--source include/show_msg.inc
581
Create trigger trg_3_406010_1 AFTER DELETE
582
on tb3 for each row set @test_var=@test_var+5;
584
--error ER_NOT_SUPPORTED_YET
585
Create trigger trg_3_406010_2 AFTER DELETE
586
on tb3 for each row set @test_var=@test_var+50;
588
--error ER_TRG_ALREADY_EXISTS
589
Create trigger trg_3_406010_1 AFTER INSERT
590
on tb3 for each row set @test_var=@test_var+1;
593
Insert into tb3 (f121,f122) values ('Test 3.5.7.15/16','Insert 3.5.7.15/16');
594
Select f121,f122 from tb3 where f121='Test 3.5.7.15/16';
596
delete from tb3 where f121='Test 3.5.7.15/16';
597
Select f121,f122 from tb3 where f121='Test 3.5.7.15/16';
599
delete from tb3 where f121='Test 3.5.7.15/16';
604
drop trigger trg_3_406010_1;
605
--error 0, ER_TRG_DOES_NOT_EXIST
606
drop trigger trg_3_406010_2;
607
delete from tb3 where f121='Test 3.5.7.15/16';
612
# Test case: Ensure that it is possible to have a BEFORE INSERT, an AFTER INSERT,
613
# a BEFORE UPDATE, an AFTER UPDATE, a BEFORE DELETE, and an AFTER DELETE
614
# trigger on the same table; that is, ensure that every persistent base
615
# table may be the subject table for exactly six triggers
616
let $message= Testcase 3.5.7.17 (see Testcase 3.5.1.1);
617
--source include/show_msg.inc
620
# Cleanup section 3.5
622
drop user test_general@localhost;
623
drop user test_general;
624
drop user test_super@localhost;