1
SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
3
drop table if exists tb3;
5
f118 char not null DEFAULT 'a',
6
f119 char binary not null DEFAULT b'101',
7
f120 char ascii not null DEFAULT b'101',
10
f129 binary not null DEFAULT b'101',
11
f130 tinyint not null DEFAULT 99,
12
f131 tinyint unsigned not null DEFAULT 99,
13
f132 tinyint zerofill not null DEFAULT 99,
14
f133 tinyint unsigned zerofill not null DEFAULT 99,
15
f134 smallint not null DEFAULT 999,
16
f135 smallint unsigned not null DEFAULT 999,
17
f136 smallint zerofill not null DEFAULT 999,
18
f137 smallint unsigned zerofill not null DEFAULT 999,
19
f138 mediumint not null DEFAULT 9999,
20
f139 mediumint unsigned not null DEFAULT 9999,
21
f140 mediumint zerofill not null DEFAULT 9999,
22
f141 mediumint unsigned zerofill not null DEFAULT 9999,
23
f142 int not null DEFAULT 99999,
24
f143 int unsigned not null DEFAULT 99999,
25
f144 int zerofill not null DEFAULT 99999,
26
f145 int unsigned zerofill not null DEFAULT 99999,
27
f146 bigint not null DEFAULT 999999,
28
f147 bigint unsigned not null DEFAULT 999999,
29
f148 bigint zerofill not null DEFAULT 999999,
30
f149 bigint unsigned zerofill not null DEFAULT 999999,
31
f150 decimal not null DEFAULT 999.999,
32
f151 decimal unsigned not null DEFAULT 999.17,
33
f152 decimal zerofill not null DEFAULT 999.999,
34
f153 decimal unsigned zerofill,
37
f156 decimal (0) unsigned,
38
f157 decimal (64) unsigned,
39
f158 decimal (0) zerofill,
40
f159 decimal (64) zerofill,
41
f160 decimal (0) unsigned zerofill,
42
f161 decimal (64) unsigned zerofill,
45
f164 decimal (0,0) unsigned,
46
f165 decimal (63,30) unsigned,
47
f166 decimal (0,0) zerofill,
48
f167 decimal (63,30) zerofill,
49
f168 decimal (0,0) unsigned zerofill,
50
f169 decimal (63,30) unsigned zerofill,
52
f171 numeric unsigned,
53
f172 numeric zerofill,
54
f173 numeric unsigned zerofill,
57
) engine = <engine_to_be_used>;
59
Note 1265 Data truncated for column 'f150' at row 1
60
Note 1265 Data truncated for column 'f151' at row 1
61
Note 1265 Data truncated for column 'f152' at row 1
65
create User test_general@localhost;
66
set password for test_general@localhost = password('PWD');
67
revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
68
create User test_super@localhost;
69
set password for test_super@localhost = password('PWD');
70
grant ALL on *.* to test_super@localhost with grant OPTION;
72
Testcase 3.5.8.1: (implied in previous tests)
73
---------------------------------------------
75
Testcase 3.5.8.2: (implied in previous tests)
76
---------------------------------------------
80
create database db_test;
81
grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
82
grant LOCK TABLES on db_test.* to test_general;
85
i120 char ascii not null DEFAULT b'101',
86
i136 smallint zerofill not null DEFAULT 999,
87
i144 int zerofill not null DEFAULT 99999,
88
i163 decimal (63,30)) engine=<engine_to_be_used>;
90
u120 char ascii not null DEFAULT b'101',
91
u136 smallint zerofill not null DEFAULT 999,
92
u144 int zerofill not null DEFAULT 99999,
93
u163 decimal (63,30)) engine=<engine_to_be_used>;
95
d120 char ascii not null DEFAULT b'101',
96
d136 smallint zerofill not null DEFAULT 999,
97
d144 int zerofill not null DEFAULT 99999,
98
d163 decimal (63,30)) engine=<engine_to_be_used>;
99
Insert into t1_u values ('a',111,99999,999.99);
100
Insert into t1_u values ('b',222,99999,999.99);
101
Insert into t1_u values ('c',333,99999,999.99);
102
Insert into t1_u values ('d',222,99999,999.99);
103
Insert into t1_u values ('e',222,99999,999.99);
104
Insert into t1_u values ('f',333,99999,999.99);
105
Insert into t1_d values ('a',111,99999,999.99);
106
Insert into t1_d values ('b',222,99999,999.99);
107
Insert into t1_d values ('c',333,99999,999.99);
108
Insert into t1_d values ('d',444,99999,999.99);
109
Insert into t1_d values ('e',222,99999,999.99);
110
Insert into t1_d values ('f',222,99999,999.99);
112
3.5.8.4 - multiple SQL
113
----------------------
115
Create trigger trg1 AFTER INSERT on tb3 for each row
117
insert into db_test.t1_i
118
values (new.f120, new.f136, new.f144, new.f163);
120
set u144=new.f144, u163=new.f163
122
delete from db_test.t1_d where d136= new.f136;
123
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
124
where u136= new.f136;
128
Insert into tb3 (f120, f122, f136, f144, f163)
129
values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
130
Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
131
f120 f122 f136 f144 f163
132
1 Test 3.5.8.4 00222 0000023456 1.050000000000000000000000000000
133
select * from db_test.t1_i;
135
1 00222 0000023456 1.050000000000000000000000000000
136
select * from db_test.t1_u;
138
a 00111 0000099999 999.990000000000000000000000000000
139
b 00222 0000023456 1.050000000000000000000000000000
140
c 00333 0000099999 999.990000000000000000000000000000
141
d 00222 0000023456 1.050000000000000000000000000000
142
e 00222 0000023456 1.050000000000000000000000000000
143
f 00333 0000099999 999.990000000000000000000000000000
144
select * from db_test.t1_d;
146
a 00111 0000099999 999.990000000000000000000000000000
147
c 00333 0000099999 999.990000000000000000000000000000
148
d 00444 0000099999 999.990000000000000000000000000000
151
3.150000000000000000000000000000
153
3.5.8.4 - single SQL - insert
154
-----------------------------
155
Create trigger trg2 BEFORE UPDATE on tb3 for each row
157
insert into db_test.t1_i
158
values (new.f120, new.f136, new.f144, new.f163);
160
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
161
f120 f122 f136 f144 f163
162
1 Test 3.5.8.4 00222 0000023456 1.050000000000000000000000000000
163
select * from db_test.t1_i order by i120;
165
1 00222 0000023456 1.050000000000000000000000000000
166
update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
167
where f122='Test 3.5.8.4';
168
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
169
f120 f122 f136 f144 f163
170
I Test 3.5.8.4-Single Insert 00222 0000023456 1.050000000000000000000000000000
171
select * from db_test.t1_i order by i120;
173
1 00222 0000023456 1.050000000000000000000000000000
174
I 00222 0000023456 1.050000000000000000000000000000
176
3.5.8.4 - single SQL - update
177
-----------------------------
179
Create trigger trg3 BEFORE UPDATE on tb3 for each row
183
update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
184
where f122='Test 3.5.8.4-Single Insert';
185
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
186
f120 f122 f136 f144 f163
187
U Test 3.5.8.4-Single Update 00222 0000023456 1.050000000000000000000000000000
188
select * from db_test.t1_u order by u120;
190
a 00111 0000099999 999.990000000000000000000000000000
191
c 00333 0000099999 999.990000000000000000000000000000
192
f 00333 0000099999 999.990000000000000000000000000000
193
U 00222 0000023456 1.050000000000000000000000000000
194
U 00222 0000023456 1.050000000000000000000000000000
195
U 00222 0000023456 1.050000000000000000000000000000
197
3.5.8.3/4 - single SQL - delete
198
-------------------------------
200
Create trigger trg4 AFTER UPDATE on tb3 for each row
201
delete from db_test.t1_d where d136= new.f136;
202
update tb3 set f120='D', f136=444,
203
f122='Test 3.5.8.4-Single Delete'
204
where f122='Test 3.5.8.4-Single Update';
205
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
206
f120 f122 f136 f144 f163
207
D Test 3.5.8.4-Single Delete 00444 0000023456 1.050000000000000000000000000000
208
select * from db_test.t1_d order by d120;
210
a 00111 0000099999 999.990000000000000000000000000000
211
c 00333 0000099999 999.990000000000000000000000000000
213
3.5.8.3/4 - single SQL - select
214
-------------------------------
216
Create trigger trg5 AFTER UPDATE on tb3 for each row
217
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
218
where u136= new.f136;
220
update tb3 set f120='S', f136=111,
221
f122='Test 3.5.8.4-Single Select'
222
where f122='Test 3.5.8.4-Single Delete';
223
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
224
f120 f122 f136 f144 f163
225
S Test 3.5.8.4-Single Select 00111 0000023456 1.050000000000000000000000000000
228
999.990000000000000000000000000000
231
drop database if exists db_test;
232
delete from tb3 where f122 like 'Test 3.5.8.4%';
233
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
235
Testcase 3.5.8.5 (IF):
236
----------------------
237
create trigger trg2 before insert on tb3 for each row
240
set @test_var='one', new.f120='2';
241
ELSEIF new.f120='2' then
242
set @test_var='two', new.f120='3';
243
ELSEIF new.f120='3' then
244
set @test_var='three', new.f120='4';
246
IF (new.f120='4') and (new.f136=10) then
247
set @test_var2='2nd if', new.f120='d';
249
set @test_var2='2nd else', new.f120='D';
252
set @test_var='Empty', @test_var2=0;
253
Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
254
select f120, f122, f136, @test_var, @test_var2
255
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
256
f120 f122 f136 @test_var @test_var2
257
D Test 3.5.8.5-if 00101 one 2nd else
258
Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
259
select f120, f122, f136, @test_var, @test_var2
260
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
261
f120 f122 f136 @test_var @test_var2
262
D Test 3.5.8.5-if 00101 two 2nd else
263
D Test 3.5.8.5-if 00102 two 2nd else
264
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
265
select f120, f122, f136, @test_var, @test_var2
266
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
267
f120 f122 f136 @test_var @test_var2
268
d Test 3.5.8.5-if 00010 three 2nd if
269
D Test 3.5.8.5-if 00101 three 2nd if
270
D Test 3.5.8.5-if 00102 three 2nd if
271
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
272
select f120, f122, f136, @test_var, @test_var2
273
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
274
f120 f122 f136 @test_var @test_var2
275
d Test 3.5.8.5-if 00010 three 2nd else
276
D Test 3.5.8.5-if 00101 three 2nd else
277
D Test 3.5.8.5-if 00102 three 2nd else
278
D Test 3.5.8.5-if 00103 three 2nd else
279
create trigger trg3 before update on tb3 for each row
281
ELSEIF new.f120='2' then
284
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSEIF new.f120='2' then
288
create trigger trg4 before update on tb3 for each row
290
IF (new.f120='4') and (new.f136=10) then
291
set @test_var2='2nd if', new.f120='d';
293
set @test_var2='2nd else', new.f120='D';
295
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7
298
delete from tb3 where f121='Test 3.5.8.5-if';
300
Testcase 3.5.8.5-case:
301
----------------------
302
create trigger trg3 before insert on tb3 for each row
304
SET new.f120=char(ascii(new.f120)-32);
306
when new.f136<100 then set new.f136=new.f136+120;
307
when new.f136<10 then set new.f144=777;
308
when new.f136>100 then set new.f120=new.f136-1;
311
when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
312
ELSE set @test_var=concat(new.f120, '*');
315
when 1 then set @test_var=concat(@test_var, 'one');
316
when 2 then set @test_var=concat(@test_var, 'two');
317
when 3 then set @test_var=concat(@test_var, 'three');
318
when 4 then set @test_var=concat(@test_var, 'four');
319
when 5 then set @test_var=concat(@test_var, 'five');
320
when 6 then set @test_var=concat(@test_var, 'six');
321
when 7 then set @test_var=concat(@test_var, 'seven');
322
when 8 then set @test_var=concat(@test_var, 'eight');
323
when 9 then set @test_var=concat(@test_var, 'nine');
324
when 10 then set @test_var=concat(@test_var, 'ten');
325
when 11 then set @test_var=concat(@test_var, 'eleven');
326
when 12 then set @test_var=concat(@test_var, 'twelve');
327
when 13 then set @test_var=concat(@test_var, 'thirteen');
328
when 14 then set @test_var=concat(@test_var, 'fourteen');
329
when 15 then set @test_var=concat(@test_var, 'fifteen');
330
ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
333
set @test_var='Empty';
334
Insert into tb3 (f120, f122, f136, f144)
335
values ('a', 'Test 3.5.8.5-case', 5, 7);
336
select f120, f122, f136, f144, @test_var
337
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
338
f120 f122 f136 f144 @test_var
339
A Test 3.5.8.5-case 00125 0000000007 A*seven
340
Insert into tb3 (f120, f122, f136, f144)
341
values ('b', 'Test 3.5.8.5-case', 71,16);
342
select f120, f122, f136, f144, @test_var
343
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
344
f120 f122 f136 f144 @test_var
345
A Test 3.5.8.5-case 00125 0000000007 B*0000000016
346
B Test 3.5.8.5-case 00191 0000000016 B*0000000016
347
Insert into tb3 (f120, f122, f136, f144)
348
values ('c', 'Test 3.5.8.5-case', 80,1);
349
select f120, f122, f136, f144, @test_var
350
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
351
f120 f122 f136 f144 @test_var
352
A Test 3.5.8.5-case 00125 0000000007 C=one
353
B Test 3.5.8.5-case 00191 0000000016 C=one
354
C Test 3.5.8.5-case 00200 0000000001 C=one
355
Insert into tb3 (f120, f122, f136)
356
values ('d', 'Test 3.5.8.5-case', 152);
357
select f120, f122, f136, f144, @test_var
358
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
359
f120 f122 f136 f144 @test_var
360
1 Test 3.5.8.5-case 00152 0000099999 1*0000099999
361
A Test 3.5.8.5-case 00125 0000000007 1*0000099999
362
B Test 3.5.8.5-case 00191 0000000016 1*0000099999
363
C Test 3.5.8.5-case 00200 0000000001 1*0000099999
364
Insert into tb3 (f120, f122, f136, f144)
365
values ('e', 'Test 3.5.8.5-case', 200, 8);
366
select f120, f122, f136, f144, @test_var
367
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
368
f120 f122 f136 f144 @test_var
369
1 Test 3.5.8.5-case 00152 0000099999 1=eight
370
1 Test 3.5.8.5-case 00200 0000000008 1=eight
371
A Test 3.5.8.5-case 00125 0000000007 1=eight
372
B Test 3.5.8.5-case 00191 0000000016 1=eight
373
C Test 3.5.8.5-case 00200 0000000001 1=eight
374
Insert into tb3 (f120, f122, f136, f144)
375
values ('f', 'Test 3.5.8.5-case', 100, 8);
376
select f120, f122, f136, f144, @test_var
377
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
378
f120 f122 f136 f144 @test_var
379
1 Test 3.5.8.5-case 00152 0000099999 1=eight
380
1 Test 3.5.8.5-case 00200 0000000008 1=eight
381
A Test 3.5.8.5-case 00125 0000000007 1=eight
382
B Test 3.5.8.5-case 00191 0000000016 1=eight
383
C Test 3.5.8.5-case 00200 0000000001 1=eight
384
create trigger trg3a before update on tb3 for each row
387
when new.f136<100 then set new.f120='p';
389
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
392
delete from tb3 where f121='Test 3.5.8.5-case';
394
Testcase 3.5.8.5-loop/leave:
395
----------------------------
396
Create trigger trg4 after insert on tb3 for each row
398
set @counter=0, @flag='Initial';
400
if new.f136<new.f144 then
401
set @counter='Nothing to loop';
404
set @counter=@counter+1;
405
if new.f136=new.f144+@counter then
406
set @counter=concat(@counter, ' loops');
414
Insert into tb3 (f122, f136, f144)
415
values ('Test 3.5.8.5-loop', 2, 8);
416
select @counter, @flag;
418
Nothing to loop Initial
419
Insert into tb3 (f122, f136, f144)
420
values ('Test 3.5.8.5-loop', 11, 8);
421
select @counter, @flag;
424
Create trigger trg4_2 after update on tb3 for each row
427
set @counter=@counter+1;
430
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
434
delete from tb3 where f122='Test 3.5.8.5-loop';
436
Testcase 3.5.8.5-repeat:
437
------------------------
438
Create trigger trg6 after insert on tb3 for each row
441
SET @counter1 = @counter1 + 1;
442
IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label;
444
SET @counter2 = @counter2 + 1;
445
UNTIL @counter1> new.f136 END REPEAT rp_label;
447
set @counter1= 0, @counter2= 0;
448
Insert into tb3 (f122, f136)
449
values ('Test 3.5.8.5-repeat', 13);
450
select @counter1, @counter2;
453
Create trigger trg6_2 after update on tb3 for each row
456
SET @counter2 = @counter2 + 1;
458
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 5
460
delete from tb3 where f122='Test 3.5.8.5-repeat';
462
Testcase 3.5.8.5-while:
463
-----------------------
464
Create trigger trg7 after insert on tb3 for each row
465
wl_label: WHILE @counter1 < new.f136 DO
466
SET @counter1 = @counter1 + 1;
467
IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label;
469
SET @counter2 = @counter2 + 1;
471
set @counter1= 0, @counter2= 0;
472
Insert into tb3 (f122, f136)
473
values ('Test 3.5.8.5-while', 7);
474
select @counter1, @counter2;
477
Create trigger trg7_2 after update on tb3 for each row
479
WHILE @counter1 < new.f136
480
SET @counter1 = @counter1 + 1;
482
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @counter1 = @counter1 + 1;
484
delete from tb3 where f122='Test 3.5.8.5-while';
487
Testcase 3.5.8.6: (requirement void)
488
------------------------------------
489
CREATE PROCEDURE sp_01 () BEGIN set @v1=1; END//
490
CREATE TRIGGER trg8_1 BEFORE UPDATE ON tb3 FOR EACH ROW
494
Insert into tb3 (f120, f122, f136) values ('6', 'Test 3.5.8.6-insert', 101);
495
update tb3 set f120='S', f136=111,
496
f122='Test 3.5.8.6-tr8_1'
497
where f122='Test 3.5.8.6-insert';
499
from tb3 where f122 like 'Test 3.5.8.6%' order by f120;
503
DROP PROCEDURE sp_01;
507
Create trigger trg9_1 before update on tb3 for each row
513
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
514
Create trigger trg9_2 before delete on tb3 for each row
520
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
521
drop user test_general@localhost;
522
drop user test_general;
523
drop user test_super@localhost;