1
SET @NO_REFRESH = IF( '' = '', 0, 1);
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',
11
f124 longtext unicode,
16
f129 binary not null DEFAULT b'101',
17
f130 tinyint not null DEFAULT 99,
18
f131 tinyint unsigned not null DEFAULT 99,
19
f132 tinyint zerofill not null DEFAULT 99,
20
f133 tinyint unsigned zerofill not null DEFAULT 99,
21
f134 smallint not null DEFAULT 999,
22
f135 smallint unsigned not null DEFAULT 999,
23
f136 smallint zerofill not null DEFAULT 999,
24
f137 smallint unsigned zerofill not null DEFAULT 999,
25
f138 mediumint not null DEFAULT 9999,
26
f139 mediumint unsigned not null DEFAULT 9999,
27
f140 mediumint zerofill not null DEFAULT 9999,
28
f141 mediumint unsigned zerofill not null DEFAULT 9999,
29
f142 int not null DEFAULT 99999,
30
f143 int unsigned not null DEFAULT 99999,
31
f144 int zerofill not null DEFAULT 99999,
32
f145 int unsigned zerofill not null DEFAULT 99999,
33
f146 bigint not null DEFAULT 999999,
34
f147 bigint unsigned not null DEFAULT 999999,
35
f148 bigint zerofill not null DEFAULT 999999,
36
f149 bigint unsigned zerofill not null DEFAULT 999999,
37
f150 decimal not null DEFAULT 999.999,
38
f151 decimal unsigned not null DEFAULT 999.17,
39
f152 decimal zerofill not null DEFAULT 999.999,
40
f153 decimal unsigned zerofill,
43
f156 decimal (0) unsigned,
44
f157 decimal (64) unsigned,
45
f158 decimal (0) zerofill,
46
f159 decimal (64) zerofill,
47
f160 decimal (0) unsigned zerofill,
48
f161 decimal (64) unsigned zerofill,
51
f164 decimal (0,0) unsigned,
52
f165 decimal (63,30) unsigned,
53
f166 decimal (0,0) zerofill,
54
f167 decimal (63,30) zerofill,
55
f168 decimal (0,0) unsigned zerofill,
56
f169 decimal (63,30) unsigned zerofill,
58
f171 numeric unsigned,
59
f172 numeric zerofill,
60
f173 numeric unsigned zerofill,
65
Note 1265 Data truncated for column 'f150' at row 1
66
Note 1265 Data truncated for column 'f151' at row 1
67
Note 1265 Data truncated for column 'f152' at row 1
68
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/myisam_tb3.txt' into table tb3 ;
70
Testcase 3.5.10.1/2/3:
71
----------------------
72
Create view vw11 as select * from tb3
73
where f122 like 'Test 3.5.10.1/2/3%';
74
Create trigger trg1a before insert on tb3
75
for each row set new.f163=111.11;
76
Create trigger trg1b after insert on tb3
77
for each row set @test_var='After Insert';
78
Create trigger trg1c before update on tb3
79
for each row set new.f121='Y', new.f122='Test 3.5.10.1/2/3-Update';
80
Create trigger trg1d after update on tb3
81
for each row set @test_var='After Update';
82
Create trigger trg1e before delete on tb3
83
for each row set @test_var=5;
84
Create trigger trg1f after delete on tb3
85
for each row set @test_var= 2* @test_var+7;
86
Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 1);
87
Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 2);
88
Insert into vw11 (f122, f151) values ('Not in View', 3);
89
select f121, f122, f151, f163
90
from tb3 where f122 like 'Test 3.5.10.1/2/3%';
92
NULL Test 3.5.10.1/2/3 1 111.110000000000000000000000000000
93
NULL Test 3.5.10.1/2/3 2 111.110000000000000000000000000000
94
select f121, f122, f151, f163 from vw11;
96
NULL Test 3.5.10.1/2/3 1 111.110000000000000000000000000000
97
NULL Test 3.5.10.1/2/3 2 111.110000000000000000000000000000
98
select f121, f122, f151, f163
99
from tb3 where f122 like 'Not in View';
101
NULL Not in View 3 111.110000000000000000000000000000
102
Update vw11 set f163=1;
103
select f121, f122, f151, f163 from tb3
104
where f122 like 'Test 3.5.10.1/2/3%';
106
Y Test 3.5.10.1/2/3-Update 1 1.000000000000000000000000000000
107
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
108
select f121, f122, f151, f163 from vw11;
110
Y Test 3.5.10.1/2/3-Update 1 1.000000000000000000000000000000
111
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
113
Select @test_var as 'before delete';
116
delete from vw11 where f151=1;
117
select f121, f122, f151, f163 from tb3
118
where f122 like 'Test 3.5.10.1/2/3%';
120
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
121
select f121, f122, f151, f163 from vw11;
123
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
124
Select @test_var as 'after delete';
134
delete from tb3 where f122 like 'Test 3.5.10.1/2/3%';
138
create table tb_load (f1 int, f2 char(25),f3 int) engine=myisam;
139
Create trigger trg4 before insert on tb_load
140
for each row set new.f3=-(new.f1 div 5), @counter= @counter+1;
142
select @counter as 'Rows Loaded Before';
145
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table tb_load;
146
select @counter as 'Rows Loaded After';
149
Select * from tb_load limit 10;
160
-4991 a_aaaaaaaaa 998
164
Testcase 3.5.10.5: (implemented in trig_frkey.test)
165
---------------------------------------------------
167
Testcase 3.5.10.6: (implemented in trig_frkey.test)
168
---------------------------------------------------
170
Testcase 3.5.10.extra:
171
----------------------
172
create table t1_sp (var136 tinyint, var151 decimal) engine=myisam;
173
create trigger trg before insert on t1_sp
174
for each row set @counter=@counter+1;
175
create procedure trig_sp()
177
declare done int default 0;
178
declare var151 decimal;
179
declare var136 tinyint;
180
declare cur1 cursor for select f136, f151 from tb3;
181
declare continue handler for sqlstate '01000' set done = 1;
183
fetch cur1 into var136, var151;
184
wl_loop: WHILE NOT done DO
185
insert into t1_sp values (var136, var151);
186
fetch cur1 into var136, var151;
195
ERROR 02000: No data - zero rows fetched, selected, or processed
199
select count(*) from tb3;
202
select count(*) from t1_sp;
205
drop procedure trig_sp;
209
Testcase 3.5.11.1 (implemented in trig_perf.test)
210
-------------------------------------------------
212
Testcase y.y.y.2: Check for triggers starting triggers
213
------------------------------------------------------
215
drop table if exists t1;
216
drop table if exists t2_1;
217
drop table if exists t2_2;
218
drop table if exists t2_3;
219
drop table if exists t2_4;
220
drop table if exists t3;
221
create table t1 (f1 integer) engine=myisam;
222
create table t2_1 (f1 integer) engine=myisam;
223
create table t2_2 (f1 integer) engine=myisam;
224
create table t2_3 (f1 integer) engine=myisam;
225
create table t2_4 (f1 integer) engine=myisam;
226
create table t3 (f1 integer) engine=myisam;
227
insert into t1 values (1);
228
create trigger tr1 after insert on t1 for each row
230
insert into t2_1 (f1) values (new.f1+1);
231
insert into t2_2 (f1) values (new.f1+1);
232
insert into t2_3 (f1) values (new.f1+1);
233
insert into t2_4 (f1) values (new.f1+1);
235
create trigger tr2_1 after insert on t2_1 for each row
236
insert into t3 (f1) values (new.f1+10);
237
create trigger tr2_2 after insert on t2_2 for each row
238
insert into t3 (f1) values (new.f1+100);
239
create trigger tr2_3 after insert on t2_3 for each row
240
insert into t3 (f1) values (new.f1+1000);
241
create trigger tr2_4 after insert on t2_4 for each row
242
insert into t3 (f1) values (new.f1+10000);
243
insert into t1 values (1);
255
drop table t1, t2_1, t2_2, t2_3, t2_4, t3;
257
Testcase y.y.y.3: Circular trigger reference
258
--------------------------------------------
260
drop table if exists t1;
261
drop table if exists t2;
262
drop table if exists t3;
263
drop table if exists t4;
264
create table t1 (f1 integer) engine = myisam;
265
create table t2 (f2 integer) engine = myisam;
266
create table t3 (f3 integer) engine = myisam;
267
create table t4 (f4 integer) engine = myisam;
268
insert into t1 values (0);
269
create trigger tr1 after insert on t1
270
for each row insert into t2 (f2) values (new.f1+1);
271
create trigger tr2 after insert on t2
272
for each row insert into t3 (f3) values (new.f2+1);
273
create trigger tr3 after insert on t3
274
for each row insert into t4 (f4) values (new.f3+1);
275
create trigger tr4 after insert on t4
276
for each row insert into t1 (f1) values (new.f4+1);
277
insert into t1 values (1);
278
ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
301
Testcase y.y.y.4: Recursive trigger/SP references (disabled bug 11889)
302
----------------------------------------------------------------------
303
set @sql_mode='traditional';
307
var151 decimal) engine=myisam;
308
create procedure trig_sp()
310
declare done int default 0;
311
declare var151 decimal;
312
declare var136 tinyint;
313
declare cur1 cursor for select f136, f151 from tb3;
314
declare continue handler for sqlstate '01000' set done = 1;
315
set @counter= @counter+1;
317
fetch cur1 into var136, var151;
318
wl_loop: WHILE NOT done DO
319
insert into t1_sp values (@counter, var136, var151);
320
fetch cur1 into var136, var151;
324
create trigger trg before insert on t1_sp
325
for each row call trig_sp();
331
ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine trig_sp
335
select count(*) from tb3;
338
select count(*) from t1_sp;
341
set @@max_sp_recursion_depth= 10;
347
ERROR HY000: Can't update table 't1_sp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
351
select count(*) from tb3;
354
select count(*) from t1_sp;
357
drop procedure trig_sp;
361
Testcase y.y.y.5: Roleback of nested trigger references
362
-------------------------------------------------------
363
set @@sql_mode='traditional';
365
drop table if exists t1;
366
drop table if exists t2;
367
drop table if exists t3;
368
drop table if exists t4;
369
create table t1 (f1 integer) engine = myisam;
370
create table t2 (f2 integer) engine = myisam;
371
create table t3 (f3 integer) engine = myisam;
372
create table t4 (f4 tinyint) engine = myisam;
373
show create table t1;
375
t1 CREATE TABLE `t1` (
376
`f1` int(11) default NULL
377
) ENGINE=MyISAM DEFAULT CHARSET=latin1
378
insert into t1 values (1);
379
create trigger tr1 after insert on t1
380
for each row insert into t2 (f2) values (new.f1+1);
381
create trigger tr2 after insert on t2
382
for each row insert into t3 (f3) values (new.f2+1);
383
create trigger tr3 after insert on t3
384
for each row insert into t4 (f4) values (new.f3+1000);
387
insert into t1 values (1);
388
ERROR 22003: Out of range value adjusted for column 'f4' at row 1