1
1
#======================================================================
4
# (test case numbering refer to requirement document TP v1.1)
4
# (test case numbering refer to requirement document TP v1.1)
5
5
#======================================================================
8
--source suite/funcs_1/include/tb3.inc
7
10
--disable_abort_on_error
9
12
# General setup for Trigger tests
43
46
connection con1_super;
44
47
create database db_drop;
46
eval create table t1 (f1 char(30)) engine=$engine_type;
49
--replace_result $engine_type <engine_to_be_used>
50
eval create table t1 (f1 char(30)) engine = $engine_type;
47
51
grant INSERT, SELECT on db_drop.t1 to test_general;
49
Create trigger trg1 BEFORE INSERT on t1
53
Create trigger trg1 BEFORE INSERT on t1
50
54
for each row set new.f1='Trigger 3.5.4.1';
51
55
connection con1_general;
61
65
Select * from t1 order by f1;
65
69
connection con1_super;
71
--error 0,ER_TRG_DOES_NOT_EXIST
69
73
drop database if exists db_drop;
70
74
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
74
# Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate error
78
# Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate error
75
79
# message, if the trigger name does not exist.
76
80
let $message= Testcase 3.5.4.2:;
77
81
--source include/show_msg.inc
83
87
drop table if exists t1_432 ;
85
eval create table t1_432 (f1 char (30)) engine=$engine_type;
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
87
92
Drop trigger tr_does_not_exit;
90
95
drop table if exists t1_432 ;
91
96
drop database if exists db_drop2;
95
# Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate
100
# Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate
96
101
# error message, if <trigger name> is not a qualified name.
97
102
let $message= Testcase 3.5.4.3:;
98
103
--source include/show_msg.inc
104
109
drop table if exists t1_433 ;
105
110
drop table if exists t1_433a ;
106
111
--enable_warnings
107
eval create table t1_433 (f1 char (30)) engine=$engine_type;
108
eval create table t1_433a (f1a char (5)) engine=$engine_type;
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;
110
CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row
117
CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row
111
118
set new.f1 = 'Trigger 3.5.4.3';
121
--error ER_PARSE_ERROR
115
122
Drop trigger t1.433.trg3;
117
# Using database.table
124
# Using database.table
125
--error ER_PARSE_ERROR
119
126
Drop trigger db_drop3.t1.433.trg3;
129
--error ER_TRG_DOES_NOT_EXIST
123
130
Drop trigger mysql.trg3;
125
132
# database does not exist
133
--error ER_TRG_DOES_NOT_EXIST
127
134
Drop trigger tbx.trg3;
130
137
Drop trigger db_drop3.trg3;
131
138
drop table if exists t1_433;
132
139
drop table if exists t1_433a;
133
140
drop database if exists db_drop3;
136
# Test case: Ensure that when a database is dropped, all triggers created within
143
# Test case: Ensure that when a database is dropped, all triggers created within
137
144
# that database are also cleanly dropped.
138
145
let $message= Testcase 3.5.4.4:;
139
146
--source include/show_msg.inc
141
148
connection con1_super;
142
149
create database db_drop4;
144
eval create table t1 (f1 char(30)) engine=$engine_type;
151
--replace_result $engine_type <engine_to_be_used>
152
eval create table t1 (f1 char(30)) engine = $engine_type;
145
153
grant INSERT, SELECT on db_drop4.t1 to test_general;
146
Create trigger trg4 BEFORE INSERT on t1
154
Create trigger trg4 BEFORE INSERT on t1
147
155
for each row set new.f1='Trigger 3.5.4.4';
148
156
connection con1_general;
157
165
where information_schema.triggers.trigger_name='trg4';
158
166
create database db_drop4;
160
eval create table t1 (f1 char(30)) engine=$engine_type;
168
--replace_result $engine_type <engine_to_be_used>
169
eval create table t1 (f1 char(30)) engine = $engine_type;
161
170
grant INSERT, SELECT on db_drop4.t1 to test_general;
162
171
connection con1_general;
163
172
Insert into t1 values ('2nd Insert 3.5.4.4');
167
176
connection con1_super;
168
177
--disable_warnings
178
--error ER_TRG_DOES_NOT_EXIST
171
180
drop database if exists db_drop4;
172
181
--enable_warnings
173
182
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
176
# Test case: Ensure that when a table is dropped, all triggers for which it is the
185
# Test case: Ensure that when a table is dropped, all triggers for which it is the
177
186
# subject table are also cleanly dropped.
178
187
let $message= Testcase 3.5.4.5:;
179
188
--source include/show_msg.inc
181
190
connection con1_super;
182
191
create database db_drop5;
184
eval create table t1 (f1 char(50)) engine=$engine_type;
193
--replace_result $engine_type <engine_to_be_used>
194
eval create table t1 (f1 char(50)) engine = $engine_type;
185
195
grant INSERT, SELECT on t1 to test_general;
186
Create trigger trg5 BEFORE INSERT on t1
196
Create trigger trg5 BEFORE INSERT on t1
187
197
for each row set new.f1='Trigger 3.5.4.5';
188
198
connection con1_general;
195
205
select trigger_schema, trigger_name, event_object_table
196
206
from information_schema.triggers
197
207
where information_schema.triggers.trigger_name='trg5';
198
eval create table t1 (f1 char(50)) engine=$engine_type;
208
--replace_result $engine_type <engine_to_be_used>
209
eval create table t1 (f1 char(50)) engine = $engine_type;
199
210
grant INSERT, SELECT on t1 to test_general;
200
211
connection con1_general;
201
212
Insert into t1 values ('2nd Insert 3.5.4.5');
226
# Test case: Ensure that, if CREATE TRIGGER is executed with a non-existent
237
# Test case: Ensure that, if CREATE TRIGGER is executed with a non-existent
227
238
# subject table, the statement fails with an appropriate error message.
228
239
let $message= Testcase 3.5.5.1:;
229
240
--source include/show_msg.inc
242
--error ER_NO_SUCH_TABLE
232
243
Create trigger trg1 before INSERT on t100 for each row set new.f2=1000;
236
# Test case: Ensure that, if CREATE TRIGGER is executed with a temporary table
247
# Test case: Ensure that, if CREATE TRIGGER is executed with a temporary table
237
248
# as the subject table, the statement fails with an appropriate error message.
238
249
let $message= Testcase 3.5.5.2:;
239
250
--source include/show_msg.inc
241
252
Create temporary table t1_temp (f1 bigint signed, f2 bigint unsigned);
244
Create trigger trg2 before INSERT
254
--error ER_TRG_ON_VIEW_OR_TEMP_TABLE
255
Create trigger trg2 before INSERT
245
256
on t1_temp for each row set new.f2=9999;
249
260
drop table t1_temp;
250
261
--enable_warnings
254
# Test case: Ensure that, if CREATE TRIGGER is executed with a view as the subject
265
# Test case: Ensure that, if CREATE TRIGGER is executed with a view as the subject
255
266
# table, the statement fails with an appropriate error message.
256
267
let $message= Testcase 3.5.5.3:;
257
268
--source include/show_msg.inc
259
270
Create view vw3 as select f118 from tb3;
261
# OBN Not sure why the server is returning error 1347
263
Create trigger trg3 before INSERT
272
# OBN Not sure why the server is returning error ER_WRONG_OBJECT
273
--error ER_WRONG_OBJECT
274
Create trigger trg3 before INSERT
264
275
on vw3 for each row set new.f118='s';
269
280
--enable_warnings
273
# Test case: Ensure that, if CREATE TRIGGER is executed with a table that resides
274
# in a different database than in which the trigger will reside, the
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
275
286
# statement fails with an appropriate error message; that is, ensure that
276
287
# the trigger and its subject table must reside in the same database.
277
288
let $message= Testcase 3.5.5.4:;
281
292
create database dbtest_one;
282
293
create database dbtest_two;
284
eval create table t2 (f1 char(15)) engine=$engine_type;
295
--replace_result $engine_type <engine_to_be_used>
296
eval create table t2 (f1 char(15)) engine = $engine_type;
298
--error ER_TRG_IN_WRONG_SCHEMA
287
299
create trigger trg4 before INSERT
288
300
on dbtest_two.t2 for each row set new.f1='trig 3.5.5.4';
289
301
grant INSERT, SELECT on dbtest_two.t2 to test_general;
328
340
--source include/show_msg.inc
331
# Test case: Ensure that a trigger definition that specifies a trigger action
332
# time that is not either BEFORE or AFTER fails, with an appropriate
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
333
345
# error message, at CREATE TRIGGER time.
334
346
let $message= Testcase 3.5.6.3:;
335
347
--source include/show_msg.inc
349
--error ER_PARSE_ERROR
338
350
Create trigger trg3_1 DURING UPDATE on tb3 for each row set new.f132=25;
351
--error ER_PARSE_ERROR
340
352
Create trigger trg3_2 TIME INSERT on tb3 for each row set new.f132=15;
343
355
# OBN - Although none of the above should have been created we should do a cleanup
344
356
# since if they have been created, not dropping them will affect following
346
358
--disable_warnings
359
--error 0, ER_TRG_DOES_NOT_EXIST
348
360
drop trigger tb3.trg3_1;
361
--error 0, ER_TRG_DOES_NOT_EXIST
350
362
drop trigger tb3.trg3_2;
351
363
--enable_warnings
354
# Test case: Ensure that a trigger defined with a trigger action time of BEFORE
355
# always executes its triggered action immediately before the trigger event.
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.
356
368
# See section 3.5.1.1
357
369
let $message= Testcase 3.5.6.4 (see Testcase 3.5.1.1);
358
370
--source include/show_msg.inc
361
# Test case: Ensure that a trigger defined with a trigger action time of AFTER
373
# Test case: Ensure that a trigger defined with a trigger action time of AFTER
362
374
# always executes its triggered action immediately after the trigger event.
363
375
let $message= Testcase 3.5.6.5 (see Testcase 3.5.1.1);
364
376
--source include/show_msg.inc
384
396
--source include/show_msg.inc
387
# Test case: Ensure that a trigger definition that specifies a trigger event that
388
# is not either INSERT, UPDATE or DELETE fails, with an appropriate error
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
389
401
# message, at CREATE TRIGGER time.
390
402
let $message= Testcase 3.5.7.4:;
391
403
--source include/show_msg.inc
405
--error ER_PARSE_ERROR
394
406
Create trigger trg4_1 BEFORE SELECT on tb3 for each row set new.f132=5;
407
--error ER_PARSE_ERROR
396
408
Create trigger trg4_2 AFTER VALUE on tb3 for each row set new.f132=1;
399
411
# OBN - Although none of the above should have been created we should do a cleanup
400
412
# since if they have been created, not dropping them will affect following
402
414
--disable_warnings
415
--error 0, ER_TRG_DOES_NOT_EXIST
404
416
drop trigger tb3.trg4_1;
417
--error 0, ER_TRG_DOES_NOT_EXIST
406
418
drop trigger tb3.trg4_2;
407
419
--enable_warnings
409
#Section 3.5.7.5 / 3.5.7.6
410
# Test case: Ensure that it is not possible to create multiple BEFORE INSERT triggers
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
411
423
# on the same table, even if the triggers have different names / different
412
424
# triggered actions.
413
425
let $message= Testcase 3.5.7.5 / 3.5.7.6:;
414
426
--source include/show_msg.inc
416
Create trigger trg5_1 BEFORE INSERT
428
Create trigger trg5_1 BEFORE INSERT
417
429
on tb3 for each row set new.f122='Trigger1 3.5.7.5/6';
419
--error ER_NOT_SUPPORTED_YET
420
Create trigger trg5_2 BEFORE INSERT
431
--error ER_NOT_SUPPORTED_YET
432
Create trigger trg5_2 BEFORE INSERT
421
433
on tb3 for each row set new.f122='Trigger2 3.5.7.5';
423
435
Insert into tb3 (f121,f122) values ('Test 3.5.7.5/6','Insert 3.5.7.5');
426
438
Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
430
442
drop trigger trg5_1;
443
--error 0, ER_TRG_DOES_NOT_EXIST
432
444
drop trigger trg5_2;
433
445
delete from tb3 where f121='Test 3.5.7.5/6';
434
446
--enable_warnings
437
#Section 3.5.7.7 / 3.5.7.8
438
# Test case: Ensure that it is not possible to create multiple AFTER INSERT triggers
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
439
451
# on the same table, even if the triggers have different names / different
440
452
# triggered actions.
441
453
let $message= Testcase 3.5.7.7 / 3.5.7.8:;
442
454
--source include/show_msg.inc
444
456
set @test_var='Before trig 3.5.7.7';
445
Create trigger trg6_1 AFTER INSERT
457
Create trigger trg6_1 AFTER INSERT
446
458
on tb3 for each row set @test_var='Trigger1 3.5.7.7/8';
448
--error ER_NOT_SUPPORTED_YET
449
Create trigger trg6_2 AFTER INSERT
460
--error ER_NOT_SUPPORTED_YET
461
Create trigger trg6_2 AFTER INSERT
450
462
on tb3 for each row set @test_var='Trigger2 3.5.7.7';
452
464
select @test_var;
458
470
select @test_var;
462
474
drop trigger trg6_1;
475
--error 0, ER_TRG_DOES_NOT_EXIST
464
476
drop trigger trg6_2;
465
477
delete from tb3 where f121='Test 3.5.7.7/8';
466
478
--enable_warnings
469
481
#Section 3.5.7.9 / 3.5.7.10
470
# Test case: Ensure that it is not possible to create multiple BEFORE UPDATE triggers
471
# on the same table, even if the triggers have different names / different
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
472
484
# triggered actions.
473
485
let $message= Testcase 3.5.7.9/10:;
474
486
--source include/show_msg.inc
476
Create trigger trg7_1 BEFORE UPDATE
488
Create trigger trg7_1 BEFORE UPDATE
477
489
on tb3 for each row set new.f122='Trigger1 3.5.7.9/10';
479
--error ER_NOT_SUPPORTED_YET
480
Create trigger trg7_2 BEFORE UPDATE
491
--error ER_NOT_SUPPORTED_YET
492
Create trigger trg7_2 BEFORE UPDATE
481
493
on tb3 for each row set new.f122='Trigger2 3.5.7.9';
483
495
Insert into tb3 (f121,f122) values ('Test 3.5.7.9/10','Insert 3.5.7.9');
486
498
Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
490
502
drop trigger trg7_1;
503
--error 0, ER_TRG_DOES_NOT_EXIST
492
504
drop trigger trg7_2;
493
505
delete from tb3 where f121='Test 3.5.7.9/10';
495
507
#Section 3.5.7.11 / 3.5.7.12
496
# Test case: Ensure that it is not possible to create multiple AFTER UPDATE triggers
508
# Test case: Ensure that it is not possible to create multiple AFTER UPDATE triggers
497
509
# on the same table, even if the triggers have different names / different
499
511
let $message= Testcase 3.5.7.11/12:;
500
512
--source include/show_msg.inc
502
514
set @test_var='Before trig 3.5.7.11';
503
Create trigger trg8_1 AFTER UPDATE
515
Create trigger trg8_1 AFTER UPDATE
504
516
on tb3 for each row set @test_var='Trigger 3.5.7.11/12';
506
--error ER_NOT_SUPPORTED_YET
507
Create trigger trg8_2 AFTER UPDATE
518
--error ER_NOT_SUPPORTED_YET
519
Create trigger trg8_2 AFTER UPDATE
508
520
on tb3 for each row set @test_var='Trigger2 3.5.7.11';
518
530
delete from tb3 where f121='Test 3.5.7.11/12';
522
534
drop trigger trg8_1;
535
--error 0, ER_TRG_DOES_NOT_EXIST
524
536
drop trigger trg8_2;
525
537
delete from tb3 where f121='Test 3.5.7.11/12';
527
539
#Section 3.5.7.13 / 3.5.7.14
528
# Test case: Ensure that it is not possible to create multiple BEFORE DELETE triggers
540
# Test case: Ensure that it is not possible to create multiple BEFORE DELETE triggers
529
541
# on the same table, even if the triggers have different names / different
530
542
# triggered actions.
531
543
let $message= Testcase 3.5.7.13/14:;
532
544
--source include/show_msg.inc
535
Create trigger trg9_1 BEFORE DELETE
547
Create trigger trg9_1 BEFORE DELETE
536
548
on tb3 for each row set @test_var=@test_var+1;
538
550
--error ER_NOT_SUPPORTED_YET
539
Create trigger trg9_2 BEFORE DELETE
551
Create trigger trg9_2 BEFORE DELETE
540
552
on tb3 for each row set @test_var=@test_var+10;
542
554
select @test_var;
550
562
select @test_var;
554
566
drop trigger trg9_1;
567
--error 0, ER_TRG_DOES_NOT_EXIST
556
568
drop trigger trg9_2;
557
569
delete from tb3 where f121='Test 3.5.7.13/14';
559
571
#Section 3.5.7.15 / 3.5.7.16
560
# Test case: Ensure that it is not possible to create multiple AFTER DELETE triggers
561
# on the same table, even if the triggers have different names / different
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
562
574
# triggered actions.
563
575
let $message= Testcase 3.5.7.15/16:;
564
576
--source include/show_msg.inc
567
Create trigger trg_3_406010_1 AFTER DELETE
579
Create trigger trg_3_406010_1 AFTER DELETE
568
580
on tb3 for each row set @test_var=@test_var+5;
570
--error ER_NOT_SUPPORTED_YET
571
Create trigger trg_3_406010_2 AFTER DELETE
582
--error ER_NOT_SUPPORTED_YET
583
Create trigger trg_3_406010_2 AFTER DELETE
572
584
on tb3 for each row set @test_var=@test_var+50;
575
Create trigger trg_3_406010_1 AFTER INSERT
586
--error ER_TRG_ALREADY_EXISTS
587
Create trigger trg_3_406010_1 AFTER INSERT
576
588
on tb3 for each row set @test_var=@test_var+1;
578
590
select @test_var;
586
598
select @test_var;
590
602
drop trigger trg_3_406010_1;
603
--error 0, ER_TRG_DOES_NOT_EXIST
592
604
drop trigger trg_3_406010_2;
593
605
delete from tb3 where f121='Test 3.5.7.15/16';
594
606
--enable_warnings
597
609
#Section 3.5.7.17
598
# Test case: Ensure that it is possible to have a BEFORE INSERT, an AFTER INSERT,
599
# a BEFORE UPDATE, an AFTER UPDATE, a BEFORE DELETE, and an AFTER DELETE
600
# trigger on the same table; that is, ensure that every persistent base
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
601
613
# table may be the subject table for exactly six triggers
602
614
let $message= Testcase 3.5.7.17 (see Testcase 3.5.1.1);
603
615
--source include/show_msg.inc