1
#======================================================================
4
# (test case numbering refer to requirement document TP v1.1)
5
#======================================================================
9
# 1. Information Schema Trigger Table
11
###############################################
13
# General setup for Trigger tests
14
let $message= Testcase: 3.5:;
15
--source include/show_msg.inc
17
--disable_abort_on_error
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;
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
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
27
connect (con_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
28
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
29
connect (con_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
33
#####################################################
34
################# Section 3.5.1 #####################
35
# Syntax checks for CREATE TRIGGER and DROP TRIGGER #
36
#####################################################
39
# Testcase: Ensure that all clauses that should be supported are supported.
40
let $message= Testcase: 3.5.1.1:;
41
--source include/show_msg.inc
42
# OBN - This test case tests basic trigger definition and execution
43
# of INSERT/UPDATE/DELETE actions and BEFORE/AFTER timings.
44
# As such it covers the equirements in sections 3.5.6.1, 3.5.6.2,
45
# 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.
46
# - Note currently as a result of limitations with locking tables in
47
# triggers, a specifc lockingof the tables is done.
48
# Once fixed, the locking and alias referances should be removed
52
Create trigger trg1_1 BEFORE INSERT
53
on tb3 for each row set @test_before = 2, new.f142 = @test_before;
54
Create trigger trg1_2 AFTER INSERT
55
on tb3 for each row set @test_after = 6;
56
Create trigger trg1_4 BEFORE UPDATE
57
on tb3 for each row set @test_before = 27,
58
new.f142 = @test_before,
59
new.f122 = 'Before Update Trigger';
60
Create trigger trg1_3 AFTER UPDATE
61
on tb3 for each row set @test_after = '15';
62
Create trigger trg1_5 BEFORE DELETE on tb3 for each row
63
select count(*) into @test_before from tb3 as tr_tb3
64
where f121 = 'Test 3.5.1.1';
65
Create trigger trg1_6 AFTER DELETE on tb3 for each row
66
select count(*) into @test_after from tb3 as tr_tb3
67
where f121 = 'Test 3.5.1.1';
68
# Trigger Execution Insert (before and after)
71
select @test_before, @test_after;
72
Insert into tb3 (f121, f122, f142, f144, f134)
73
values ('Test 3.5.1.1', 'First Row', @test_before, @test_after, 1);
74
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
75
select @test_before, @test_after;
77
# Trigger Execution Update (before and after)
78
set @test_before = 18;
80
select @test_before, @test_after;
81
Update tb3 set tb3.f122 = 'Update',
82
tb3.f142 = @test_before,
83
tb3.f144 = @test_after
84
where tb3.f121 = 'Test 3.5.1.1';
85
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
86
select @test_before, @test_after;
88
# Trigger Execution Delete (before and after)
89
Insert into tb3 (f121, f122, f142, f144, f134)
90
values ('Test 3.5.1.1', 'Second Row', 5, 6, 2);
93
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
94
select @test_before, @test_after;
95
Delete from tb3 where f121 = 'Test 3.5.1.1' and f134 = 2;
96
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
97
select @test_before, @test_after;
114
delete from tb3 where f121='Test 3.5.1.1';
118
# Testcase: Ensure that all clauses that should not be supported are disallowed
119
# with an appropriate error message.
120
let $message= Testcase: 3.5.1.2:;
121
--source include/show_msg.inc
124
Create trigger trg_1 after insert
125
on tb3 for each statement set @x= 1;
135
# Testcase: Ensure that all supported clauses are supported only in the correct order.
136
let $message= Testcase 3.5.1.3:;
137
--source include/show_msg.inc
139
CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't';
142
CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's';
145
CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row;
148
CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row;
151
CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test';
154
# OBN - Although none of the above should have been created we should do a cleanup
155
# since if they have been created, not dropping them will affect following
172
# Testcase: Ensure that an appropriate error message is returned if a clause
173
# is out-of-order in an SQL statement.
174
# OBN - FIXME - Missing 3.5.1.4 need to add
177
# Testcase: Ensure that all clauses that are defined to be mandatory are indeed
178
# required to be mandatory by the MySQL server and tools
179
let $message= Testcase: 3.5.1.5:;
180
--source include/show_msg.inc
183
CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e';
186
CREATE TRIGGER trg4_2 INSERT on tb3 for each set row new.f120 = 'f';
189
CREATE TRIGGER trg4_3 BEFORE INSERT tb3 for each row set new.f120 = 'g';
192
CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g';
195
CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g';
198
CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g';
201
# OBN - Although none of the above should have been created we should do a cleanup
202
# since if they have been created, not dropping them will affect following
220
# Testcase: Ensure that any clauses that are defined to be optional are indeed
221
# trated as optional by MySQL server and tools
222
let $message= Testcase 3.5.1.6: - Need to fix;
223
--source include/show_msg.inc
224
# OBN - FIXME - Missing 3.5.1.6 need to add
227
# Testcase: Ensure that all valid, fully-qualified, and non-qualified,
228
# trigger names are accepted, at creation time.
229
let $message= Testcase 3.5.1.7: - need to fix;
230
--source include/show_msg.inc
232
drop table if exists t1;
233
eval create table t1 (f1 int, f2 char(25),f3 int) engine=$engine_type;
234
CREATE TRIGGER trg5_1 BEFORE INSERT on test.t1
235
for each row set new.f3 = '14';
236
CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ
237
BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
239
insert into t1 (f2) values ('insert 3.5.1.7');
241
update t1 set f2='update 3.5.1.7';
243
select trigger_name from information_schema.triggers;
249
# The above trigger should be dropped since the name was trimmed.
250
drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ;
254
# Testcase: Ensure that any invalid trigger name is never accepted, and that an
255
# appropriate error message is returned when the name is rejected.
256
let $message= Testcase 3.5.1.8:;
257
--source include/show_msg.inc
260
CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't';
263
CREATE TRIGGER trigger before insert on tb3 for each row set new.f120 = 't';
266
CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't';
269
CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't';
272
CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't';
275
CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3
276
for each row set new.f120 ='X';
279
drop database if exists trig_db;
281
create database trig_db;
283
eval create table t1 (f1 integer) engine = $engine_type;
285
# Can't create a trigger in a different database
288
CREATE TRIGGER trig_db.trg6_2 AFTER INSERT on tb3
289
for each row set @ret_trg6_2 = 5;
291
# Can't create a trigger refrencing a table in a different db
294
CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3
295
for each row set @ret_trg6_3 = 18;
301
drop database trig_db;
302
# OBN - Although none of the above should have been created we should do a cleanup
303
# since if they have been created, not dropping them will affect following
312
#Testcase: Ensure that a reference to a non-existent trigger is rejected with
313
# an appropriate error message.
314
let $message= Testcase 3.5.1.9:(cannot be inplemented at this point);
315
--source include/show_msg.inc
319
#Testcase: Ensure that it is not possible to create two triggers with the same name on
321
let $message= Testcase 3.5.1.10:;
322
--source include/show_msg.inc
324
CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X';
327
CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y';
337
# Testcase: Ensure that it is not possible to create two or more triggers with
338
# the same name, provided each is associated with a different table.
339
let $message= Testcase 3.5.1.?:;
340
--source include/show_msg.inc
343
drop table if exists t1;
344
drop table if exists t2;
346
eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
347
eval create table t2 (f1 char(50), f2 integer) engine = $engine_type;
349
create trigger trig before insert on t1
350
for each row set new.f1 ='trig t1';
353
create trigger trig before update on t2
354
for each row set new.f1 ='trig t2';
356
insert into t1 value ('insert to t1',1);
358
update t1 set f1='update to t1';
360
insert into t2 value ('insert to t2',2);
361
update t2 set f1='update to t1';
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
376
let $message= Testcase 3.5.1.11:;
377
--source include/show_msg.inc
380
drop database if exists trig_db1;
381
drop database if exists trig_db2;
382
drop database if exists trig_db3;
384
create database trig_db1;
385
create database trig_db2;
386
create database trig_db3;
388
eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
389
create trigger trig before insert on t1
390
for each row set new.f1 ='trig1', @test_var1='trig1';
392
eval create table t2 (f1 char(50), f2 integer) engine = $engine_type;
393
create trigger trig before insert on t2
394
for each row set new.f1 ='trig2', @test_var2='trig2';
396
eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
397
create trigger trig before insert on t1
398
for each row set new.f1 ='trig3', @test_var3='trig3';
400
set @test_var1= '', @test_var2= '', @test_var3= '';
402
insert into t1 (f1,f2) values ('insert to db1 t1',1);
403
insert into trig_db1.t1 (f1,f2) values ('insert to db1 t1 from db1',2);
404
insert into trig_db2.t2 (f1,f2) values ('insert to db2 t2 from db1',3);
405
insert into trig_db3.t1 (f1,f2) values ('insert to db3 t1 from db1',4);
406
select @test_var1, @test_var2, @test_var3;
408
select * from trig_db2.t2;
409
select * from trig_db3.t1;
415
drop database trig_db1;
416
drop database trig_db2;
417
drop database trig_db3;
420
###########################################
421
################ Section 3.5.2 ############
422
# Check for the global nature of Triggers #
423
###########################################
426
# Test case: Ensure that if a trigger created without a qualifying database
427
# name belongs to the database in use at creation time.
429
# Test case: Ensure that if a trigger created with a qualifying database name
430
# belongs to the database specified.
432
# Test case: Ensure that if a trigger created with a qualifying database name
433
# does not belong to the database in use at creation time unless
434
# the qualifying database name identifies the database that is
435
# also in use at creation time.
436
let $message= Testcase 3.5.2.1/2/3:;
437
--source include/show_msg.inc
441
drop database if exists trig_db1;
442
drop database if exists trig_db2;
444
create database trig_db1;
445
create database trig_db2;
447
eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
448
eval create table trig_db2.t1 (f1 char(50), f2 integer) engine = $engine_type;
449
create trigger trig1_b before insert on t1
450
for each row set @test_var1='trig1_b';
451
create trigger trig_db1.trig1_a after insert on t1
452
for each row set @test_var2='trig1_a';
453
create trigger trig_db2.trig2 before insert on trig_db2.t1
454
for each row set @test_var3='trig2';
455
select trigger_schema, trigger_name, event_object_table
456
from information_schema.triggers;
458
set @test_var1= '', @test_var2= '', @test_var3= '';
459
insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352);
460
insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352);
461
select @test_var1, @test_var2, @test_var3;
465
drop database trig_db1;
466
drop database trig_db2;
468
###########################################
469
################ Section 3.5.3 ############
470
# Check for the global nature of Triggers #
471
###########################################
473
# General setup to be used in all testcases of 3.5.3
474
let $message= Testcase 3.5.3:;
475
--source include/show_msg.inc
478
drop database if exists priv_db;
480
create database priv_db;
482
create table t1 (f1 char(20));
484
create User test_noprivs@localhost;
485
set password for test_noprivs@localhost = password('PWD');
487
create User test_yesprivs@localhost;
488
set password for test_yesprivs@localhost = password('PWD');
490
#Section 3.5.3.1 / 3.5.3.2
491
# Test case: Ensure SUPER privilege is required to create a trigger
492
#Section 3.5.3.3 / 3.5.3.4
493
# Test case: Ensure that root always has the SUPER privilege.
494
# OMR - No need to test this since SUPER priv is an existing one and not related
495
# or added for triggers (TP 2005-06-06)
496
#Section 3.5.3.5 / 3.5.3.6
497
# Test case: Ensure that the SUPER privilege is required to drop a trigger.
498
let $message= Testcase 3.5.3.2/6:;
499
--source include/show_msg.inc
501
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
502
grant ALL on *.* to test_noprivs@localhost;
503
revoke SUPER on *.* from test_noprivs@localhost;
504
show grants for test_noprivs@localhost;
506
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
507
grant SUPER on *.* to test_yesprivs@localhost;
508
# Adding the minimal priv to be able to set to the db
509
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
510
show grants for test_yesprivs@localhost;
512
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
513
connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
514
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
515
connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
518
let $message= Testcase 3.5.3.2:;
519
--source include/show_msg.inc
526
create trigger trg1_1 before INSERT on t1 for each row
527
set new.f1 = 'trig 3.5.3.2_1-no';
531
insert into t1 (f1) values ('insert 3.5.3.2-no');
534
connection yes_privs;
538
create trigger trg1_2 before INSERT on t1 for each row
539
set new.f1 = 'trig 3.5.3.2_2-yes';
543
insert into t1 (f1) values ('insert 3.5.3.2-yes');
546
let $message= Testcase 3.5.3.6:;
547
--source include/show_msg.inc
557
insert into t1 (f1) values ('insert 3.5.3.6-yes');
560
connection yes_privs;
567
insert into t1 (f1) values ('insert 3.5.3.6-no');
576
disconnect yes_privs;
581
# Test case: Ensure that use of the construct "SET NEW. <column name> = <value>"
582
# fails at CREATE TRIGGER time, if the current user does not have the
583
# UPDATE privilege on the column specified
584
# Note: As a result of bug 8884 the triggers are actually created.
585
# Disabled because of bug 8884
587
# --- 3.5.3.7a - Privs set on a global level
588
let $message=Testcase 3.5.3.7a:;
589
--source include/show_msg.inc
591
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
592
grant ALL on *.* to test_noprivs@localhost;
593
revoke UPDATE on *.* from test_noprivs@localhost;
594
show grants for test_noprivs@localhost;
596
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
597
grant SUPER, UPDATE on *.* to test_yesprivs@localhost;
598
show grants for test_yesprivs@localhost;
600
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
601
connect (no_privs_424a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
602
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
603
connect (yes_privs_424a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
605
connection no_privs_424a;
611
let $message= Trigger create disabled - should fail - Bug 8884;
612
--source include/show_msg.inc
614
# create trigger trg4a_1 before INSERT on t1 for each row
615
# set new.f1 = 'trig 3.5.3.7-1a';
618
insert into t1 (f1) values ('insert 3.5.3.7-1a');
621
drop trigger trg4a_1;
623
connection yes_privs_424a;
627
create trigger trg4a_2 before INSERT on t1 for each row
628
set new.f1 = 'trig 3.5.3.7-2a';
631
insert into t1 (f1) values ('insert 3.5.3.7-2b');
636
drop trigger trg4a_2;
637
disconnect no_privs_424a;
638
disconnect yes_privs_424a;
641
# --- 3.5.3.7b - Privs set on a database level
642
let $message= Testcase 3.5.3.7b:;
643
--source include/show_msg.inc
645
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
646
grant SUPER on *.* to test_noprivs;
647
grant ALL on priv_db.* to test_noprivs@localhost;
648
revoke UPDATE on priv_db.* from test_noprivs@localhost;
649
show grants for test_noprivs;
651
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
652
grant SUPER on *.* to test_yesprivs@localhost;
653
grant UPDATE on priv_db.* to test_yesprivs@localhost;
654
show grants for test_yesprivs@localhost;
656
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
657
connect (no_privs_424b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
658
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
659
connect (yes_privs_424b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
662
connection no_privs_424b;
666
let $message= Trigger create disabled - should fail - Bug 8884;
667
--source include/show_msg.inc
669
# create trigger trg4b_1 before UPDATE on t1 for each row
670
# set new.f1 = 'trig 3.5.3.7-1b';
673
insert into t1 (f1) values ('insert 3.5.3.7-1b');
675
update t1 set f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b';
678
drop trigger trg4b_1;
680
connection yes_privs_424b;
683
create trigger trg4b_2 before UPDATE on t1 for each row
684
set new.f1 = 'trig 3.5.3.7-2b';
687
insert into t1 (f1) values ('insert 3.5.3.7-2b');
689
update t1 set f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b';
693
drop trigger trg4b_2;
694
disconnect no_privs_424b;
695
disconnect yes_privs_424b;
698
# --- 3.5.3.7c - Privs set on a table level
699
let $message= Testcase 3.5.3.7c;
700
--source include/show_msg.inc
702
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
703
grant SUPER on *.* to test_noprivs@localhost;
704
grant ALL on priv_db.t1 to test_noprivs@localhost;
705
revoke UPDATE on priv_db.t1 from test_noprivs@localhost;
706
show grants for test_noprivs;
708
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
709
grant SUPER on *.* to test_yesprivs@localhost;
710
grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
711
show grants for test_yesprivs@localhost;
713
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
714
connect (no_privs_424c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
715
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
716
connect (yes_privs_424c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
719
connection no_privs_424c;
723
let $message= Trigger create disabled - should fail - Bug 8884;
724
--source include/show_msg.inc
726
# create trigger trg4c_1 before INSERT on t1 for each row
727
# set new.f1 = 'trig 3.5.3.7-1c';
730
insert into t1 (f1) values ('insert 3.5.3.7-1c');
733
drop trigger trg4c_1;
735
connection yes_privs_424c;
738
create trigger trg4c_2 before INSERT on t1 for each row
739
set new.f1 = 'trig 3.5.3.7-2c';
742
insert into t1 (f1) values ('insert 3.5.3.7-2c');
747
drop trigger trg4c_2;
748
disconnect no_privs_424c;
749
disconnect yes_privs_424c;
752
# --- 3.5.3.7d - Privs set on a column level
754
let $message= Testcase 3.5.3.7d:;
756
--source include/show_msg.inc
758
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
759
grant SUPER on *.* to test_noprivs@localhost;
760
# There is no ALL privs on the column level
761
grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
762
show grants for test_noprivs;
764
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
765
grant SUPER on *.* to test_yesprivs@localhost;
766
grant UPDATE (f1) on priv_db.t1 to test_yesprivs@localhost;
767
show grants for test_noprivs;
769
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
770
connect (no_privs_424d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
771
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
772
connect (yes_privs_424d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
775
connection no_privs_424d;
778
let $message= Trigger create disabled - should fail - Bug 8884;
779
--source include/show_msg.inc
781
# create trigger trg4d_1 before INSERT on t1 for each row
782
# set new.f1 = 'trig 3.5.3.7-1d';
785
insert into t1 (f1) values ('insert 3.5.3.7-1d');
788
drop trigger trg4d_1;
790
connection yes_privs_424d;
793
create trigger trg4d_2 before INSERT on t1 for each row
794
set new.f1 = 'trig 3.5.3.7-2d';
797
insert into t1 (f1) values ('insert 3.5.3.7-2d');
802
drop trigger trg4d_2;
803
disconnect no_privs_424d;
804
disconnect yes_privs_424d;
808
# Test case: Ensure that use of the construct "SET <target> = NEW. <Column name>" fails
809
# at CREATE TRIGGER time, if the current user does not have the SELECT privilege
810
# on the column specified.
812
# --- 3.5.3.8a - Privs set on a global level
813
let $message= Testcase 3.5.3.8a:;
814
--source include/show_msg.inc
816
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
817
grant ALL on *.* to test_noprivs@localhost;
818
revoke SELECT on *.* from test_noprivs@localhost;
819
show grants for test_noprivs@localhost;
821
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
822
grant SUPER, SELECT on *.* to test_yesprivs@localhost;
823
show grants for test_yesprivs@localhost;
825
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
826
connect (no_privs_425a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
827
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
828
connect (yes_privs_425a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
831
connection no_privs_425a;
836
let $message= Trigger create disabled - should fail - Bug 8887;
837
--source include/show_msg.inc
839
# create trigger trg5a_1 before INSERT on t1 for each row
840
# set @test_var = new.f1;
843
set @test_var = 'before trig 3.5.3.8-1a';
845
insert into t1 (f1) values ('insert 3.5.3.8-1a');
848
drop trigger trg5a_1;
850
connection yes_privs_425a;
854
create trigger trg5a_2 before INSERT on t1 for each row
855
set @test_var= new.f1;
858
set @test_var= 'before trig 3.5.3.8-2a';
860
insert into t1 (f1) values ('insert 3.5.3.8-2a');
865
drop trigger trg5a_2;
866
disconnect no_privs_425a;
867
disconnect yes_privs_425a;
870
# --- 3.5.3.8b - Privs set on a database level
871
let $message= Testcase: 3.5.3.8b;
872
--source include/show_msg.inc
874
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
875
grant SUPER on *.* to test_noprivs@localhost;
876
grant ALL on priv_db.* to test_noprivs@localhost;
877
revoke SELECT on priv_db.* from test_noprivs@localhost;
878
show grants for test_noprivs@localhost;
880
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
881
grant SUPER on *.* to test_yesprivs@localhost;
882
grant SELECT on priv_db.* to test_yesprivs@localhost;
883
show grants for test_yesprivs@localhost;
885
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
886
connect (no_privs_425b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
887
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
888
connect (yes_privs_425b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
891
connection no_privs_425b;
895
let $message= Trigger create disabled - should fail - Bug 8887;
896
--source include/show_msg.inc
898
# create trigger trg5b_1 before UPDATE on t1 for each row
899
# set @test_var= new.f1;
902
set @test_var= 'before trig 3.5.3.8-1b';
903
insert into t1 (f1) values ('insert 3.5.3.8-1b');
905
update t1 set f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b';
908
drop trigger trg5b_1;
910
connection yes_privs_425b;
913
create trigger trg5b_2 before UPDATE on t1 for each row
914
set @test_var= new.f1;
917
set @test_var= 'before trig 3.5.3.8-2b';
918
insert into t1 (f1) values ('insert 3.5.3.8-2b');
920
update t1 set f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b';
924
drop trigger trg5b_2;
925
disconnect no_privs_425b;
926
disconnect yes_privs_425b;
929
# --- 3.5.3.8c - Privs set on a table level
930
let $message= Testcase 3.5.3.8c:;
931
--source include/show_msg.inc
933
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
934
grant SUPER on *.* to test_noprivs@localhost;
935
grant ALL on priv_db.t1 to test_noprivs@localhost;
936
revoke SELECT on priv_db.t1 from test_noprivs@localhost;
937
show grants for test_noprivs@localhost;
939
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
940
grant SUPER on *.* to test_yesprivs@localhost;
941
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
942
show grants for test_yesprivs@localhost;
944
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
945
connect (no_privs_425c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
946
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
947
connect (yes_privs_425c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
950
connection no_privs_425c;
954
let $message= Trigger create disabled - should fail - Bug 8887;
955
--source include/show_msg.inc
957
# create trigger trg5c_1 before INSERT on t1 for each row
958
# set @test_var= new.f1;
961
set @test_var= 'before trig 3.5.3.8-1c';
962
insert into t1 (f1) values ('insert 3.5.3.8-1c');
965
drop trigger trg5c_1;
967
connection yes_privs_425c;
970
create trigger trg5c_2 before INSERT on t1 for each row
971
set @test_var= new.f1;
974
set @test_var='before trig 3.5.3.8-2c';
975
insert into t1 (f1) values ('insert 3.5.3.8-2c');
979
drop trigger trg5c_2;
980
disconnect no_privs_425c;
981
disconnect yes_privs_425c;
984
# --- 3.5.3.8d - Privs set on a column level
985
let $message=Testcase: 3.5.3.8d:;
986
--source include/show_msg.inc
988
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
989
grant SUPER on *.* to test_noprivs@localhost;
990
# There is no ALL prov on the column level
991
grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
992
show grants for test_noprivs@localhost;
994
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
995
grant SUPER on *.* to test_yesprivs@localhost;
996
grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost;
997
show grants for test_noprivs@localhost;
999
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
1000
connect (no_privs_425d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
1001
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
1002
connect (yes_privs_425d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
1005
connection no_privs_425d;
1008
let $message= Trigger create disabled - should fail - Bug 8887;
1009
--source include/show_msg.inc
1011
# create trigger trg5d_1 before INSERT on t1 for each row
1012
# set @test_var= new.f1;
1015
set @test_var='before trig 3.5.3.8-1d';
1016
insert into t1 (f1) values ('insert 3.5.3.8-1d');
1019
drop trigger trg5d_1;
1021
connection yes_privs_425d;
1024
create trigger trg5d_2 before INSERT on t1 for each row
1025
set @test_var= new.f1;
1028
set @test_var='before trig 3.5.3.8-2d';
1029
insert into t1 (f1) values ('insert 3.5.3.8-2d');
1034
drop trigger trg5d_2;
1039
drop database if exists priv_db;
1040
drop user test_yesprivs@localhost;
1041
drop user test_noprivs@localhost;
1042
drop user test_noprivs;
1045
####################################
1046
############ Section 3.5.4 #########
1047
# Drop Trigger Checkes: #
1048
####################################
1049
let $message= Testcase 3.5.4:;
1050
--source include/show_msg.inc
1056
# Testcase: Ensure that the DROP TRIGGER statement cleanly drops its target trigger.
1057
let $message= Testcase 3.5.4.1:;
1058
--source include/show_msg.inc
1060
connection con_super;
1061
create database db_drop;
1063
eval create table t1 (f1 char(30)) engine=$engine_type;
1064
grant INSERT, SELECT on db_drop.t1 to test_general;
1066
Create trigger trg1 BEFORE INSERT on t1
1067
for each row set new.f1='Trigger 3.5.4.1';
1068
connection con_general;
1070
Insert into t1 values ('Insert error 3.5.4.1');
1072
connection con_super;
1074
select trigger_schema, trigger_name, event_object_table
1075
from information_schema.triggers;
1076
connection con_general;
1077
Insert into t1 values ('Insert no trigger 3.5.4.1');
1082
connection con_super;
1086
drop database if exists db_drop;
1087
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
1091
# Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate error
1092
# message, if the trigger name does not exist.
1093
let $message= Testcase 3.5.4.2:;
1094
--source include/show_msg.inc
1096
connection con_super;
1097
create database db_drop2;
1100
drop table if exists t1_432 ;
1102
eval create table t1_432 (f1 char (30)) engine=$engine_type;
1104
Drop trigger tr_does_not_exit;
1107
drop table if exists t1_432 ;
1108
drop database if exists db_drop2;
1112
# Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate
1113
# error message, if <trigger name> is not a qualified name.
1114
let $message= Testcase 3.5.4.3:;
1115
--source include/show_msg.inc
1117
connection con_super;
1118
create database db_drop3;
1121
drop table if exists t1_433 ;
1122
drop table if exists t1_433a ;
1124
eval create table t1_433 (f1 char (30)) engine=$engine_type;
1125
eval create table t1_433a (f1a char (5)) engine=$engine_type;
1127
CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row
1128
set new.f1 = 'Trigger 3.5.4.3';
1132
Drop trigger t1.433.trg3;
1134
# Using database.table
1136
Drop trigger db_drop3.t1.433.trg3;
1140
Drop trigger mysql.trg3;
1142
# database does not exist
1144
Drop trigger tbx.trg3;
1147
Drop trigger db_drop3.trg3;
1148
drop table if exists t1_433;
1149
drop table if exists t1_433a;
1150
drop database if exists db_drop3;
1153
# Test case: Ensure that when a database is dropped, all triggers created within
1154
# that database are also cleanly dropped.
1155
let $message= Testcase 3.5.4.4:;
1156
--source include/show_msg.inc
1158
connection con_super;
1159
create database db_drop4;
1161
eval create table t1 (f1 char(30)) engine=$engine_type;
1162
grant INSERT, SELECT on db_drop4.t1 to test_general;
1163
Create trigger trg4 BEFORE INSERT on t1
1164
for each row set new.f1='Trigger 3.5.4.4';
1165
connection con_general;
1167
Insert into t1 values ('Insert 3.5.4.4');
1169
connection con_super;
1170
Drop database db_drop4;
1172
select trigger_schema, trigger_name, event_object_table
1173
from information_schema.triggers
1174
where information_schema.triggers.trigger_name='trg4';
1175
create database db_drop4;
1177
eval create table t1 (f1 char(30)) engine=$engine_type;
1178
grant INSERT, SELECT on db_drop4.t1 to test_general;
1179
connection con_general;
1180
Insert into t1 values ('2nd Insert 3.5.4.4');
1184
connection con_super;
1188
drop database if exists db_drop4;
1190
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
1193
# Test case: Ensure that when a table is dropped, all triggers for which it is the
1194
# subject table are also cleanly dropped.
1195
let $message= Testcase 3.5.4.5:;
1196
--source include/show_msg.inc
1198
connection con_super;
1199
create database db_drop5;
1201
eval create table t1 (f1 char(50)) engine=$engine_type;
1202
grant INSERT, SELECT on t1 to test_general;
1203
Create trigger trg5 BEFORE INSERT on t1
1204
for each row set new.f1='Trigger 3.5.4.5';
1205
connection con_general;
1207
Insert into t1 values ('Insert 3.5.4.5');
1209
connection con_super;
1212
select trigger_schema, trigger_name, event_object_table
1213
from information_schema.triggers
1214
where information_schema.triggers.trigger_name='trg5';
1215
eval create table t1 (f1 char(50)) engine=$engine_type;
1216
grant INSERT, SELECT on t1 to test_general;
1217
connection con_general;
1218
Insert into t1 values ('2nd Insert 3.5.4.5');
1222
connection con_super;
1226
drop database if exists db_drop5;
1228
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
1231
##################################
1232
######### Section 3.5.5 ##########
1233
# Checks on the Subject Table #
1234
##################################
1236
let $message= Testcase 3.5.5:;
1237
--source include/show_msg.inc
1243
# Test case: Ensure that, if CREATE TRIGGER is executed with a non-existent
1244
# subject table, the statement fails with an appropriate error message.
1245
let $message= Testcase 3.5.5.1:;
1246
--source include/show_msg.inc
1249
Create trigger trg1 before INSERT on t100 for each row set new.f2=1000;
1253
# Test case: Ensure that, if CREATE TRIGGER is executed with a temporary table
1254
# as the subject table, the statement fails with an appropriate error message.
1255
let $message= Testcase 3.5.5.2:;
1256
--source include/show_msg.inc
1258
Create temporary table t1_temp (f1 bigint signed, f2 bigint unsigned);
1261
Create trigger trg2 before INSERT
1262
on t1_temp for each row set new.f2=9999;
1271
# Test case: Ensure that, if CREATE TRIGGER is executed with a view as the subject
1272
# table, the statement fails with an appropriate error message.
1273
let $message= Testcase 3.5.5.3:;
1274
--source include/show_msg.inc
1276
Create view vw3 as select f118 from tb3;
1278
# OBN Not sure why the server is returning error 1347
1280
Create trigger trg3 before INSERT
1281
on vw3 for each row set new.f118='s';
1290
# Test case: Ensure that, if CREATE TRIGGER is executed with a table that resides
1291
# in a different database than in which the trigger will reside, the
1292
# statement fails with an appropriate error message; that is, ensure that
1293
# the trigger and its subject table must reside in the same database.
1294
let $message= Testcase 3.5.5.4:;
1295
--source include/show_msg.inc
1297
connection con_super;
1298
create database dbtest_one;
1299
create database dbtest_two;
1301
create table t2 (f1 char(15));
1304
create trigger trg4 before INSERT
1305
on dbtest_two.t2 for each row set new.f1='trig 3.5.5.4';
1306
grant INSERT, SELECT on dbtest_two.t2 to test_general;
1307
grant SELECT on dbtest_one.* to test_general;
1308
connection con_general;
1310
Insert into t2 values ('1st Insert 3.5.5.4');
1313
Insert into dbtest_two.t2 values ('2nd Insert 3.5.5.4');
1314
Select * from dbtest_two.t2;
1317
connection con_super;
1319
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
1320
DROP DATABASE if exists dbtest_one;
1321
drop database if EXISTS dbtest_two;
1324
#####################################
1325
########### Section 3.5.6 ###########
1326
# Check on the Trigger Action Time #
1327
#####################################
1329
let $message= Testcase 3.5.6:;
1330
--source include/show_msg.inc
1336
# Test case: Ensure that a trigger definition can specify a trigger action time of BEFORE.
1337
# See section 3.5.1.1
1338
let $message= Testcase 3.5.6.1 (see Testcase 3.5.1.1);
1339
--source include/show_msg.inc
1342
# Test case: Ensure that a trigger definition can specify a trigger action time of AFTER.
1343
# See section 3.5.1.1
1344
let $message= Testcase 3.5.6.2 (see Testcase 3.5.1.1);
1345
--source include/show_msg.inc
1348
# Test case: Ensure that a trigger definition that specifies a trigger action
1349
# time that is not either BEFORE or AFTER fails, with an appropriate
1350
# error message, at CREATE TRIGGER time.
1351
let $message= Testcase 3.5.6.3:;
1352
--source include/show_msg.inc
1355
Create trigger trg3_1 DURING UPDATE on tb3 for each row set new.f132=25;
1357
Create trigger trg3_2 TIME INSERT on tb3 for each row set new.f132=15;
1360
# OBN - Although none of the above should have been created we should do a cleanup
1361
# since if they have been created, not dropping them will affect following
1365
drop trigger tb3.trg3_1;
1367
drop trigger tb3.trg3_2;
1371
# Test case: Ensure that a trigger defined with a trigger action time of BEFORE
1372
# always executes its triggered action immediately before the trigger event.
1373
# See section 3.5.1.1
1374
let $message= Testcase 3.5.6.4 (see Testcase 3.5.1.1);
1375
--source include/show_msg.inc
1378
# Test case: Ensure that a trigger defined with a trigger action time of AFTER
1379
# always executes its triggered action immediately after the trigger event.
1380
let $message= Testcase 3.5.6.5 (see Testcase 3.5.1.1);
1381
--source include/show_msg.inc
1383
#############################
1384
####### Section 3.5.7 #######
1385
# Check on Trigger Event #
1386
#############################
1389
#Test case: Ensure that a trigger definition can specify a trigger event of INSERT.
1390
let $message= Testcase 3.5.7.1 (see Testcase 3.5.1.1);
1391
--source include/show_msg.inc
1394
# Test case: Ensure that a trigger definition can specify a trigger event of UPDATE.
1395
let $message= Testcase 3.5.7.2 (see Testcase 3.5.1.1);
1396
--source include/show_msg.inc
1399
# Test case: Ensure that a trigger definition can specify a trigger event of DELETE.
1400
let $message= Testcase 3.5.7.3 (see Testcase 3.5.1.1);
1401
--source include/show_msg.inc
1404
# Test case: Ensure that a trigger definition that specifies a trigger event that
1405
# is not either INSERT, UPDATE or DELETE fails, with an appropriate error
1406
# message, at CREATE TRIGGER time.
1407
let $message= Testcase 3.5.7.4:;
1408
--source include/show_msg.inc
1411
Create trigger trg4_1 BEFORE SELECT on tb3 for each row set new.f132=5;
1413
Create trigger trg4_2 AFTER VALUE on tb3 for each row set new.f132=1;
1416
# OBN - Although none of the above should have been created we should do a cleanup
1417
# since if they have been created, not dropping them will affect following
1421
drop trigger tb3.trg4_1;
1423
drop trigger tb3.trg4_2;
1426
#Section 3.5.7.5 / 3.5.7.6
1427
# Test case: Ensure that it is not possible to create multiple BEFORE INSERT triggers
1428
# on the same table, even if the triggers have different names / different
1429
# triggered actions.
1430
let $message= Testcase 3.5.7.5 / 3.5.7.6:;
1431
--source include/show_msg.inc
1433
Create trigger trg5_1 BEFORE INSERT
1434
on tb3 for each row set new.f122='Trigger1 3.5.7.5/6';
1437
Create trigger trg5_2 BEFORE INSERT
1438
on tb3 for each row set new.f122='Trigger2 3.5.7.5';
1440
Insert into tb3 (f121,f122) values ('Test 3.5.7.5/6','Insert 3.5.7.5');
1441
Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
1442
update tb3 set f122='Update 3.5.7.6' where f121= 'Test 3.5.7.5/6';
1443
Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
1447
drop trigger trg5_1;
1449
drop trigger trg5_2;
1450
delete from tb3 where f121='Test 3.5.7.5/6';
1454
#Section 3.5.7.7 / 3.5.7.8
1455
# Test case: Ensure that it is not possible to create multiple AFTER INSERT triggers
1456
# on the same table, even if the triggers have different names / different
1457
# triggered actions.
1458
let $message= Testcase 3.5.7.7 / 3.5.7.8:;
1459
--source include/show_msg.inc
1461
set @test_var='Before trig 3.5.7.7';
1462
Create trigger trg6_1 AFTER INSERT
1463
on tb3 for each row set @test_var='Trigger1 3.5.7.7/8';
1466
Create trigger trg6_2 AFTER INSERT
1467
on tb3 for each row set @test_var='Trigger2 3.5.7.7';
1470
Insert into tb3 (f121,f122) values ('Test 3.5.7.7/8','Insert 3.5.7.7');
1471
Select f121,f122 from tb3 where f121='Test 3.5.7.7/8';
1473
update tb3 set f122='Update 3.5.7.8' where f121= 'Test 3.5.7.7/8';
1474
Select f121,f122 from tb3 where f121='Test 3.5.7.7/8';
1479
drop trigger trg6_1;
1481
drop trigger trg6_2;
1482
delete from tb3 where f121='Test 3.5.7.7/8';
1486
#Section 3.5.7.9 / 3.5.7.10
1487
# Test case: Ensure that it is not possible to create multiple BEFORE UPDATE triggers
1488
# on the same table, even if the triggers have different names / different
1489
# triggered actions.
1490
let $message= Testcase 3.5.7.9/10:;
1491
--source include/show_msg.inc
1493
Create trigger trg7_1 BEFORE UPDATE
1494
on tb3 for each row set new.f122='Trigger1 3.5.7.9/10';
1497
Create trigger trg7_2 BEFORE UPDATE
1498
on tb3 for each row set new.f122='Trigger2 3.5.7.9';
1500
Insert into tb3 (f121,f122) values ('Test 3.5.7.9/10','Insert 3.5.7.9');
1501
Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
1502
update tb3 set f122='update 3.5.7.10' where f121='Test 3.5.7.9/10';
1503
Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
1507
drop trigger trg7_1;
1509
drop trigger trg7_2;
1510
delete from tb3 where f121='Test 3.5.7.9/10';
1512
#Section 3.5.7.11 / 3.5.7.12
1513
# Test case: Ensure that it is not possible to create multiple AFTER UPDATE triggers
1514
# on the same table, even if the triggers have different names / different
1515
# triggered actions.
1516
let $message= Testcase 3.5.7.11/12:;
1517
--source include/show_msg.inc
1519
set @test_var='Before trig 3.5.7.11';
1520
Create trigger trg8_1 AFTER UPDATE
1521
on tb3 for each row set @test_var='Trigger 3.5.7.11/12';
1524
Create trigger trg8_2 AFTER UPDATE
1525
on tb3 for each row set @test_var='Trigger2 3.5.7.11';
1529
Insert into tb3 (f121,f122) values ('Test 3.5.7.11/12','Insert 3.5.7.11/12');
1531
Select f121,f122 from tb3 where f121='Test 3.5.7.11/12';
1532
update tb3 set f122='update 3.5.7.12' where f121='Test 3.5.7.11/12';
1533
Select f121,f122 from tb3 where f121='Test 3.5.7.11/12';
1535
delete from tb3 where f121='Test 3.5.7.11/12';
1539
drop trigger trg8_1;
1541
drop trigger trg8_2;
1542
delete from tb3 where f121='Test 3.5.7.11/12';
1544
#Section 3.5.7.13 / 3.5.7.14
1545
# Test case: Ensure that it is not possible to create multiple BEFORE DELETE triggers
1546
# on the same table, even if the triggers have different names / different
1547
# triggered actions.
1548
let $message= Testcase 3.5.7.13/14:;
1549
--source include/show_msg.inc
1552
Create trigger trg9_1 BEFORE DELETE
1553
on tb3 for each row set @test_var=@test_var+1;
1556
Create trigger trg9_2 BEFORE DELETE
1557
on tb3 for each row set @test_var=@test_var+10;
1560
Insert into tb3 (f121,f122) values ('Test 3.5.7.13/14','Insert 3.5.7.13');
1561
Select f121,f122 from tb3 where f121='Test 3.5.7.13/14';
1563
delete from tb3 where f121='Test 3.5.7.13/14';
1564
Select f121,f122 from tb3 where f121='Test 3.5.7.13/14';
1566
delete from tb3 where f121='Test 3.5.7.13/14';
1571
drop trigger trg9_1;
1573
drop trigger trg9_2;
1574
delete from tb3 where f121='Test 3.5.7.13/14';
1576
#Section 3.5.7.15 / 3.5.7.16
1577
# Test case: Ensure that it is not possible to create multiple AFTER DELETE triggers
1578
# on the same table, even if the triggers have different names / different
1579
# triggered actions.
1580
let $message= Testcase 3.5.7.15/16:;
1581
--source include/show_msg.inc
1584
Create trigger trg_3_406010_1 AFTER DELETE
1585
on tb3 for each row set @test_var=@test_var+5;
1588
Create trigger trg_3_406010_2 AFTER DELETE
1589
on tb3 for each row set @test_var=@test_var+50;
1592
Create trigger trg_3_406010_1 AFTER INSERT
1593
on tb3 for each row set @test_var=@test_var+1;
1596
Insert into tb3 (f121,f122) values ('Test 3.5.7.15/16','Insert 3.5.7.15/16');
1597
Select f121,f122 from tb3 where f121='Test 3.5.7.15/16';
1599
delete from tb3 where f121='Test 3.5.7.15/16';
1600
Select f121,f122 from tb3 where f121='Test 3.5.7.15/16';
1602
delete from tb3 where f121='Test 3.5.7.15/16';
1607
drop trigger trg_3_406010_1;
1609
drop trigger trg_3_406010_2;
1610
delete from tb3 where f121='Test 3.5.7.15/16';
1615
# Test case: Ensure that it is possible to have a BEFORE INSERT, an AFTER INSERT,
1616
# a BEFORE UPDATE, an AFTER UPDATE, a BEFORE DELETE, and an AFTER DELETE
1617
# trigger on the same table; that is, ensure that every persistent base
1618
# table may be the subject table for exactly six triggers
1619
let $message= Testcase 3.5.7.17 (see Testcase 3.5.1.1);
1620
--source include/show_msg.inc
1623
#################################
1624
####### Section 3.5.8 ###########
1625
# Checks on Triggered Actions #
1626
#################################
1629
# Testcase: Ensure that the triggered action of every trigger always executes
1630
# correctly and the results in all expected changes made to the database
1631
let $message= Testcase 3.5.8.1: (implied in previous tests);
1632
--source include/show_msg.inc
1633
# OBN - FIXME - Missing 3.5.8.1 need to add
1636
# Testcase: Ensure that the triggered actions of every trigger never results
1637
# in an unexpected change made to the database.
1638
let $message= Testcase 3.5.8.2: (implied in previous tests);
1639
--source include/show_msg.inc
1642
#Section 3.5.8.3 / 3.5.8.4
1643
#Test case: Ensure that the triggered action can any valid SQL statement / set
1644
# of valid SQL statements, provided the statements are written within
1645
# a BEGIN/END compound statement construct
1646
# OBN - At this point the tests focuses on the the INSERT/UPDATE/DELETE SQL statements
1647
# as there are the most likely to be used in triggers
1648
let $message= Testcase 3.5.8.3/4:;
1649
--source include/show_msg.inc
1651
# creating test tables to perform the trigger SQL on
1652
connection con_super;
1653
create database db_test;
1654
grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
1655
grant LOCK TABLES on db_test.* to test_general;
1657
eval create table t1_i (
1658
i120 char ascii not null DEFAULT b'101',
1659
i136 smallint zerofill not null DEFAULT 999,
1660
i144 int zerofill not null DEFAULT 99999,
1661
i163 decimal (63,30)) engine=$engine_type;
1662
eval create table t1_u (
1663
u120 char ascii not null DEFAULT b'101',
1664
u136 smallint zerofill not null DEFAULT 999,
1665
u144 int zerofill not null DEFAULT 99999,
1666
u163 decimal (63,30)) engine=$engine_type;
1667
eval create table t1_d (
1668
d120 char ascii not null DEFAULT b'101',
1669
d136 smallint zerofill not null DEFAULT 999,
1670
d144 int zerofill not null DEFAULT 99999,
1671
d163 decimal (63,30)) engine=$engine_type;
1672
Insert into t1_u values ('a',111,99999,999.99);
1673
Insert into t1_u values ('b',222,99999,999.99);
1674
Insert into t1_u values ('c',333,99999,999.99);
1675
Insert into t1_u values ('d',222,99999,999.99);
1676
Insert into t1_u values ('e',222,99999,999.99);
1677
Insert into t1_u values ('f',333,99999,999.99);
1678
Insert into t1_d values ('a',111,99999,999.99);
1679
Insert into t1_d values ('b',222,99999,999.99);
1680
Insert into t1_d values ('c',333,99999,999.99);
1681
Insert into t1_d values ('d',444,99999,999.99);
1682
Insert into t1_d values ('e',222,99999,999.99);
1683
Insert into t1_d values ('f',222,99999,999.99);
1685
let $message= 3.5.8.4 - multiple SQL;
1686
--source include/show_msg.inc
1687
# Trigger definition - multiple SQL
1690
Create trigger trg1 AFTER INSERT on tb3 for each row
1692
insert into db_test.t1_i
1693
values (new.f120, new.f136, new.f144, new.f163);
1695
set u144=new.f144, u163=new.f163
1696
where u136=new.f136;
1697
delete from db_test.t1_d where d136= new.f136;
1698
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
1699
where u136= new.f136;
1703
# Test trigger execution - multiple SQL
1704
connection con_general;
1707
Insert into tb3 (f120, f122, f136, f144, f163)
1708
values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
1709
Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
1710
select * from db_test.t1_i;
1711
select * from db_test.t1_u;
1712
select * from db_test.t1_d;
1716
let $message= 3.5.8.4 - single SQL - insert;
1717
--source include/show_msg.inc
1718
# Trigger definition - single SQL Insert
1719
connection con_super;
1720
Create trigger trg2 BEFORE UPDATE on tb3 for each row
1721
insert into db_test.t1_i
1722
values (new.f120, new.f136, new.f144, new.f163);
1724
# Trigger exeution - single SQL Insert
1725
connection con_general;
1726
update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
1727
where f122='Test 3.5.8.4';
1728
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
1729
select * from db_test.t1_i;
1732
let $message= 3.5.8.4 - single SQL - update;
1733
--source include/show_msg.inc
1734
# Trigger definition - single SQL update
1735
connection con_super;
1737
Create trigger trg3 BEFORE UPDATE on tb3 for each row
1740
where u136=new.f136;
1742
# Trigger exeution - single SQL - update;
1743
connection con_general;
1744
update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
1745
where f122='Test 3.5.8.4-Single Insert';
1746
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
1747
select * from db_test.t1_u;
1750
let $message= 3.5.8.3/4 - single SQL - delete;
1751
--source include/show_msg.inc
1752
# Trigger definition - single SQL delete
1753
connection con_super;
1755
Create trigger trg4 AFTER UPDATE on tb3 for each row
1756
delete from db_test.t1_d where d136= new.f136;
1758
# Trigger exeution - single SQL delete
1759
connection con_general;
1760
#lock tables tb3 write, db_test.t1_i write, db_test.t1_u write, db_test.t1_d write;
1761
update tb3 set f120='D', f136=444,
1762
f122='Test 3.5.8.4-Single Delete'
1763
where f122='Test 3.5.8.4-Single Update';
1765
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
1766
select * from db_test.t1_d;
1769
let $message= 3.5.8.3/4 - single SQL - select;
1770
--source include/show_msg.inc
1771
# Trigger definition - single SQL select
1772
connection con_super;
1774
Create trigger trg5 AFTER UPDATE on tb3 for each row
1775
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
1776
where u136= new.f136;
1778
# Trigger exeution - single SQL select
1779
connection con_general;
1781
update tb3 set f120='S', f136=111,
1782
f122='Test 3.5.8.4-Single Select'
1783
where f122='Test 3.5.8.4-Single Delete';
1784
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
1792
drop database if exists db_test;
1793
delete from tb3 where f122 like 'Test 3.5.8.4%';
1794
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
1798
#Section 3.5.8.5 (IF)
1799
# Test case: Ensure that the stored procedure-specific flow control statement like IF
1800
# works correctly when it is a part of the triggered action portion of a
1801
# trigger definition.
1802
let $message= Testcase 3.5.8.5 (IF):;
1803
--source include/show_msg.inc
1806
create trigger trg2 before insert on tb3 for each row
1808
IF new.f120='1' then
1809
set @test_var='one', new.f120='2';
1810
ELSEIF new.f120='2' then
1811
set @test_var='two', new.f120='3';
1812
ELSEIF new.f120='3' then
1813
set @test_var='three', new.f120='4';
1816
IF (new.f120='4') and (new.f136=10) then
1817
set @test_var2='2nd if', new.f120='d';
1819
set @test_var2='2nd else', new.f120='D';
1824
set @test_var='Empty', @test_var2=0;
1825
Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
1826
select f120, f122, f136, @test_var, @test_var2
1827
from tb3 where f122 = 'Test 3.5.8.5-if';
1828
Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
1829
select f120, f122, f136, @test_var, @test_var2
1830
from tb3 where f122 = 'Test 3.5.8.5-if';
1831
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
1832
select f120, f122, f136, @test_var, @test_var2
1833
from tb3 where f122 = 'Test 3.5.8.5-if';
1834
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
1835
select f120, f122, f136, @test_var, @test_var2
1836
from tb3 where f122 = 'Test 3.5.8.5-if';
1840
create trigger trg3 before update on tb3 for each row
1842
ELSEIF new.f120='2' then
1849
create trigger trg4 before update on tb3 for each row
1851
IF (new.f120='4') and (new.f136=10) then
1852
set @test_var2='2nd if', new.f120='d';
1854
set @test_var2='2nd else', new.f120='D';
1863
delete from tb3 where f121='Test 3.5.8.5-if';
1867
#Section 3.5.8.5 (CASE)
1868
# Test case: Ensure that the stored procedure-specific flow control statement
1869
# like CASE works correctly when it is a part of the triggered action
1870
# portion of a trigger definition.
1871
let $message= Testcase 3.5.8.5-case:;
1872
--source include/show_msg.inc
1875
create trigger trg3 before insert on tb3 for each row
1877
SET new.f120=char(ascii(new.f120)-32);
1879
when new.f136<100 then set new.f136=new.f136+120;
1880
when new.f136<10 then set new.f144=777;
1881
when new.f136>100 then set new.f120=new.f136-1;
1884
when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
1885
ELSE set @test_var=concat(new.f120, '*');
1888
when 1 then set @test_var=concat(@test_var, 'one');
1889
when 2 then set @test_var=concat(@test_var, 'two');
1890
when 3 then set @test_var=concat(@test_var, 'three');
1891
when 4 then set @test_var=concat(@test_var, 'four');
1892
when 5 then set @test_var=concat(@test_var, 'five');
1893
when 6 then set @test_var=concat(@test_var, 'six');
1894
when 7 then set @test_var=concat(@test_var, 'seven');
1895
when 8 then set @test_var=concat(@test_var, 'eight');
1896
when 9 then set @test_var=concat(@test_var, 'nine');
1897
when 10 then set @test_var=concat(@test_var, 'ten');
1898
when 11 then set @test_var=concat(@test_var, 'eleven');
1899
when 12 then set @test_var=concat(@test_var, 'twelve');
1900
when 13 then set @test_var=concat(@test_var, 'thirteen');
1901
when 14 then set @test_var=concat(@test_var, 'fourteen');
1902
when 15 then set @test_var=concat(@test_var, 'fifteen');
1903
ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
1908
set @test_var='Empty';
1909
Insert into tb3 (f120, f122, f136, f144)
1910
values ('a', 'Test 3.5.8.5-case', 5, 7);
1911
select f120, f122, f136, f144, @test_var
1912
from tb3 where f122 = 'Test 3.5.8.5-case';
1913
Insert into tb3 (f120, f122, f136, f144)
1914
values ('b', 'Test 3.5.8.5-case', 71,16);
1915
select f120, f122, f136, f144, @test_var
1916
from tb3 where f122 = 'Test 3.5.8.5-case';
1917
Insert into tb3 (f120, f122, f136, f144)
1918
values ('c', 'Test 3.5.8.5-case', 80,1);
1919
select f120, f122, f136, f144, @test_var
1920
from tb3 where f122 = 'Test 3.5.8.5-case';
1921
Insert into tb3 (f120, f122, f136)
1922
values ('d', 'Test 3.5.8.5-case', 152);
1923
select f120, f122, f136, f144, @test_var
1924
from tb3 where f122 = 'Test 3.5.8.5-case';
1925
Insert into tb3 (f120, f122, f136, f144)
1926
values ('e', 'Test 3.5.8.5-case', 200, 8);
1927
select f120, f122, f136, f144, @test_var
1928
from tb3 where f122 = 'Test 3.5.8.5-case';
1930
Insert into tb3 (f120, f122, f136, f144)
1931
values ('f', 'Test 3.5.8.5-case', 100, 8);
1932
select f120, f122, f136, f144, @test_var
1933
from tb3 where f122 = 'Test 3.5.8.5-case';
1937
create trigger trg3a before update on tb3 for each row
1940
when new.f136<100 then set new.f120='p';
1950
delete from tb3 where f121='Test 3.5.8.5-case';
1953
#Section 3.5.8.5 (LOOP)
1954
# Test case: Ensure that the stored procedure-specific flow control
1955
# statement like LOOP / LEAVE work correctly when they are
1956
# part of the triggered action portion of a trigger definition.
1957
let $message= Testcase 3.5.8.5-loop/leave:;
1958
--source include/show_msg.inc
1961
Create trigger trg4 after insert on tb3 for each row
1963
set @counter=0, @flag='Initial';
1965
if new.f136<new.f144 then
1966
set @counter='Nothing to loop';
1969
set @counter=@counter+1;
1970
if new.f136=new.f144+@counter then
1971
set @counter=concat(@counter, ' loops');
1980
Insert into tb3 (f122, f136, f144)
1981
values ('Test 3.5.8.5-loop', 2, 8);
1982
select @counter, @flag;
1983
Insert into tb3 (f122, f136, f144)
1984
values ('Test 3.5.8.5-loop', 11, 8);
1985
select @counter, @flag;
1991
Create trigger trg4_2 after update on tb3 for each row
1994
set @counter=@counter+1;
1999
drop trigger trg4_2;
2004
delete from tb3 where f122='Test 3.5.8.5-loop';
2007
#Section 3.5.8.5 (REPEAT ITERATE)
2008
#Testcase: Ensure that the stored procedure-specific flow control statements
2009
# like REPEAT work correctly when they are part of the triggered action
2010
# portion of a trigger definition.
2011
let $message= Testcase 3.5.8.5-repeat:;
2012
--source include/show_msg.inc
2015
Create trigger trg6 after insert on tb3 for each row
2018
SET @counter1 = @counter1 + 1;
2019
IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label;
2021
SET @counter2 = @counter2 + 1;
2022
UNTIL @counter1> new.f136 END REPEAT rp_label;
2026
set @counter1= 0, @counter2= 0;
2027
Insert into tb3 (f122, f136)
2028
values ('Test 3.5.8.5-repeat', 13);
2029
select @counter1, @counter2;
2034
Create trigger trg6_2 after update on tb3 for each row
2037
SET @counter2 = @counter2 + 1;
2044
delete from tb3 where f122='Test 3.5.8.5-repeat';
2048
#Section 3.5.8.5 (WHILE)
2049
# Test case: Ensure that the stored procedure-specific flow control
2050
# statements WHILE, work correctly when they are part of
2051
# the triggered action portion of a trigger definition.
2052
let $message= Testcase 3.5.8.5-while:;
2053
--source include/show_msg.inc
2056
Create trigger trg7 after insert on tb3 for each row
2057
wl_label: WHILE @counter1 < new.f136 DO
2058
SET @counter1 = @counter1 + 1;
2059
IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label;
2061
SET @counter2 = @counter2 + 1;
2062
END WHILE wl_label//
2065
set @counter1= 0, @counter2= 0;
2066
Insert into tb3 (f122, f136)
2067
values ('Test 3.5.8.5-while', 7);
2068
select @counter1, @counter2;
2071
Create trigger trg7_2 after update on tb3 for each row
2073
WHILE @counter1 < new.f136
2074
SET @counter1 = @counter1 + 1;
2080
delete from tb3 where f122='Test 3.5.8.5-while';
2085
# Test case: Ensure that a trigger definition that includes a CALL to a stored
2086
# procedure fails, at CREATE TRIGGER time, with an appropriate error
2088
# OBN - requirement void since allowed
2089
# Fails due to Bug 9909 the bug allows the trigger to be created
2090
# and fails in execution time
2091
let $message= Testcase 3.5.8.6: (requirement void);
2092
--source include/show_msg.inc
2096
# Test case: Ensure that a trigger definition that includes a
2097
# transaction-delimiting statement (e.g. COMMIT,
2098
# ROLLBACK, START TRANSACTION) fails, at CREATE TRIGGER
2099
# time, with an appropriate error message.
2100
# OBN - Fails due to Bug ____
2101
let $message= Testcase 3.5.8.7: (Disabled as a result of bug _____);
2102
--source include/show_msg.inc
2105
# Create trigger trg9_1 before update on tb3 for each row
2107
# Start transaction;
2113
# Create trigger trg9_2 before delete on tb3 for each row
2115
# Start transaction;
2116
# Set @var2=old.f120;
2120
#################################
2121
####### Section 3.5.9 ###########
2122
# Checks on old and new rows #
2123
#################################
2126
#Test case: Ensure that every trigger executes its triggered action on each row
2127
# that meets the conditions stated in the trigger definition.
2129
#Testcase: Ensure that a trigger never executes its triggered action on any row
2130
# that doesn't meet the conditions stated in the trigger definition.
2131
let $message= Testcase 3.5.9.1/2:;
2132
--source include/show_msg.inc
2134
Create trigger trg1 BEFORE UPDATE on tb3 for each row
2135
set new.f142 = 94087, @counter=@counter+1;
2137
select count(*) as TotalRows from tb3;
2138
select count(*) as Affected from tb3 where f130<100;
2139
select count(*) as NotAffected from tb3 where f130>=100;
2140
select count(*) as NewValuew from tb3 where f142=94087;
2143
Update tb3 Set f142='1' where f130<100;
2144
select count(*) as ExpectedChanged, @counter as TrigCounter
2145
from tb3 where f142=94087;
2146
select count(*) as ExpectedNotChange from tb3
2147
where f130<100 and f142<>94087;
2148
select count(*) as NonExpectedChanged from tb3
2149
where f130>=130 and f142=94087;
2158
#Test case: Ensure that a reference to OLD.<column name> always correctly refers
2159
# to the values of the specified column of the subject table before a
2160
# data row is updated or deleted.
2161
let $message= Testcase 3.5.9.3:;
2162
--source include/show_msg.inc
2164
Create trigger trg2_a before update on tb3 for each row
2165
set @tr_var_b4_118=old.f118, @tr_var_b4_121=old.f121,
2166
@tr_var_b4_122=old.f122, @tr_var_b4_136=old.f136,
2167
@tr_var_b4_163=old.f163;
2169
Create trigger trg2_b after update on tb3 for each row
2170
set @tr_var_af_118=old.f118, @tr_var_af_121=old.f121,
2171
@tr_var_af_122=old.f122, @tr_var_af_136=old.f136,
2172
@tr_var_af_163=old.f163;
2174
Create trigger trg2_c before delete on tb3 for each row
2175
set @tr_var_b4_118=old.f118, @tr_var_b4_121=old.f121,
2176
@tr_var_b4_122=old.f122, @tr_var_b4_136=old.f136,
2177
@tr_var_b4_163=old.f163;
2179
Create trigger trg2_d after delete on tb3 for each row
2180
set @tr_var_af_118=old.f118, @tr_var_af_121=old.f121,
2181
@tr_var_af_122=old.f122, @tr_var_af_136=old.f136,
2182
@tr_var_af_163=old.f163;
2186
set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0,
2187
@tr_var_b4_136=0, @tr_var_b4_163=0;
2188
set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0,
2189
@tr_var_af_136=0, @tr_var_af_163=0;
2190
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
2191
@tr_var_b4_136, @tr_var_b4_163;
2192
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
2193
@tr_var_af_136, @tr_var_af_163;
2196
Insert into tb3 (f122, f136, f163)
2197
values ('Test 3.5.9.3', 7, 123.17);
2198
Update tb3 Set f136=8 where f122='Test 3.5.9.3';
2200
select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3';
2201
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
2202
@tr_var_b4_136, @tr_var_b4_163;
2203
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
2204
@tr_var_af_136, @tr_var_af_163;
2207
set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0,
2208
@tr_var_b4_136=0, @tr_var_b4_163=0;
2209
set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0,
2210
@tr_var_af_136=0, @tr_var_af_163=0;
2211
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
2212
@tr_var_b4_136, @tr_var_b4_163;
2213
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
2214
@tr_var_af_136, @tr_var_af_163;
2217
delete from tb3 where f122='Test 3.5.9.3';
2219
select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3';
2220
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
2221
@tr_var_b4_136, @tr_var_b4_163;
2222
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
2223
@tr_var_af_136, @tr_var_af_163;
2226
drop trigger trg2_a;
2227
drop trigger trg2_b;
2228
drop trigger trg2_c;
2229
drop trigger trg2_d;
2233
#Test case: Ensure that a reference to NEW.<column name> always correctly refers
2234
# to the values of the specified column of the subject table after an
2235
# existing data row has been updated or a new data row has been inserted.
2236
let $message= Testcase 3.5.9.4:;
2237
--source include/show_msg.inc
2239
Create trigger trg3_a before insert on tb3 for each row
2240
set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121,
2241
@tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136,
2242
@tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163;
2244
Create trigger trg3_b after insert on tb3 for each row
2245
set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121,
2246
@tr_var_af_122=new.f122, @tr_var_af_136=new.f136,
2247
@tr_var_af_151=new.f151, @tr_var_af_163=new.f163;
2249
Create trigger trg3_c before update on tb3 for each row
2250
set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121,
2251
@tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136,
2252
@tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163;
2254
Create trigger trg3_d after update on tb3 for each row
2255
set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121,
2256
@tr_var_af_122=new.f122, @tr_var_af_136=new.f136,
2257
@tr_var_af_151=new.f151, @tr_var_af_163=new.f163;
2260
set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0,
2261
@tr_var_b4_136=0, @tr_var_b4_151=0, @tr_var_b4_163=0;
2262
set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0,
2263
@tr_var_af_136=0, @tr_var_af_151=0, @tr_var_af_163=0;
2264
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
2265
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
2266
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
2267
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
2270
Insert into tb3 (f122, f136, f151, f163)
2271
values ('Test 3.5.9.4', 7, DEFAULT, 995.24);
2273
select f118, f121, f122, f136, f151, f163 from tb3
2274
where f122 like 'Test 3.5.9.4%';
2275
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
2276
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
2277
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
2278
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
2281
set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0,
2282
@tr_var_b4_136=0, @tr_var_b4_151=0, @tr_var_b4_163=0;
2283
set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0,
2284
@tr_var_af_136=0, @tr_var_af_151=0, @tr_var_af_163=0;
2285
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
2286
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
2287
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
2288
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
2291
Update tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL
2292
where f122='Test 3.5.9.4';
2294
select f118, f121, f122, f136, f151, f163 from tb3
2295
where f122 like 'Test 3.5.9.4-trig';
2296
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
2297
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
2298
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
2299
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
2302
drop trigger trg3_a;
2303
drop trigger trg3_b;
2304
drop trigger trg3_c;
2305
drop trigger trg3_d;
2306
delete from tb3 where f122='Test 3.5.9.4-trig';
2311
# Test case: Ensure that the definition of an INSERT trigger can include a
2312
# reference to NEW. <Column name>.
2313
let $message= Testcase 3.5.9.5: (implied in previous tests);
2314
--source include/show_msg.inc
2317
# Test case: Ensure that the definition of an INSERT trigger cannot include
2318
# a reference to OLD. <Column name>.
2319
let $message= Testcase 3.5.9.6:;
2320
--source include/show_msg.inc
2323
create trigger trg4a before insert on tb3 for each row
2324
set @temp1= old.f120;
2326
create trigger trg4b after insert on tb3 for each row
2327
set old.f120= 'test';
2339
# Test case: Ensure that the definition of an UPDATE trigger can include a
2340
# reference to NEW. <Column name>.
2341
let $message= Testcase 3.5.9.7: (implied in previous tests);
2342
--source include/show_msg.inc
2345
# Test case: Ensure that the definition of an UPDATE trigger cannot include a
2346
# reference to OLD. <Column name>.
2347
let $message= Testcase 3.5.9.8: (implied in previous tests);
2348
--source include/show_msg.inc
2351
# Test case: Ensure that the definition of a DELETE trigger cannot include a
2352
# reference to NEW.<column name>.
2353
let $message= Testcase 3.5.9.9:;
2354
--source include/show_msg.inc
2357
create trigger trg5a before DELETE on tb3 for each row
2358
set @temp1=new.f122;
2360
create trigger trg5b after DELETE on tb3 for each row
2361
set new.f122='test';
2362
let $message= The above returns the wrong error, should be error 1362 (Bug 11648)
2363
--source include/show_msg.inc
2375
# Test case: Ensure that the definition of a DELETE trigger can include a reference
2376
# to OLD.<column name>.
2377
let $message= Testcase 3.5.9.10: (implied in previous tests);
2378
--source include/show_msg.inc
2382
# Testcase: Ensure that trigger definition that includes a referance to
2383
# NEW.<colunm name> fails with an appropriate error message,
2384
# at CREATE TRIGGER time, if the trigger event in not INSERT or UPDATE
2385
let $message= Testcase 3.5.9.11: covered by 3.5.9.9;
2386
--source include/show_msg.inc
2390
# Testcase: Ensure that trigger definition that includes a referance to
2391
# OLD.<column name> fails with an appropriate error message, at
2392
# CREATE TRIGGER time, if the trigger event is not DELETE or UPDATE
2393
let $message= Testcase 3.5.9.12: covered by 3.5.9.6;
2394
--source include/show_msg.inc
2398
# Test case: Ensure that all references to OLD. <Column name> are read-only,
2399
# that is, that they cannot be used to modify a data row.
2400
let $message= Testcase 3.5.9.13:;
2401
--source include/show_msg.inc
2404
create trigger trg6a before UPDATE on tb3 for each row
2405
set old.f118='C', new.f118='U';
2407
create trigger trg6b after INSERT on tb3 for each row
2408
set old.f136=163, new.f118='U';
2410
create trigger trg6c after UPDATE on tb3 for each row
2425
# Test case: Ensure that all references to NEW. <Column name> may be used both to
2426
# read a data row and to modify a data row
2427
let $message= Testcase 3.5.9.14: (implied in previous tests);
2428
--source include/show_msg.inc
2431
##############################################
2432
################ Section 3.5.10 #################
2433
# Check on Trigger Activation
2434
##############################################
2436
# Test case: Ensure that every trigger that should be activated by
2437
# every possible type of implicit insertion into its subject
2438
# table (INSERT into a view based on the subject table) is
2439
# indeed activated correctly
2441
# Test case: Ensure that every trigger that should be activated by every
2442
# possible type of implicit insertion into its subject table
2443
# (UPDATE into a view based on the subject table) is indeed
2444
# activated correctly
2446
# Test case: Ensure that every trigger that should be activated by every
2447
# possible type of implicit insertion into its subject table
2448
# (DELETE from a view based on the subject table) is indeed
2449
# activated correctly
2450
let $message= Testcase 3.5.10.1/2/3:;
2451
--source include/show_msg.inc
2453
Create view vw11 as select * from tb3
2454
where f122 like 'Test 3.5.10.1/2/3%';
2455
Create trigger trg1a before insert on tb3
2456
for each row set new.f163=111.11;
2457
Create trigger trg1b after insert on tb3
2458
for each row set @test_var='After Insert';
2459
Create trigger trg1c before update on tb3
2460
for each row set new.f121='Y', new.f122='Test 3.5.10.1/2/3-Update';
2461
Create trigger trg1d after update on tb3
2462
for each row set @test_var='After Update';
2463
Create trigger trg1e before delete on tb3
2464
for each row set @test_var=5;
2465
Create trigger trg1f after delete on tb3
2466
for each row set @test_var= 2* @test_var+7;
2469
Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 1);
2470
Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 2);
2471
Insert into vw11 (f122, f151) values ('Not in View', 3);
2472
select f121, f122, f151, f163
2473
from tb3 where f122 like 'Test 3.5.10.1/2/3%';
2474
select f121, f122, f151, f163 from vw11;
2475
select f121, f122, f151, f163
2476
from tb3 where f122 like 'Not in View';
2479
Update vw11 set f163=1;
2480
select f121, f122, f151, f163 from tb3
2481
where f122 like 'Test 3.5.10.1/2/3%';
2482
select f121, f122, f151, f163 from vw11;
2486
Select @test_var as 'before delete';
2487
delete from vw11 where f151=1;
2488
select f121, f122, f151, f163 from tb3
2489
where f122 like 'Test 3.5.10.1/2/3%';
2490
select f121, f122, f151, f163 from vw11;
2491
Select @test_var as 'after delete';
2502
delete from tb3 where f122 like 'Test 3.5.10.1/2/3%';
2507
# Test case: Ensure that every trigger that should be activated by every
2508
# possible type of implicit insertion into its subject table
2509
# (LOAD into the subject table) is indeed activated correctly
2510
let $message= Testcase 3.5.10.4:;
2511
--source include/show_msg.inc
2513
eval create table tb_load (f1 int, f2 char(25),f3 int) engine=$engine_type;
2514
Create trigger trg4 before insert on tb_load
2515
for each row set new.f3=-(new.f1 div 5), @counter= @counter+1;
2518
select @counter as 'Rows Loaded Before';
2519
--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
2520
eval load data infile '$MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table tb_load;
2522
select @counter as 'Rows Loaded After';
2523
Select * from tb_load limit 10;
2533
# Testcase: Ensure that every trigger that should be activated by every possible
2534
# type of implicit update of its subject table (e.g.a FOREIGN KEY SET
2535
# DEFAULT action or an UPDATE of a view based on the subject table) is
2536
# indeed activated correctly
2537
let $message= Testcase 3.5.10.5: (implemented in trig_frkey.test);
2538
--source include/show_msg.inc
2542
# Testcase: Ensure that every trigger that should be activated by every possible
2543
# type of implicit deletion from its subject table (e.g.a FOREIGN KEY
2544
# CASCADE action or a DELETE from a view based on the subject table) is
2545
# indeed activated correctly
2546
let $message= Testcase 3.5.10.6: (implemented in trig_frkey.test);
2547
--source include/show_msg.inc
2549
#Section 3.5.10.extra
2550
# Testcase: Ensure that every trigger that should be activated by every possible
2551
# type of implicit deletion from its subject table (e.g. an action performed
2552
# on the subject table from a stored procedure is indeed activated correctly
2553
let $message= Testcase 3.5.10.extra:;
2554
--source include/show_msg.inc
2556
eval create table t1_sp (var136 tinyint, var151 decimal) engine=$engine_type;
2558
create trigger trg before insert on t1_sp
2559
for each row set @counter=@counter+1;
2560
# declare continue handler for sqlstate '01000' set done = 1;
2563
create procedure trig_sp()
2565
declare done int default 0;
2566
declare var151 decimal;
2567
declare var136 tinyint;
2568
declare cur1 cursor for select f136, f151 from tb3;
2569
declare continue handler for sqlstate '01000' set done = 1;
2571
fetch cur1 into var136, var151;
2572
wl_loop: WHILE NOT done DO
2573
insert into t1_sp values (var136, var151);
2574
fetch cur1 into var136, var151;
2585
select count(*) from tb3;
2586
select count(*) from t1_sp;
2590
drop procedure trig_sp;
2595
##################################
2596
########## Section 3.5.11 ########
2597
# Check on Trigger Performance #
2598
##################################
2600
# Testcase: Ensure that a set of complicated, interlocking triggers that are activated
2601
# by multiple trigger events on no fewer than 50 different tables with at least
2602
# 500,000 rows each, all work correctly, return the correct results, and have
2603
# the correct effects on the database. It is expected that the Services Provider
2604
# will use its own skills and experience in database testing to devise tables and
2605
# triggers that fulfill this requirement.
2606
let $message= Testcase 3.5.11.1 (implemented in trig_perf.test);
2607
--source include/show_msg.inc
2609
# Cleanup section 3.5
2611
drop user test_general@localhost;
2612
drop user test_general;
2613
drop user test_super@localhost;
2615
##########################################
2616
# Other Scenasrios (not in requirements) #
2617
##########################################
2618
# Testcase: y.y.y.2:
2619
# Checking for triggers starting triggers (no direct requirement)
2620
let $message= Testcase y.y.y.2: Check for triggers starting triggers;
2621
--source include/show_msg.inc
2625
drop table if exists t1;
2626
drop table if exists t2_1;
2627
drop table if exists t2_2;
2628
drop table if exists t2_3;
2629
drop table if exists t2_4;
2630
drop table if exists t3;
2633
create table t1 (f1 integer);
2634
create table t2_1 (f1 integer);
2635
create table t2_2 (f1 integer);
2636
create table t2_3 (f1 integer);
2637
create table t2_4 (f1 integer);
2638
create table t3 (f1 integer);
2640
insert into t1 values (1);
2642
create trigger tr1 after insert on t1 for each row
2644
insert into t2_1 (f1) values (new.f1+1);
2645
insert into t2_2 (f1) values (new.f1+1);
2646
insert into t2_3 (f1) values (new.f1+1);
2647
insert into t2_4 (f1) values (new.f1+1);
2651
create trigger tr2_1 after insert on t2_1 for each row
2652
insert into t3 (f1) values (new.f1+10);
2653
create trigger tr2_2 after insert on t2_2 for each row
2654
insert into t3 (f1) values (new.f1+100);
2655
create trigger tr2_3 after insert on t2_3 for each row
2656
insert into t3 (f1) values (new.f1+1000);
2657
create trigger tr2_4 after insert on t2_4 for each row
2658
insert into t3 (f1) values (new.f1+10000);
2660
#lock tables t1 write, t2_1 write, t2_2 write, t2_3 write, t2_4 write, t3 write;
2661
insert into t1 values (1);
2672
drop table t1, t2_1, t2_2, t2_3, t2_4, t3;
2675
# Testcase: y.y.y.3:
2676
# Checking for circular trigger definitions
2677
let $message= Testcase y.y.y.3: Circular trigger reference;
2678
--source include/show_msg.inc
2681
drop table if exists t1;
2682
drop table if exists t2;
2683
drop table if exists t3;
2684
drop table if exists t4;
2686
eval create table t1 (f1 integer) engine = $engine_type;
2687
eval create table t2 (f2 integer) engine = $engine_type;
2688
eval create table t3 (f3 integer) engine = $engine_type;
2689
eval create table t4 (f4 integer) engine = $engine_type;
2691
insert into t1 values (0);
2692
create trigger tr1 after insert on t1
2693
for each row insert into t2 (f2) values (new.f1+1);
2694
create trigger tr2 after insert on t2
2695
for each row insert into t3 (f3) values (new.f2+1);
2696
create trigger tr3 after insert on t3
2697
for each row insert into t4 (f4) values (new.f3+1);
2698
create trigger tr4 after insert on t4
2699
for each row insert into t1 (f1) values (new.f4+1);
2703
insert into t1 values (1);
2723
# Testcase: create recursive trigger/storedprocedures conditions
2724
let $message= Testcase y.y.y.4: Recursive trigger/SP references (disabled bug 11889);
2725
--source include/show_msg.inc
2727
set @sql_mode='traditional';
2728
eval create table t1_sp (
2731
var151 decimal) engine=$engine_type;
2734
create procedure trig_sp()
2736
declare done int default 0;
2737
declare var151 decimal;
2738
declare var136 tinyint;
2739
declare cur1 cursor for select f136, f151 from tb3;
2740
declare continue handler for sqlstate '01000' set done = 1;
2741
set @counter= @counter+1;
2743
fetch cur1 into var136, var151;
2744
wl_loop: WHILE NOT done DO
2745
insert into t1_sp values (@counter, var136, var151);
2746
fetch cur1 into var136, var151;
2752
create trigger trg before insert on t1_sp
2753
for each row call trig_sp();
2760
select count(*) from tb3;
2761
select count(*) from t1_sp;
2765
drop procedure trig_sp;
2771
# Testcase: y.y.y.5:
2772
# Checking rollback of nested trigger definitions
2773
let $message= Testcase y.y.y.5: Roleback of nested trigger references;
2774
--source include/show_msg.inc
2776
set @@sql_mode='traditional';
2779
drop table if exists t1;
2780
drop table if exists t2;
2781
drop table if exists t3;
2782
drop table if exists t4;
2784
eval create table t1 (f1 integer) engine = $engine_type;
2785
eval create table t2 (f2 integer) engine = $engine_type;
2786
eval create table t3 (f3 integer) engine = $engine_type;
2787
eval create table t4 (f4 tinyint) engine = $engine_type;
2788
show create table t1;
2789
insert into t1 values (1);
2790
create trigger tr1 after insert on t1
2791
for each row insert into t2 (f2) values (new.f1+1);
2792
create trigger tr2 after insert on t2
2793
for each row insert into t3 (f3) values (new.f2+1);
2794
create trigger tr3 after insert on t3
2795
for each row insert into t4 (f4) values (new.f3+1000);
2797
#lock tables t1 write, t2 write, t3 write, t4 write;
2802
insert into t1 values (1);
added
removed
mysql-test/suite/funcs_1/triggers/triggers_master.test
