1
#======================================================================
4
# (test case numbering refer to requirement document TP v1.1)
5
#======================================================================
6
# WL#4084: enable disabled parts, 2007-11-15 hhunger
8
# General setup for Trigger tests
9
let $message= Testcase: 3.5:;
10
--source include/show_msg.inc
12
--disable_abort_on_error
14
create User test_general@localhost;
15
set password for test_general@localhost = password('PWD');
16
revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
18
create User test_super@localhost;
19
set password for test_super@localhost = password('PWD');
20
grant ALL on *.* to test_super@localhost with grant OPTION;
21
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
22
connect (con2_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
23
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
24
connect (con2_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
27
#################################
28
####### Section 3.5.8 ###########
29
# Checks on Triggered Actions #
30
#################################
33
# Testcase: Ensure that the triggered action of every trigger always executes
34
# correctly and the results in all expected changes made to the database
35
let $message= Testcase 3.5.8.1: (implied in previous tests);
36
--source include/show_msg.inc
39
# Testcase: Ensure that the triggered actions of every trigger never results
40
# in an unexpected change made to the database.
41
let $message= Testcase 3.5.8.2: (implied in previous tests);
42
--source include/show_msg.inc
45
#Section 3.5.8.3 / 3.5.8.4
46
#Test case: Ensure that the triggered action can any valid SQL statement / set
47
# of valid SQL statements, provided the statements are written within
48
# a BEGIN/END compound statement construct
49
# OBN - At this point the tests focuses on the the INSERT/UPDATE/DELETE SQL statements
50
# as there are the most likely to be used in triggers
51
let $message= Testcase 3.5.8.3/4:;
52
--source include/show_msg.inc
54
# creating test tables to perform the trigger SQL on
55
connection con2_super;
56
create database db_test;
57
grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
58
grant LOCK TABLES on db_test.* to test_general;
60
eval create table t1_i (
61
i120 char ascii not null DEFAULT b'101',
62
i136 smallint zerofill not null DEFAULT 999,
63
i144 int zerofill not null DEFAULT 99999,
64
i163 decimal (63,30)) engine=$engine_type;
65
eval create table t1_u (
66
u120 char ascii not null DEFAULT b'101',
67
u136 smallint zerofill not null DEFAULT 999,
68
u144 int zerofill not null DEFAULT 99999,
69
u163 decimal (63,30)) engine=$engine_type;
70
eval create table t1_d (
71
d120 char ascii not null DEFAULT b'101',
72
d136 smallint zerofill not null DEFAULT 999,
73
d144 int zerofill not null DEFAULT 99999,
74
d163 decimal (63,30)) engine=$engine_type;
75
Insert into t1_u values ('a',111,99999,999.99);
76
Insert into t1_u values ('b',222,99999,999.99);
77
Insert into t1_u values ('c',333,99999,999.99);
78
Insert into t1_u values ('d',222,99999,999.99);
79
Insert into t1_u values ('e',222,99999,999.99);
80
Insert into t1_u values ('f',333,99999,999.99);
81
Insert into t1_d values ('a',111,99999,999.99);
82
Insert into t1_d values ('b',222,99999,999.99);
83
Insert into t1_d values ('c',333,99999,999.99);
84
Insert into t1_d values ('d',444,99999,999.99);
85
Insert into t1_d values ('e',222,99999,999.99);
86
Insert into t1_d values ('f',222,99999,999.99);
88
let $message= 3.5.8.4 - multiple SQL;
89
--source include/show_msg.inc
90
# Trigger definition - multiple SQL
93
Create trigger trg1 AFTER INSERT on tb3 for each row
95
insert into db_test.t1_i
96
values (new.f120, new.f136, new.f144, new.f163);
98
set u144=new.f144, u163=new.f163
100
delete from db_test.t1_d where d136= new.f136;
101
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
102
where u136= new.f136;
106
# Test trigger execution - multiple SQL
107
connection con2_general;
110
Insert into tb3 (f120, f122, f136, f144, f163)
111
values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
112
Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
114
select * from db_test.t1_i;
116
select * from db_test.t1_u;
118
select * from db_test.t1_d;
122
let $message= 3.5.8.4 - single SQL - insert;
123
--source include/show_msg.inc
124
# Trigger definition - single SQL Insert
125
connection con2_super;
127
Create trigger trg2 BEFORE UPDATE on tb3 for each row
129
insert into db_test.t1_i
130
values (new.f120, new.f136, new.f144, new.f163);
134
# Trigger exeution - single SQL Insert
135
connection con2_general;
136
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
137
select * from db_test.t1_i order by i120;
138
update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
139
where f122='Test 3.5.8.4';
140
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
141
select * from db_test.t1_i order by i120;
144
let $message= 3.5.8.4 - single SQL - update;
145
--source include/show_msg.inc
146
# Trigger definition - single SQL update
147
connection con2_super;
149
Create trigger trg3 BEFORE UPDATE on tb3 for each row
154
# Trigger exeution - single SQL - update;
155
connection con2_general;
156
update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
157
where f122='Test 3.5.8.4-Single Insert';
158
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
159
select * from db_test.t1_u order by u120;
162
let $message= 3.5.8.3/4 - single SQL - delete;
163
--source include/show_msg.inc
164
# Trigger definition - single SQL delete
165
connection con2_super;
167
Create trigger trg4 AFTER UPDATE on tb3 for each row
168
delete from db_test.t1_d where d136= new.f136;
170
# Trigger exeution - single SQL delete
171
connection con2_general;
172
#lock tables tb3 write, db_test.t1_i write, db_test.t1_u write, db_test.t1_d write;
173
update tb3 set f120='D', f136=444,
174
f122='Test 3.5.8.4-Single Delete'
175
where f122='Test 3.5.8.4-Single Update';
177
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
178
select * from db_test.t1_d order by d120;
181
let $message= 3.5.8.3/4 - single SQL - select;
182
--source include/show_msg.inc
183
# Trigger definition - single SQL select
184
connection con2_super;
186
Create trigger trg5 AFTER UPDATE on tb3 for each row
187
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
188
where u136= new.f136;
190
# Trigger exeution - single SQL select
191
connection con2_general;
193
update tb3 set f120='S', f136=111,
194
f122='Test 3.5.8.4-Single Select'
195
where f122='Test 3.5.8.4-Single Delete';
196
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
204
drop database if exists db_test;
205
delete from tb3 where f122 like 'Test 3.5.8.4%';
206
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
210
#Section 3.5.8.5 (IF)
211
# Test case: Ensure that the stored procedure-specific flow control statement like IF
212
# works correctly when it is a part of the triggered action portion of a
213
# trigger definition.
214
let $message= Testcase 3.5.8.5 (IF):;
215
--source include/show_msg.inc
218
create trigger trg2 before insert on tb3 for each row
221
set @test_var='one', new.f120='2';
222
ELSEIF new.f120='2' then
223
set @test_var='two', new.f120='3';
224
ELSEIF new.f120='3' then
225
set @test_var='three', new.f120='4';
228
IF (new.f120='4') and (new.f136=10) then
229
set @test_var2='2nd if', new.f120='d';
231
set @test_var2='2nd else', new.f120='D';
236
set @test_var='Empty', @test_var2=0;
237
Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
238
select f120, f122, f136, @test_var, @test_var2
239
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
240
Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
241
select f120, f122, f136, @test_var, @test_var2
242
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
243
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
244
select f120, f122, f136, @test_var, @test_var2
245
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
246
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
247
select f120, f122, f136, @test_var, @test_var2
248
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
252
create trigger trg3 before update on tb3 for each row
254
ELSEIF new.f120='2' then
261
create trigger trg4 before update on tb3 for each row
263
IF (new.f120='4') and (new.f136=10) then
264
set @test_var2='2nd if', new.f120='d';
266
set @test_var2='2nd else', new.f120='D';
275
delete from tb3 where f121='Test 3.5.8.5-if';
279
#Section 3.5.8.5 (CASE)
280
# Test case: Ensure that the stored procedure-specific flow control statement
281
# like CASE works correctly when it is a part of the triggered action
282
# portion of a trigger definition.
283
let $message= Testcase 3.5.8.5-case:;
284
--source include/show_msg.inc
287
create trigger trg3 before insert on tb3 for each row
289
SET new.f120=char(ascii(new.f120)-32);
291
when new.f136<100 then set new.f136=new.f136+120;
292
when new.f136<10 then set new.f144=777;
293
when new.f136>100 then set new.f120=new.f136-1;
296
when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
297
ELSE set @test_var=concat(new.f120, '*');
300
when 1 then set @test_var=concat(@test_var, 'one');
301
when 2 then set @test_var=concat(@test_var, 'two');
302
when 3 then set @test_var=concat(@test_var, 'three');
303
when 4 then set @test_var=concat(@test_var, 'four');
304
when 5 then set @test_var=concat(@test_var, 'five');
305
when 6 then set @test_var=concat(@test_var, 'six');
306
when 7 then set @test_var=concat(@test_var, 'seven');
307
when 8 then set @test_var=concat(@test_var, 'eight');
308
when 9 then set @test_var=concat(@test_var, 'nine');
309
when 10 then set @test_var=concat(@test_var, 'ten');
310
when 11 then set @test_var=concat(@test_var, 'eleven');
311
when 12 then set @test_var=concat(@test_var, 'twelve');
312
when 13 then set @test_var=concat(@test_var, 'thirteen');
313
when 14 then set @test_var=concat(@test_var, 'fourteen');
314
when 15 then set @test_var=concat(@test_var, 'fifteen');
315
ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
320
set @test_var='Empty';
321
Insert into tb3 (f120, f122, f136, f144)
322
values ('a', 'Test 3.5.8.5-case', 5, 7);
323
select f120, f122, f136, f144, @test_var
324
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
325
Insert into tb3 (f120, f122, f136, f144)
326
values ('b', 'Test 3.5.8.5-case', 71,16);
327
select f120, f122, f136, f144, @test_var
328
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
329
Insert into tb3 (f120, f122, f136, f144)
330
values ('c', 'Test 3.5.8.5-case', 80,1);
331
select f120, f122, f136, f144, @test_var
332
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
333
Insert into tb3 (f120, f122, f136)
334
values ('d', 'Test 3.5.8.5-case', 152);
335
select f120, f122, f136, f144, @test_var
336
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
337
Insert into tb3 (f120, f122, f136, f144)
338
values ('e', 'Test 3.5.8.5-case', 200, 8);
339
select f120, f122, f136, f144, @test_var
340
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
342
Insert into tb3 (f120, f122, f136, f144)
343
values ('f', 'Test 3.5.8.5-case', 100, 8);
344
select f120, f122, f136, f144, @test_var
345
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
349
create trigger trg3a before update on tb3 for each row
352
when new.f136<100 then set new.f120='p';
362
delete from tb3 where f121='Test 3.5.8.5-case';
365
#Section 3.5.8.5 (LOOP)
366
# Test case: Ensure that the stored procedure-specific flow control
367
# statement like LOOP / LEAVE work correctly when they are
368
# part of the triggered action portion of a trigger definition.
369
let $message= Testcase 3.5.8.5-loop/leave:;
370
--source include/show_msg.inc
373
Create trigger trg4 after insert on tb3 for each row
375
set @counter=0, @flag='Initial';
377
if new.f136<new.f144 then
378
set @counter='Nothing to loop';
381
set @counter=@counter+1;
382
if new.f136=new.f144+@counter then
383
set @counter=concat(@counter, ' loops');
392
Insert into tb3 (f122, f136, f144)
393
values ('Test 3.5.8.5-loop', 2, 8);
394
select @counter, @flag;
395
Insert into tb3 (f122, f136, f144)
396
values ('Test 3.5.8.5-loop', 11, 8);
397
select @counter, @flag;
403
Create trigger trg4_2 after update on tb3 for each row
406
set @counter=@counter+1;
416
delete from tb3 where f122='Test 3.5.8.5-loop';
419
#Section 3.5.8.5 (REPEAT ITERATE)
420
#Testcase: Ensure that the stored procedure-specific flow control statements
421
# like REPEAT work correctly when they are part of the triggered action
422
# portion of a trigger definition.
423
let $message= Testcase 3.5.8.5-repeat:;
424
--source include/show_msg.inc
427
Create trigger trg6 after insert on tb3 for each row
430
SET @counter1 = @counter1 + 1;
431
IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label;
433
SET @counter2 = @counter2 + 1;
434
UNTIL @counter1> new.f136 END REPEAT rp_label;
438
set @counter1= 0, @counter2= 0;
439
Insert into tb3 (f122, f136)
440
values ('Test 3.5.8.5-repeat', 13);
441
select @counter1, @counter2;
446
Create trigger trg6_2 after update on tb3 for each row
449
SET @counter2 = @counter2 + 1;
456
delete from tb3 where f122='Test 3.5.8.5-repeat';
460
#Section 3.5.8.5 (WHILE)
461
# Test case: Ensure that the stored procedure-specific flow control
462
# statements WHILE, work correctly when they are part of
463
# the triggered action portion of a trigger definition.
464
let $message= Testcase 3.5.8.5-while:;
465
--source include/show_msg.inc
468
Create trigger trg7 after insert on tb3 for each row
469
wl_label: WHILE @counter1 < new.f136 DO
470
SET @counter1 = @counter1 + 1;
471
IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label;
473
SET @counter2 = @counter2 + 1;
477
set @counter1= 0, @counter2= 0;
478
Insert into tb3 (f122, f136)
479
values ('Test 3.5.8.5-while', 7);
480
select @counter1, @counter2;
483
Create trigger trg7_2 after update on tb3 for each row
485
WHILE @counter1 < new.f136
486
SET @counter1 = @counter1 + 1;
492
delete from tb3 where f122='Test 3.5.8.5-while';
497
# Test case: Ensure that a trigger definition that includes a CALL to a stored
498
# procedure fails, at CREATE TRIGGER time, with an appropriate error
499
# message. Not more valid requirement.
500
let $message= Testcase 3.5.8.6: (requirement void);
501
--source include/show_msg.inc
503
CREATE PROCEDURE sp_01 () BEGIN set @v1=1; END//
505
CREATE TRIGGER trg8_1 BEFORE UPDATE ON tb3 FOR EACH ROW
510
Insert into tb3 (f120, f122, f136) values ('6', 'Test 3.5.8.6-insert', 101);
511
update tb3 set f120='S', f136=111,
512
f122='Test 3.5.8.6-tr8_1'
513
where f122='Test 3.5.8.6-insert';
515
from tb3 where f122 like 'Test 3.5.8.6%' order by f120;
517
DROP PROCEDURE sp_01;
521
# Test case: Ensure that a trigger definition that includes a
522
# transaction-delimiting statement (e.g. COMMIT,
523
# ROLLBACK, START TRANSACTION) fails, at CREATE TRIGGER
524
# time, with an appropriate error message.
525
let $message= Testcase 3.5.8.7;
526
--source include/show_msg.inc
529
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
530
Create trigger trg9_1 before update on tb3 for each row
537
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
538
Create trigger trg9_2 before delete on tb3 for each row
547
# Cleanup section 3.5
549
drop user test_general@localhost;
550
drop user test_general;
551
drop user test_super@localhost;
added
removed
mysql-test/suite/funcs_1/triggers/triggers_08.inc
