1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
|
SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
USE test;
drop table if exists tb3;
create table tb3 (
f118 char not null DEFAULT 'a',
f119 char binary not null DEFAULT b'101',
f120 char ascii not null DEFAULT b'101',
f121 char(50),
f122 char(50),
f129 binary not null DEFAULT b'101',
f130 tinyint not null DEFAULT 99,
f131 tinyint unsigned not null DEFAULT 99,
f132 tinyint zerofill not null DEFAULT 99,
f133 tinyint unsigned zerofill not null DEFAULT 99,
f134 smallint not null DEFAULT 999,
f135 smallint unsigned not null DEFAULT 999,
f136 smallint zerofill not null DEFAULT 999,
f137 smallint unsigned zerofill not null DEFAULT 999,
f138 mediumint not null DEFAULT 9999,
f139 mediumint unsigned not null DEFAULT 9999,
f140 mediumint zerofill not null DEFAULT 9999,
f141 mediumint unsigned zerofill not null DEFAULT 9999,
f142 int not null DEFAULT 99999,
f143 int unsigned not null DEFAULT 99999,
f144 int zerofill not null DEFAULT 99999,
f145 int unsigned zerofill not null DEFAULT 99999,
f146 bigint not null DEFAULT 999999,
f147 bigint unsigned not null DEFAULT 999999,
f148 bigint zerofill not null DEFAULT 999999,
f149 bigint unsigned zerofill not null DEFAULT 999999,
f150 decimal not null DEFAULT 999.999,
f151 decimal unsigned not null DEFAULT 999.17,
f152 decimal zerofill not null DEFAULT 999.999,
f153 decimal unsigned zerofill,
f154 decimal (0),
f155 decimal (64),
f156 decimal (0) unsigned,
f157 decimal (64) unsigned,
f158 decimal (0) zerofill,
f159 decimal (64) zerofill,
f160 decimal (0) unsigned zerofill,
f161 decimal (64) unsigned zerofill,
f162 decimal (0,0),
f163 decimal (63,30),
f164 decimal (0,0) unsigned,
f165 decimal (63,30) unsigned,
f166 decimal (0,0) zerofill,
f167 decimal (63,30) zerofill,
f168 decimal (0,0) unsigned zerofill,
f169 decimal (63,30) unsigned zerofill,
f170 numeric,
f171 numeric unsigned,
f172 numeric zerofill,
f173 numeric unsigned zerofill,
f174 numeric (0),
f175 numeric (64)
) engine = <engine_to_be_used>;
Warnings:
Note 1265 Data truncated for column 'f150' at row 0
Note 1265 Data truncated for column 'f151' at row 0
Note 1265 Data truncated for column 'f152' at row 0
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/memory_tb3.txt'
into table tb3;
Testcase 3.5.10.1/2/3:
----------------------
Create view vw11 as select * from tb3
where f122 like 'Test 3.5.10.1/2/3%';
Create trigger trg1a before insert on tb3
for each row set new.f163=111.11;
Create trigger trg1b after insert on tb3
for each row set @test_var='After Insert';
Create trigger trg1c before update on tb3
for each row set new.f121='Y', new.f122='Test 3.5.10.1/2/3-Update';
Create trigger trg1d after update on tb3
for each row set @test_var='After Update';
Create trigger trg1e before delete on tb3
for each row set @test_var=5;
Create trigger trg1f after delete on tb3
for each row set @test_var= 2* @test_var+7;
Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 1);
Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 2);
Insert into vw11 (f122, f151) values ('Not in View', 3);
select f121, f122, f151, f163
from tb3 where f122 like 'Test 3.5.10.1/2/3%' order by f151;
f121 f122 f151 f163
NULL Test 3.5.10.1/2/3 1 111.110000000000000000000000000000
NULL Test 3.5.10.1/2/3 2 111.110000000000000000000000000000
select f121, f122, f151, f163 from vw11;
f121 f122 f151 f163
NULL Test 3.5.10.1/2/3 1 111.110000000000000000000000000000
NULL Test 3.5.10.1/2/3 2 111.110000000000000000000000000000
select f121, f122, f151, f163
from tb3 where f122 like 'Not in View';
f121 f122 f151 f163
NULL Not in View 3 111.110000000000000000000000000000
Update vw11 set f163=1;
select f121, f122, f151, f163 from tb3
where f122 like 'Test 3.5.10.1/2/3%' order by f151;
f121 f122 f151 f163
Y Test 3.5.10.1/2/3-Update 1 1.000000000000000000000000000000
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
select f121, f122, f151, f163 from vw11;
f121 f122 f151 f163
Y Test 3.5.10.1/2/3-Update 1 1.000000000000000000000000000000
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
set @test_var=0;
Select @test_var as 'before delete';
before delete
0
delete from vw11 where f151=1;
select f121, f122, f151, f163 from tb3
where f122 like 'Test 3.5.10.1/2/3%' order by f151;
f121 f122 f151 f163
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
select f121, f122, f151, f163 from vw11;
f121 f122 f151 f163
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
Select @test_var as 'after delete';
after delete
17
drop view vw11;
drop trigger trg1a;
drop trigger trg1b;
drop trigger trg1c;
drop trigger trg1d;
drop trigger trg1e;
drop trigger trg1f;
delete from tb3 where f122 like 'Test 3.5.10.1/2/3%';
Testcase 3.5.10.4:
------------------
create table tb_load (f1 int, f2 char(25),f3 int) engine = <engine_to_be_used>;
Create trigger trg4 before insert on tb_load
for each row set new.f3=-(new.f1 div 5), @counter= @counter+1;
set @counter= 0;
select @counter as 'Rows Loaded Before';
Rows Loaded Before
0
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table tb_load;
select @counter as 'Rows Loaded After';
Rows Loaded After
10
Select * from tb_load order by f1 limit 10;
f1 f2 f3
-5000 a` 1000
-4999 aaa 999
-4998 abaa 999
-4997 acaaa 999
-4996 adaaaa 999
-4995 aeaaaaa 999
-4994 afaaaaaa 998
-4993 agaaaaaaa 998
-4992 a^aaaaaaaa 998
-4991 a_aaaaaaaaa 998
drop trigger trg4;
drop table tb_load;
Testcase 3.5.10.5: (implemented in trig_frkey.test)
---------------------------------------------------
Testcase 3.5.10.6: (implemented in trig_frkey.test)
---------------------------------------------------
Testcase 3.5.10.extra:
----------------------
set sql_mode = 'NO_ENGINE_SUBSTITUTION';
create table t1_sp (var136 tinyint, var151 decimal) engine = <engine_to_be_used>;
create trigger trg before insert on t1_sp
for each row set @counter=@counter+1;
create procedure trig_sp()
begin
declare done int default 0;
declare var151 decimal;
declare var136 tinyint;
declare cur1 cursor for select f136, f151 from tb3;
declare continue handler for sqlstate '01000' set done = 1;
open cur1;
fetch cur1 into var136, var151;
wl_loop: WHILE NOT done DO
insert into t1_sp values (var136, var151);
fetch cur1 into var136, var151;
END WHILE wl_loop;
close cur1;
end//
set @counter=0;
select @counter;
@counter
0
call trig_sp();
ERROR 02000: No data - zero rows fetched, selected, or processed
select @counter;
@counter
11
select count(*) from tb3;
count(*)
11
select count(*) from t1_sp;
count(*)
11
drop procedure trig_sp;
drop trigger trg;
drop table t1_sp;
set sql_mode = default;
Testcase 3.5.11.1 (implemented in trig_perf.test)
-------------------------------------------------
Testcase y.y.y.2: Check for triggers starting triggers
------------------------------------------------------
use test;
drop table if exists t1;
drop table if exists t2_1;
drop table if exists t2_2;
drop table if exists t2_3;
drop table if exists t2_4;
drop table if exists t3;
create table t1 (f1 integer) engine = <engine_to_be_used>;
create table t2_1 (f1 integer) engine = <engine_to_be_used>;
create table t2_2 (f1 integer) engine = <engine_to_be_used>;
create table t2_3 (f1 integer) engine = <engine_to_be_used>;
create table t2_4 (f1 integer) engine = <engine_to_be_used>;
create table t3 (f1 integer) engine = <engine_to_be_used>;
insert into t1 values (1);
create trigger tr1 after insert on t1 for each row
BEGIN
insert into t2_1 (f1) values (new.f1+1);
insert into t2_2 (f1) values (new.f1+1);
insert into t2_3 (f1) values (new.f1+1);
insert into t2_4 (f1) values (new.f1+1);
END//
create trigger tr2_1 after insert on t2_1 for each row
insert into t3 (f1) values (new.f1+10);
create trigger tr2_2 after insert on t2_2 for each row
insert into t3 (f1) values (new.f1+100);
create trigger tr2_3 after insert on t2_3 for each row
insert into t3 (f1) values (new.f1+1000);
create trigger tr2_4 after insert on t2_4 for each row
insert into t3 (f1) values (new.f1+10000);
insert into t1 values (1);
select * from t3 order by f1;
f1
12
102
1002
10002
drop trigger tr1;
drop trigger tr2_1;
drop trigger tr2_2;
drop trigger tr2_3;
drop trigger tr2_4;
drop table t1, t2_1, t2_2, t2_3, t2_4, t3;
Testcase y.y.y.3: Circular trigger reference
--------------------------------------------
use test;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t1 (f1 integer) engine = <engine_to_be_used>;
create table t2 (f2 integer) engine = <engine_to_be_used>;
create table t3 (f3 integer) engine = <engine_to_be_used>;
create table t4 (f4 integer) engine = <engine_to_be_used>;
insert into t1 values (0);
create trigger tr1 after insert on t1
for each row insert into t2 (f2) values (new.f1+1);
create trigger tr2 after insert on t2
for each row insert into t3 (f3) values (new.f2+1);
create trigger tr3 after insert on t3
for each row insert into t4 (f4) values (new.f3+1);
create trigger tr4 after insert on t4
for each row insert into t1 (f1) values (new.f4+1);
insert into t1 values (1);
ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
select * from t1 order by f1;
f1
0
1
select * from t2 order by f2;
f2
2
select * from t3 order by f3;
f3
3
select * from t4 order by f4;
f4
4
drop trigger tr1;
drop trigger tr2;
drop trigger tr3;
drop trigger tr4;
drop table t1;
drop table t2;
drop table t3;
drop table t4;
Testcase y.y.y.4: Recursive trigger/SP references
-------------------------------------------------
set sql_mode = 'NO_ENGINE_SUBSTITUTION';
create table t1_sp (
count integer,
var136 tinyint,
var151 decimal) engine = <engine_to_be_used>;
create procedure trig_sp()
begin
declare done int default 0;
declare var151 decimal;
declare var136 tinyint;
declare cur1 cursor for select f136, f151 from tb3;
declare continue handler for sqlstate '01000' set done = 1;
set @counter= @counter+1;
open cur1;
fetch cur1 into var136, var151;
wl_loop: WHILE NOT done DO
insert into t1_sp values (@counter, var136, var151);
fetch cur1 into var136, var151;
END WHILE wl_loop;
close cur1;
end//
create trigger trg before insert on t1_sp
for each row call trig_sp();
set @counter=0;
select @counter;
@counter
0
call trig_sp();
ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine trig_sp
select @counter;
@counter
1
select count(*) from tb3;
count(*)
11
select count(*) from t1_sp;
count(*)
0
set @@max_sp_recursion_depth= 10;
set @counter=0;
select @counter;
@counter
0
call trig_sp();
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
select @counter;
@counter
2
select count(*) from tb3;
count(*)
11
select count(*) from t1_sp;
count(*)
0
drop procedure trig_sp;
drop trigger trg;
drop table t1_sp;
set sql_mode = default;
Testcase y.y.y.5: Rollback of nested trigger references
-------------------------------------------------------
set @@sql_mode='traditional';
use test;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t1 (f1 integer) engine = <engine_to_be_used>;
create table t2 (f2 integer) engine = <engine_to_be_used>;
create table t3 (f3 integer) engine = <engine_to_be_used>;
create table t4 (f4 tinyint) engine = <engine_to_be_used>;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`f1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
insert into t1 values (1);
create trigger tr1 after insert on t1
for each row insert into t2 (f2) values (new.f1+1);
create trigger tr2 after insert on t2
for each row insert into t3 (f3) values (new.f2+1);
create trigger tr3 after insert on t3
for each row insert into t4 (f4) values (new.f3+1000);
set autocommit=0;
start transaction;
insert into t1 values (1);
ERROR 22003: Out of range value for column 'f4' at row 1
commit;
select * from t1 order by f1;
f1
1
1
select * from t2 order by f2;
f2
2
select * from t3 order by f3;
f3
3
drop trigger tr1;
drop trigger tr2;
drop trigger tr3;
drop table t1;
drop table t2;
drop table t3;
drop table t4;
DROP TABLE test.tb3;
|