2
drop table if exists tb3;
4
f118 char not null DEFAULT 'a',
5
f119 char binary not null DEFAULT b'101',
6
f120 char ascii not null DEFAULT b'101',
9
f129 binary not null DEFAULT b'101',
10
f130 tinyint not null DEFAULT 99,
11
f131 tinyint unsigned not null DEFAULT 99,
12
f132 tinyint zerofill not null DEFAULT 99,
13
f133 tinyint unsigned zerofill not null DEFAULT 99,
14
f134 smallint not null DEFAULT 999,
15
f135 smallint unsigned not null DEFAULT 999,
16
f136 smallint zerofill not null DEFAULT 999,
17
f137 smallint unsigned zerofill not null DEFAULT 999,
18
f138 mediumint not null DEFAULT 9999,
19
f139 mediumint unsigned not null DEFAULT 9999,
20
f140 mediumint zerofill not null DEFAULT 9999,
21
f141 mediumint unsigned zerofill not null DEFAULT 9999,
22
f142 int not null DEFAULT 99999,
23
f143 int unsigned not null DEFAULT 99999,
24
f144 int zerofill not null DEFAULT 99999,
25
f145 int unsigned zerofill not null DEFAULT 99999,
26
f146 bigint not null DEFAULT 999999,
27
f147 bigint unsigned not null DEFAULT 999999,
28
f148 bigint zerofill not null DEFAULT 999999,
29
f149 bigint unsigned zerofill not null DEFAULT 999999,
30
f150 decimal not null DEFAULT 999.999,
31
f151 decimal unsigned not null DEFAULT 999.17,
32
f152 decimal zerofill not null DEFAULT 999.999,
33
f153 decimal unsigned zerofill,
36
f156 decimal (0) unsigned,
37
f157 decimal (64) unsigned,
38
f158 decimal (0) zerofill,
39
f159 decimal (64) zerofill,
40
f160 decimal (0) unsigned zerofill,
41
f161 decimal (64) unsigned zerofill,
44
f164 decimal (0,0) unsigned,
45
f165 decimal (63,30) unsigned,
46
f166 decimal (0,0) zerofill,
47
f167 decimal (63,30) zerofill,
48
f168 decimal (0,0) unsigned zerofill,
49
f169 decimal (63,30) unsigned zerofill,
51
f171 numeric unsigned,
52
f172 numeric zerofill,
53
f173 numeric unsigned zerofill,
56
) engine = <engine_to_be_used>;
58
Note 1265 Data truncated for column 'f150' at row 1
59
Note 1265 Data truncated for column 'f151' at row 1
60
Note 1265 Data truncated for column 'f152' at row 1
61
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/memory_tb3.txt'
64
Testcase 3.5.10.1/2/3:
65
----------------------
66
Create view vw11 as select * from tb3
67
where f122 like 'Test 3.5.10.1/2/3%';
68
Create trigger trg1a before insert on tb3
69
for each row set new.f163=111.11;
70
Create trigger trg1b after insert on tb3
71
for each row set @test_var='After Insert';
72
Create trigger trg1c before update on tb3
73
for each row set new.f121='Y', new.f122='Test 3.5.10.1/2/3-Update';
74
Create trigger trg1d after update on tb3
75
for each row set @test_var='After Update';
76
Create trigger trg1e before delete on tb3
77
for each row set @test_var=5;
78
Create trigger trg1f after delete on tb3
79
for each row set @test_var= 2* @test_var+7;
80
Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 1);
81
Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 2);
82
Insert into vw11 (f122, f151) values ('Not in View', 3);
83
select f121, f122, f151, f163
84
from tb3 where f122 like 'Test 3.5.10.1/2/3%' order by f151;
86
NULL Test 3.5.10.1/2/3 1 111.110000000000000000000000000000
87
NULL Test 3.5.10.1/2/3 2 111.110000000000000000000000000000
88
select f121, f122, f151, f163 from vw11;
90
NULL Test 3.5.10.1/2/3 1 111.110000000000000000000000000000
91
NULL Test 3.5.10.1/2/3 2 111.110000000000000000000000000000
92
select f121, f122, f151, f163
93
from tb3 where f122 like 'Not in View';
95
NULL Not in View 3 111.110000000000000000000000000000
96
Update vw11 set f163=1;
97
select f121, f122, f151, f163 from tb3
98
where f122 like 'Test 3.5.10.1/2/3%' order by f151;
100
Y Test 3.5.10.1/2/3-Update 1 1.000000000000000000000000000000
101
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
102
select f121, f122, f151, f163 from vw11;
104
Y Test 3.5.10.1/2/3-Update 1 1.000000000000000000000000000000
105
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
107
Select @test_var as 'before delete';
110
delete from vw11 where f151=1;
111
select f121, f122, f151, f163 from tb3
112
where f122 like 'Test 3.5.10.1/2/3%' order by f151;
114
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
115
select f121, f122, f151, f163 from vw11;
117
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
118
Select @test_var as 'after delete';
128
delete from tb3 where f122 like 'Test 3.5.10.1/2/3%';
132
create table tb_load (f1 int, f2 char(25),f3 int) engine = <engine_to_be_used>;
133
Create trigger trg4 before insert on tb_load
134
for each row set new.f3=-(new.f1 div 5), @counter= @counter+1;
136
select @counter as 'Rows Loaded Before';
139
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table tb_load;
140
select @counter as 'Rows Loaded After';
143
Select * from tb_load order by f1 limit 10;
154
-4991 a_aaaaaaaaa 998
158
Testcase 3.5.10.5: (implemented in trig_frkey.test)
159
---------------------------------------------------
161
Testcase 3.5.10.6: (implemented in trig_frkey.test)
162
---------------------------------------------------
164
Testcase 3.5.10.extra:
165
----------------------
166
create table t1_sp (var136 tinyint, var151 decimal) engine = <engine_to_be_used>;
167
create trigger trg before insert on t1_sp
168
for each row set @counter=@counter+1;
169
create procedure trig_sp()
171
declare done int default 0;
172
declare var151 decimal;
173
declare var136 tinyint;
174
declare cur1 cursor for select f136, f151 from tb3;
175
declare continue handler for sqlstate '01000' set done = 1;
177
fetch cur1 into var136, var151;
178
wl_loop: WHILE NOT done DO
179
insert into t1_sp values (var136, var151);
180
fetch cur1 into var136, var151;
189
ERROR 02000: No data - zero rows fetched, selected, or processed
193
select count(*) from tb3;
196
select count(*) from t1_sp;
199
drop procedure trig_sp;
203
Testcase 3.5.11.1 (implemented in trig_perf.test)
204
-------------------------------------------------
206
Testcase y.y.y.2: Check for triggers starting triggers
207
------------------------------------------------------
209
drop table if exists t1;
210
drop table if exists t2_1;
211
drop table if exists t2_2;
212
drop table if exists t2_3;
213
drop table if exists t2_4;
214
drop table if exists t3;
215
create table t1 (f1 integer) engine = <engine_to_be_used>;
216
create table t2_1 (f1 integer) engine = <engine_to_be_used>;
217
create table t2_2 (f1 integer) engine = <engine_to_be_used>;
218
create table t2_3 (f1 integer) engine = <engine_to_be_used>;
219
create table t2_4 (f1 integer) engine = <engine_to_be_used>;
220
create table t3 (f1 integer) engine = <engine_to_be_used>;
221
insert into t1 values (1);
222
create trigger tr1 after insert on t1 for each row
224
insert into t2_1 (f1) values (new.f1+1);
225
insert into t2_2 (f1) values (new.f1+1);
226
insert into t2_3 (f1) values (new.f1+1);
227
insert into t2_4 (f1) values (new.f1+1);
229
create trigger tr2_1 after insert on t2_1 for each row
230
insert into t3 (f1) values (new.f1+10);
231
create trigger tr2_2 after insert on t2_2 for each row
232
insert into t3 (f1) values (new.f1+100);
233
create trigger tr2_3 after insert on t2_3 for each row
234
insert into t3 (f1) values (new.f1+1000);
235
create trigger tr2_4 after insert on t2_4 for each row
236
insert into t3 (f1) values (new.f1+10000);
237
insert into t1 values (1);
238
select * from t3 order by f1;
249
drop table t1, t2_1, t2_2, t2_3, t2_4, t3;
251
Testcase y.y.y.3: Circular trigger reference
252
--------------------------------------------
254
drop table if exists t1;
255
drop table if exists t2;
256
drop table if exists t3;
257
drop table if exists t4;
258
create table t1 (f1 integer) engine = <engine_to_be_used>;
259
create table t2 (f2 integer) engine = <engine_to_be_used>;
260
create table t3 (f3 integer) engine = <engine_to_be_used>;
261
create table t4 (f4 integer) engine = <engine_to_be_used>;
262
insert into t1 values (0);
263
create trigger tr1 after insert on t1
264
for each row insert into t2 (f2) values (new.f1+1);
265
create trigger tr2 after insert on t2
266
for each row insert into t3 (f3) values (new.f2+1);
267
create trigger tr3 after insert on t3
268
for each row insert into t4 (f4) values (new.f3+1);
269
create trigger tr4 after insert on t4
270
for each row insert into t1 (f1) values (new.f4+1);
271
insert into t1 values (1);
272
ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
273
select * from t1 order by f1;
276
select * from t2 order by f2;
278
select * from t3 order by f3;
280
select * from t4 order by f4;
291
Testcase y.y.y.4: Recursive trigger/SP references
292
-------------------------------------------------
293
set @sql_mode='traditional';
297
var151 decimal) engine = <engine_to_be_used>;
298
create procedure trig_sp()
300
declare done int default 0;
301
declare var151 decimal;
302
declare var136 tinyint;
303
declare cur1 cursor for select f136, f151 from tb3;
304
declare continue handler for sqlstate '01000' set done = 1;
305
set @counter= @counter+1;
307
fetch cur1 into var136, var151;
308
wl_loop: WHILE NOT done DO
309
insert into t1_sp values (@counter, var136, var151);
310
fetch cur1 into var136, var151;
314
create trigger trg before insert on t1_sp
315
for each row call trig_sp();
321
ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine trig_sp
325
select count(*) from tb3;
328
select count(*) from t1_sp;
331
set @@max_sp_recursion_depth= 10;
337
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.
341
select count(*) from tb3;
344
select count(*) from t1_sp;
347
drop procedure trig_sp;
351
Testcase y.y.y.5: Rollback of nested trigger references
352
-------------------------------------------------------
353
set @@sql_mode='traditional';
355
drop table if exists t1;
356
drop table if exists t2;
357
drop table if exists t3;
358
drop table if exists t4;
359
create table t1 (f1 integer) engine = <engine_to_be_used>;
360
create table t2 (f2 integer) engine = <engine_to_be_used>;
361
create table t3 (f3 integer) engine = <engine_to_be_used>;
362
create table t4 (f4 tinyint) engine = <engine_to_be_used>;
363
show create table t1;
365
t1 CREATE TABLE `t1` (
366
`f1` int(11) DEFAULT NULL
367
) ENGINE=InnoDB DEFAULT CHARSET=latin1
368
insert into t1 values (1);
369
create trigger tr1 after insert on t1
370
for each row insert into t2 (f2) values (new.f1+1);
371
create trigger tr2 after insert on t2
372
for each row insert into t3 (f3) values (new.f2+1);
373
create trigger tr3 after insert on t3
374
for each row insert into t4 (f4) values (new.f3+1000);
377
insert into t1 values (1);
378
ERROR 22003: Out of range value for column 'f4' at row 1
380
select * from t1 order by f1;
383
select * from t2 order by f2;
385
select * from t3 order by f3;