1
#======================================================================
4
# (test case numbering refer to requirement document TP v1.1)
5
#======================================================================
6
# WL#4084: enable disabled parts, 2007-11-15 hhunger
9
--source suite/funcs_1/include/tb3.inc
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 (con2_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
28
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
29
connect (con2_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
32
#################################
33
####### Section 3.5.8 ###########
34
# Checks on Triggered Actions #
35
#################################
38
# Testcase: Ensure that the triggered action of every trigger always executes
39
# correctly and the results in all expected changes made to the database
40
let $message= Testcase 3.5.8.1: (implied in previous tests);
41
--source include/show_msg.inc
44
# Testcase: Ensure that the triggered actions of every trigger never results
45
# in an unexpected change made to the database.
46
let $message= Testcase 3.5.8.2: (implied in previous tests);
47
--source include/show_msg.inc
50
#Section 3.5.8.3 / 3.5.8.4
51
#Test case: Ensure that the triggered action can any valid SQL statement / set
52
# of valid SQL statements, provided the statements are written within
53
# a BEGIN/END compound statement construct
54
# OBN - At this point the tests focuses on the the INSERT/UPDATE/DELETE SQL statements
55
# as there are the most likely to be used in triggers
56
let $message= Testcase 3.5.8.3/4:;
57
--source include/show_msg.inc
59
# creating test tables to perform the trigger SQL on
60
connection con2_super;
61
create database db_test;
62
grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
63
grant LOCK TABLES on db_test.* to test_general;
65
--replace_result $engine_type <engine_to_be_used>
66
eval create table t1_i (
67
i120 char ascii not null DEFAULT b'101',
68
i136 smallint zerofill not null DEFAULT 999,
69
i144 int zerofill not null DEFAULT 99999,
70
i163 decimal (63,30)) engine=$engine_type;
71
--replace_result $engine_type <engine_to_be_used>
72
eval create table t1_u (
73
u120 char ascii not null DEFAULT b'101',
74
u136 smallint zerofill not null DEFAULT 999,
75
u144 int zerofill not null DEFAULT 99999,
76
u163 decimal (63,30)) engine=$engine_type;
77
--replace_result $engine_type <engine_to_be_used>
78
eval create table t1_d (
79
d120 char ascii not null DEFAULT b'101',
80
d136 smallint zerofill not null DEFAULT 999,
81
d144 int zerofill not null DEFAULT 99999,
82
d163 decimal (63,30)) engine=$engine_type;
83
Insert into t1_u values ('a',111,99999,999.99);
84
Insert into t1_u values ('b',222,99999,999.99);
85
Insert into t1_u values ('c',333,99999,999.99);
86
Insert into t1_u values ('d',222,99999,999.99);
87
Insert into t1_u values ('e',222,99999,999.99);
88
Insert into t1_u values ('f',333,99999,999.99);
89
Insert into t1_d values ('a',111,99999,999.99);
90
Insert into t1_d values ('b',222,99999,999.99);
91
Insert into t1_d values ('c',333,99999,999.99);
92
Insert into t1_d values ('d',444,99999,999.99);
93
Insert into t1_d values ('e',222,99999,999.99);
94
Insert into t1_d values ('f',222,99999,999.99);
96
let $message= 3.5.8.4 - multiple SQL;
97
--source include/show_msg.inc
98
# Trigger definition - multiple SQL
101
Create trigger trg1 AFTER INSERT on tb3 for each row
103
insert into db_test.t1_i
104
values (new.f120, new.f136, new.f144, new.f163);
106
set u144=new.f144, u163=new.f163
108
delete from db_test.t1_d where d136= new.f136;
109
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
110
where u136= new.f136;
114
# Test trigger execution - multiple SQL
115
connection con2_general;
118
Insert into tb3 (f120, f122, f136, f144, f163)
119
values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
120
Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
122
select * from db_test.t1_i;
124
select * from db_test.t1_u;
126
select * from db_test.t1_d;
130
let $message= 3.5.8.4 - single SQL - insert;
131
--source include/show_msg.inc
132
# Trigger definition - single SQL Insert
133
connection con2_super;
135
Create trigger trg2 BEFORE UPDATE on tb3 for each row
137
insert into db_test.t1_i
138
values (new.f120, new.f136, new.f144, new.f163);
142
# Trigger exeution - single SQL Insert
143
connection con2_general;
144
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
145
select * from db_test.t1_i order by i120;
146
update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
147
where f122='Test 3.5.8.4';
148
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
149
select * from db_test.t1_i order by i120;
152
let $message= 3.5.8.4 - single SQL - update;
153
--source include/show_msg.inc
154
# Trigger definition - single SQL update
155
connection con2_super;
157
Create trigger trg3 BEFORE UPDATE on tb3 for each row
162
# Trigger exeution - single SQL - update;
163
connection con2_general;
164
update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
165
where f122='Test 3.5.8.4-Single Insert';
166
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
167
select * from db_test.t1_u order by u120;
170
let $message= 3.5.8.3/4 - single SQL - delete;
171
--source include/show_msg.inc
172
# Trigger definition - single SQL delete
173
connection con2_super;
175
Create trigger trg4 AFTER UPDATE on tb3 for each row
176
delete from db_test.t1_d where d136= new.f136;
178
# Trigger exeution - single SQL delete
179
connection con2_general;
180
#lock tables tb3 write, db_test.t1_i write, db_test.t1_u write, db_test.t1_d write;
181
update tb3 set f120='D', f136=444,
182
f122='Test 3.5.8.4-Single Delete'
183
where f122='Test 3.5.8.4-Single Update';
185
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
186
select * from db_test.t1_d order by d120;
189
let $message= 3.5.8.3/4 - single SQL - select;
190
--source include/show_msg.inc
191
# Trigger definition - single SQL select
192
connection con2_super;
194
Create trigger trg5 AFTER UPDATE on tb3 for each row
195
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
196
where u136= new.f136;
198
# Trigger exeution - single SQL select
199
connection con2_general;
201
update tb3 set f120='S', f136=111,
202
f122='Test 3.5.8.4-Single Select'
203
where f122='Test 3.5.8.4-Single Delete';
204
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
212
drop database if exists db_test;
213
delete from tb3 where f122 like 'Test 3.5.8.4%';
214
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
218
#Section 3.5.8.5 (IF)
219
# Test case: Ensure that the stored procedure-specific flow control statement like IF
220
# works correctly when it is a part of the triggered action portion of a
221
# trigger definition.
222
let $message= Testcase 3.5.8.5 (IF):;
223
--source include/show_msg.inc
226
create trigger trg2 before insert on tb3 for each row
229
set @test_var='one', new.f120='2';
230
ELSEIF new.f120='2' then
231
set @test_var='two', new.f120='3';
232
ELSEIF new.f120='3' then
233
set @test_var='three', new.f120='4';
236
IF (new.f120='4') and (new.f136=10) then
237
set @test_var2='2nd if', new.f120='d';
239
set @test_var2='2nd else', new.f120='D';
244
set @test_var='Empty', @test_var2=0;
245
Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
246
select f120, f122, f136, @test_var, @test_var2
247
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
248
Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
249
select f120, f122, f136, @test_var, @test_var2
250
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
251
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
252
select f120, f122, f136, @test_var, @test_var2
253
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
254
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
255
select f120, f122, f136, @test_var, @test_var2
256
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
259
--error ER_PARSE_ERROR
260
create trigger trg3 before update on tb3 for each row
262
ELSEIF new.f120='2' then
265
--error 0, ER_TRG_DOES_NOT_EXIST
268
--error ER_PARSE_ERROR
269
create trigger trg4 before update on tb3 for each row
271
IF (new.f120='4') and (new.f136=10) then
272
set @test_var2='2nd if', new.f120='d';
274
set @test_var2='2nd else', new.f120='D';
277
--error 0, ER_TRG_DOES_NOT_EXIST
283
delete from tb3 where f121='Test 3.5.8.5-if';
287
#Section 3.5.8.5 (CASE)
288
# Test case: Ensure that the stored procedure-specific flow control statement
289
# like CASE works correctly when it is a part of the triggered action
290
# portion of a trigger definition.
291
let $message= Testcase 3.5.8.5-case:;
292
--source include/show_msg.inc
295
create trigger trg3 before insert on tb3 for each row
297
SET new.f120=char(ascii(new.f120)-32);
299
when new.f136<100 then set new.f136=new.f136+120;
300
when new.f136<10 then set new.f144=777;
301
when new.f136>100 then set new.f120=new.f136-1;
304
when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
305
ELSE set @test_var=concat(new.f120, '*');
308
when 1 then set @test_var=concat(@test_var, 'one');
309
when 2 then set @test_var=concat(@test_var, 'two');
310
when 3 then set @test_var=concat(@test_var, 'three');
311
when 4 then set @test_var=concat(@test_var, 'four');
312
when 5 then set @test_var=concat(@test_var, 'five');
313
when 6 then set @test_var=concat(@test_var, 'six');
314
when 7 then set @test_var=concat(@test_var, 'seven');
315
when 8 then set @test_var=concat(@test_var, 'eight');
316
when 9 then set @test_var=concat(@test_var, 'nine');
317
when 10 then set @test_var=concat(@test_var, 'ten');
318
when 11 then set @test_var=concat(@test_var, 'eleven');
319
when 12 then set @test_var=concat(@test_var, 'twelve');
320
when 13 then set @test_var=concat(@test_var, 'thirteen');
321
when 14 then set @test_var=concat(@test_var, 'fourteen');
322
when 15 then set @test_var=concat(@test_var, 'fifteen');
323
ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
328
set @test_var='Empty';
329
Insert into tb3 (f120, f122, f136, f144)
330
values ('a', 'Test 3.5.8.5-case', 5, 7);
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, f144)
334
values ('b', 'Test 3.5.8.5-case', 71,16);
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 ('c', 'Test 3.5.8.5-case', 80,1);
339
select f120, f122, f136, f144, @test_var
340
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
341
Insert into tb3 (f120, f122, f136)
342
values ('d', 'Test 3.5.8.5-case', 152);
343
select f120, f122, f136, f144, @test_var
344
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
345
Insert into tb3 (f120, f122, f136, f144)
346
values ('e', 'Test 3.5.8.5-case', 200, 8);
347
select f120, f122, f136, f144, @test_var
348
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
349
--error 0, ER_SP_CASE_NOT_FOUND
350
Insert into tb3 (f120, f122, f136, f144)
351
values ('f', 'Test 3.5.8.5-case', 100, 8);
352
select f120, f122, f136, f144, @test_var
353
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
356
--error ER_PARSE_ERROR
357
create trigger trg3a before update on tb3 for each row
360
when new.f136<100 then set new.f120='p';
364
--error 0, ER_TRG_DOES_NOT_EXIST
370
delete from tb3 where f121='Test 3.5.8.5-case';
373
#Section 3.5.8.5 (LOOP)
374
# Test case: Ensure that the stored procedure-specific flow control
375
# statement like LOOP / LEAVE work correctly when they are
376
# part of the triggered action portion of a trigger definition.
377
let $message= Testcase 3.5.8.5-loop/leave:;
378
--source include/show_msg.inc
381
Create trigger trg4 after insert on tb3 for each row
383
set @counter=0, @flag='Initial';
385
if new.f136<new.f144 then
386
set @counter='Nothing to loop';
389
set @counter=@counter+1;
390
if new.f136=new.f144+@counter then
391
set @counter=concat(@counter, ' loops');
400
Insert into tb3 (f122, f136, f144)
401
values ('Test 3.5.8.5-loop', 2, 8);
402
select @counter, @flag;
403
Insert into tb3 (f122, f136, f144)
404
values ('Test 3.5.8.5-loop', 11, 8);
405
select @counter, @flag;
410
--error ER_PARSE_ERROR
411
Create trigger trg4_2 after update on tb3 for each row
414
set @counter=@counter+1;
418
--error 0, ER_TRG_DOES_NOT_EXIST
424
delete from tb3 where f122='Test 3.5.8.5-loop';
427
#Section 3.5.8.5 (REPEAT ITERATE)
428
#Testcase: Ensure that the stored procedure-specific flow control statements
429
# like REPEAT work correctly when they are part of the triggered action
430
# portion of a trigger definition.
431
let $message= Testcase 3.5.8.5-repeat:;
432
--source include/show_msg.inc
435
Create trigger trg6 after insert on tb3 for each row
438
SET @counter1 = @counter1 + 1;
439
IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label;
441
SET @counter2 = @counter2 + 1;
442
UNTIL @counter1> new.f136 END REPEAT rp_label;
446
set @counter1= 0, @counter2= 0;
447
Insert into tb3 (f122, f136)
448
values ('Test 3.5.8.5-repeat', 13);
449
select @counter1, @counter2;
453
--error ER_PARSE_ERROR
454
Create trigger trg6_2 after update on tb3 for each row
457
SET @counter2 = @counter2 + 1;
464
delete from tb3 where f122='Test 3.5.8.5-repeat';
468
#Section 3.5.8.5 (WHILE)
469
# Test case: Ensure that the stored procedure-specific flow control
470
# statements WHILE, work correctly when they are part of
471
# the triggered action portion of a trigger definition.
472
let $message= Testcase 3.5.8.5-while:;
473
--source include/show_msg.inc
476
Create trigger trg7 after insert on tb3 for each row
477
wl_label: WHILE @counter1 < new.f136 DO
478
SET @counter1 = @counter1 + 1;
479
IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label;
481
SET @counter2 = @counter2 + 1;
485
set @counter1= 0, @counter2= 0;
486
Insert into tb3 (f122, f136)
487
values ('Test 3.5.8.5-while', 7);
488
select @counter1, @counter2;
490
--error ER_PARSE_ERROR
491
Create trigger trg7_2 after update on tb3 for each row
493
WHILE @counter1 < new.f136
494
SET @counter1 = @counter1 + 1;
500
delete from tb3 where f122='Test 3.5.8.5-while';
505
# Test case: Ensure that a trigger definition that includes a CALL to a stored
506
# procedure fails, at CREATE TRIGGER time, with an appropriate error
507
# message. Not more valid requirement.
508
let $message= Testcase 3.5.8.6: (requirement void);
509
--source include/show_msg.inc
511
CREATE PROCEDURE sp_01 () BEGIN set @v1=1; END//
513
CREATE TRIGGER trg8_1 BEFORE UPDATE ON tb3 FOR EACH ROW
518
Insert into tb3 (f120, f122, f136) values ('6', 'Test 3.5.8.6-insert', 101);
519
update tb3 set f120='S', f136=111,
520
f122='Test 3.5.8.6-tr8_1'
521
where f122='Test 3.5.8.6-insert';
523
from tb3 where f122 like 'Test 3.5.8.6%' order by f120;
525
DROP PROCEDURE sp_01;
529
# Test case: Ensure that a trigger definition that includes a
530
# transaction-delimiting statement (e.g. COMMIT,
531
# ROLLBACK, START TRANSACTION) fails, at CREATE TRIGGER
532
# time, with an appropriate error message.
533
let $message= Testcase 3.5.8.7;
534
--source include/show_msg.inc
537
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
538
Create trigger trg9_1 before update on tb3 for each row
545
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
546
Create trigger trg9_2 before delete on tb3 for each row
555
# Cleanup section 3.5
557
drop user test_general@localhost;
558
drop user test_general;
559
drop user test_super@localhost;