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;
71
connect(localhost,test_general,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
72
connect(localhost,test_super,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
74
Testcase 3.5.8.1: (implied in previous tests)
75
---------------------------------------------
77
Testcase 3.5.8.2: (implied in previous tests)
78
---------------------------------------------
82
create database db_test;
83
grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
84
grant LOCK TABLES on db_test.* to test_general;
87
i120 char ascii not null DEFAULT b'101',
88
i136 smallint zerofill not null DEFAULT 999,
89
i144 int zerofill not null DEFAULT 99999,
90
i163 decimal (63,30)) engine=<engine_to_be_used>;
92
u120 char ascii not null DEFAULT b'101',
93
u136 smallint zerofill not null DEFAULT 999,
94
u144 int zerofill not null DEFAULT 99999,
95
u163 decimal (63,30)) engine=<engine_to_be_used>;
97
d120 char ascii not null DEFAULT b'101',
98
d136 smallint zerofill not null DEFAULT 999,
99
d144 int zerofill not null DEFAULT 99999,
100
d163 decimal (63,30)) engine=<engine_to_be_used>;
101
Insert into t1_u values ('a',111,99999,999.99);
102
Insert into t1_u values ('b',222,99999,999.99);
103
Insert into t1_u values ('c',333,99999,999.99);
104
Insert into t1_u values ('d',222,99999,999.99);
105
Insert into t1_u values ('e',222,99999,999.99);
106
Insert into t1_u values ('f',333,99999,999.99);
107
Insert into t1_d values ('a',111,99999,999.99);
108
Insert into t1_d values ('b',222,99999,999.99);
109
Insert into t1_d values ('c',333,99999,999.99);
110
Insert into t1_d values ('d',444,99999,999.99);
111
Insert into t1_d values ('e',222,99999,999.99);
112
Insert into t1_d values ('f',222,99999,999.99);
114
3.5.8.4 - multiple SQL
115
----------------------
117
Create trigger trg1 AFTER INSERT on tb3 for each row
119
insert into db_test.t1_i
120
values (new.f120, new.f136, new.f144, new.f163);
122
set u144=new.f144, u163=new.f163
124
delete from db_test.t1_d where d136= new.f136;
125
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
126
where u136= new.f136;
130
Insert into tb3 (f120, f122, f136, f144, f163)
131
values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
132
Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
133
f120 f122 f136 f144 f163
134
1 Test 3.5.8.4 00222 0000023456 1.050000000000000000000000000000
135
select * from db_test.t1_i;
137
1 00222 0000023456 1.050000000000000000000000000000
138
select * from db_test.t1_u;
140
a 00111 0000099999 999.990000000000000000000000000000
141
b 00222 0000023456 1.050000000000000000000000000000
142
c 00333 0000099999 999.990000000000000000000000000000
143
d 00222 0000023456 1.050000000000000000000000000000
144
e 00222 0000023456 1.050000000000000000000000000000
145
f 00333 0000099999 999.990000000000000000000000000000
146
select * from db_test.t1_d;
148
a 00111 0000099999 999.990000000000000000000000000000
149
c 00333 0000099999 999.990000000000000000000000000000
150
d 00444 0000099999 999.990000000000000000000000000000
153
3.150000000000000000000000000000
155
3.5.8.4 - single SQL - insert
156
-----------------------------
157
Create trigger trg2 BEFORE UPDATE on tb3 for each row
159
insert into db_test.t1_i
160
values (new.f120, new.f136, new.f144, new.f163);
162
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
163
f120 f122 f136 f144 f163
164
1 Test 3.5.8.4 00222 0000023456 1.050000000000000000000000000000
165
select * from db_test.t1_i order by i120;
167
1 00222 0000023456 1.050000000000000000000000000000
168
update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
169
where f122='Test 3.5.8.4';
170
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
171
f120 f122 f136 f144 f163
172
I Test 3.5.8.4-Single Insert 00222 0000023456 1.050000000000000000000000000000
173
select * from db_test.t1_i order by i120;
175
1 00222 0000023456 1.050000000000000000000000000000
176
I 00222 0000023456 1.050000000000000000000000000000
178
3.5.8.4 - single SQL - update
179
-----------------------------
181
Create trigger trg3 BEFORE UPDATE on tb3 for each row
185
update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
186
where f122='Test 3.5.8.4-Single Insert';
187
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
188
f120 f122 f136 f144 f163
189
U Test 3.5.8.4-Single Update 00222 0000023456 1.050000000000000000000000000000
190
select * from db_test.t1_u order by u120;
192
a 00111 0000099999 999.990000000000000000000000000000
193
c 00333 0000099999 999.990000000000000000000000000000
194
f 00333 0000099999 999.990000000000000000000000000000
195
U 00222 0000023456 1.050000000000000000000000000000
196
U 00222 0000023456 1.050000000000000000000000000000
197
U 00222 0000023456 1.050000000000000000000000000000
199
3.5.8.3/4 - single SQL - delete
200
-------------------------------
202
Create trigger trg4 AFTER UPDATE on tb3 for each row
203
delete from db_test.t1_d where d136= new.f136;
204
update tb3 set f120='D', f136=444,
205
f122='Test 3.5.8.4-Single Delete'
206
where f122='Test 3.5.8.4-Single Update';
207
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
208
f120 f122 f136 f144 f163
209
D Test 3.5.8.4-Single Delete 00444 0000023456 1.050000000000000000000000000000
210
select * from db_test.t1_d order by d120;
212
a 00111 0000099999 999.990000000000000000000000000000
213
c 00333 0000099999 999.990000000000000000000000000000
215
3.5.8.3/4 - single SQL - select
216
-------------------------------
218
Create trigger trg5 AFTER UPDATE on tb3 for each row
219
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
220
where u136= new.f136;
222
update tb3 set f120='S', f136=111,
223
f122='Test 3.5.8.4-Single Select'
224
where f122='Test 3.5.8.4-Single Delete';
225
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
226
f120 f122 f136 f144 f163
227
S Test 3.5.8.4-Single Select 00111 0000023456 1.050000000000000000000000000000
230
999.990000000000000000000000000000
233
drop database if exists db_test;
234
delete from tb3 where f122 like 'Test 3.5.8.4%';
235
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
237
Testcase 3.5.8.5 (IF):
238
----------------------
239
create trigger trg2 before insert on tb3 for each row
242
set @test_var='one', new.f120='2';
243
ELSEIF new.f120='2' then
244
set @test_var='two', new.f120='3';
245
ELSEIF new.f120='3' then
246
set @test_var='three', new.f120='4';
248
IF (new.f120='4') and (new.f136=10) then
249
set @test_var2='2nd if', new.f120='d';
251
set @test_var2='2nd else', new.f120='D';
254
set @test_var='Empty', @test_var2=0;
255
Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
256
select f120, f122, f136, @test_var, @test_var2
257
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
258
f120 f122 f136 @test_var @test_var2
259
D Test 3.5.8.5-if 00101 one 2nd else
260
Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
261
select f120, f122, f136, @test_var, @test_var2
262
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
263
f120 f122 f136 @test_var @test_var2
264
D Test 3.5.8.5-if 00101 two 2nd else
265
D Test 3.5.8.5-if 00102 two 2nd else
266
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
267
select f120, f122, f136, @test_var, @test_var2
268
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
269
f120 f122 f136 @test_var @test_var2
270
d Test 3.5.8.5-if 00010 three 2nd if
271
D Test 3.5.8.5-if 00101 three 2nd if
272
D Test 3.5.8.5-if 00102 three 2nd if
273
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
274
select f120, f122, f136, @test_var, @test_var2
275
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
276
f120 f122 f136 @test_var @test_var2
277
d Test 3.5.8.5-if 00010 three 2nd else
278
D Test 3.5.8.5-if 00101 three 2nd else
279
D Test 3.5.8.5-if 00102 three 2nd else
280
D Test 3.5.8.5-if 00103 three 2nd else
281
create trigger trg3 before update on tb3 for each row
283
ELSEIF new.f120='2' then
286
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
290
create trigger trg4 before update on tb3 for each row
292
IF (new.f120='4') and (new.f136=10) then
293
set @test_var2='2nd if', new.f120='d';
295
set @test_var2='2nd else', new.f120='D';
297
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
300
delete from tb3 where f121='Test 3.5.8.5-if';
302
Testcase 3.5.8.5-case:
303
----------------------
304
create trigger trg3 before insert on tb3 for each row
306
SET new.f120=char(ascii(new.f120)-32);
308
when new.f136<100 then set new.f136=new.f136+120;
309
when new.f136<10 then set new.f144=777;
310
when new.f136>100 then set new.f120=new.f136-1;
313
when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
314
ELSE set @test_var=concat(new.f120, '*');
317
when 1 then set @test_var=concat(@test_var, 'one');
318
when 2 then set @test_var=concat(@test_var, 'two');
319
when 3 then set @test_var=concat(@test_var, 'three');
320
when 4 then set @test_var=concat(@test_var, 'four');
321
when 5 then set @test_var=concat(@test_var, 'five');
322
when 6 then set @test_var=concat(@test_var, 'six');
323
when 7 then set @test_var=concat(@test_var, 'seven');
324
when 8 then set @test_var=concat(@test_var, 'eight');
325
when 9 then set @test_var=concat(@test_var, 'nine');
326
when 10 then set @test_var=concat(@test_var, 'ten');
327
when 11 then set @test_var=concat(@test_var, 'eleven');
328
when 12 then set @test_var=concat(@test_var, 'twelve');
329
when 13 then set @test_var=concat(@test_var, 'thirteen');
330
when 14 then set @test_var=concat(@test_var, 'fourteen');
331
when 15 then set @test_var=concat(@test_var, 'fifteen');
332
ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
335
set @test_var='Empty';
336
Insert into tb3 (f120, f122, f136, f144)
337
values ('a', 'Test 3.5.8.5-case', 5, 7);
338
select f120, f122, f136, f144, @test_var
339
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
340
f120 f122 f136 f144 @test_var
341
A Test 3.5.8.5-case 00125 0000000007 A*seven
342
Insert into tb3 (f120, f122, f136, f144)
343
values ('b', 'Test 3.5.8.5-case', 71,16);
344
select f120, f122, f136, f144, @test_var
345
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
346
f120 f122 f136 f144 @test_var
347
A Test 3.5.8.5-case 00125 0000000007 B*0000000016
348
B Test 3.5.8.5-case 00191 0000000016 B*0000000016
349
Insert into tb3 (f120, f122, f136, f144)
350
values ('c', 'Test 3.5.8.5-case', 80,1);
351
select f120, f122, f136, f144, @test_var
352
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
353
f120 f122 f136 f144 @test_var
354
A Test 3.5.8.5-case 00125 0000000007 C=one
355
B Test 3.5.8.5-case 00191 0000000016 C=one
356
C Test 3.5.8.5-case 00200 0000000001 C=one
357
Insert into tb3 (f120, f122, f136)
358
values ('d', 'Test 3.5.8.5-case', 152);
360
Warning 1265 Data truncated for column 'f120' at row 1
361
select f120, f122, f136, f144, @test_var
362
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
363
f120 f122 f136 f144 @test_var
364
1 Test 3.5.8.5-case 00152 0000099999 1*0000099999
365
A Test 3.5.8.5-case 00125 0000000007 1*0000099999
366
B Test 3.5.8.5-case 00191 0000000016 1*0000099999
367
C Test 3.5.8.5-case 00200 0000000001 1*0000099999
368
Insert into tb3 (f120, f122, f136, f144)
369
values ('e', 'Test 3.5.8.5-case', 200, 8);
371
Warning 1265 Data truncated for column 'f120' at row 1
372
select f120, f122, f136, f144, @test_var
373
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
374
f120 f122 f136 f144 @test_var
375
1 Test 3.5.8.5-case 00152 0000099999 1=eight
376
1 Test 3.5.8.5-case 00200 0000000008 1=eight
377
A Test 3.5.8.5-case 00125 0000000007 1=eight
378
B Test 3.5.8.5-case 00191 0000000016 1=eight
379
C Test 3.5.8.5-case 00200 0000000001 1=eight
380
Insert into tb3 (f120, f122, f136, f144)
381
values ('f', 'Test 3.5.8.5-case', 100, 8);
382
select f120, f122, f136, f144, @test_var
383
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
384
f120 f122 f136 f144 @test_var
385
1 Test 3.5.8.5-case 00152 0000099999 1=eight
386
1 Test 3.5.8.5-case 00200 0000000008 1=eight
387
A Test 3.5.8.5-case 00125 0000000007 1=eight
388
B Test 3.5.8.5-case 00191 0000000016 1=eight
389
C Test 3.5.8.5-case 00200 0000000001 1=eight
390
create trigger trg3a before update on tb3 for each row
393
when new.f136<100 then set new.f120='p';
395
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
398
delete from tb3 where f121='Test 3.5.8.5-case';
400
Testcase 3.5.8.5-loop/leave:
401
----------------------------
402
Create trigger trg4 after insert on tb3 for each row
404
set @counter=0, @flag='Initial';
406
if new.f136<new.f144 then
407
set @counter='Nothing to loop';
410
set @counter=@counter+1;
411
if new.f136=new.f144+@counter then
412
set @counter=concat(@counter, ' loops');
420
Insert into tb3 (f122, f136, f144)
421
values ('Test 3.5.8.5-loop', 2, 8);
422
select @counter, @flag;
424
Nothing to loop Initial
425
Insert into tb3 (f122, f136, f144)
426
values ('Test 3.5.8.5-loop', 11, 8);
427
select @counter, @flag;
430
Create trigger trg4_2 after update on tb3 for each row
433
set @counter=@counter+1;
436
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 ';
440
delete from tb3 where f122='Test 3.5.8.5-loop';
442
Testcase 3.5.8.5-repeat:
443
------------------------
444
Create trigger trg6 after insert on tb3 for each row
447
SET @counter1 = @counter1 + 1;
448
IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label;
450
SET @counter2 = @counter2 + 1;
451
UNTIL @counter1> new.f136 END REPEAT rp_label;
453
set @counter1= 0, @counter2= 0;
454
Insert into tb3 (f122, f136)
455
values ('Test 3.5.8.5-repeat', 13);
456
select @counter1, @counter2;
459
Create trigger trg6_2 after update on tb3 for each row
462
SET @counter2 = @counter2 + 1;
464
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
466
delete from tb3 where f122='Test 3.5.8.5-repeat';
468
Testcase 3.5.8.5-while:
469
-----------------------
470
Create trigger trg7 after insert on tb3 for each row
471
wl_label: WHILE @counter1 < new.f136 DO
472
SET @counter1 = @counter1 + 1;
473
IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label;
475
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;
488
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;
490
delete from tb3 where f122='Test 3.5.8.5-while';
493
Testcase 3.5.8.6: (requirement void)
494
------------------------------------
495
CREATE PROCEDURE sp_01 () BEGIN set @v1=1; END//
496
CREATE TRIGGER trg8_1 BEFORE UPDATE ON tb3 FOR EACH ROW
500
Insert into tb3 (f120, f122, f136) values ('6', 'Test 3.5.8.6-insert', 101);
501
update tb3 set f120='S', f136=111,
502
f122='Test 3.5.8.6-tr8_1'
503
where f122='Test 3.5.8.6-insert';
505
from tb3 where f122 like 'Test 3.5.8.6%' order by f120;
509
DROP PROCEDURE sp_01;
513
Create trigger trg9_1 before update on tb3 for each row
519
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
520
Create trigger trg9_2 before delete on tb3 for each row
526
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
527
drop user test_general@localhost;
528
drop user test_general;
529
drop user test_super@localhost;
added
removed
mysql-test/suite/funcs_1/r/myisam_trig_08.result
