Debian Codegraph @

mysql-test/suite/funcs_1/r/innodb_trig_08.result

raw
1 USE test;
2 drop table if exists tb3;
3 create table 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',
7 f121 char(50),
8 f122 char(50),
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,
34 f154 decimal (0),
35 f155 decimal (64),
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,
42 f162 decimal (0,0),
43 f163 decimal (63,30),
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,
50 f170 numeric,
51 f171 numeric unsigned,
52 f172 numeric zerofill,
53 f173 numeric unsigned zerofill,
54 f174 numeric (0),
55 f175 numeric (64)
56 ) charset latin1 engine = <engine_to_be_used>;
57 Warnings:
58 Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
59 Warning 1287 'ASCII' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
60 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
61 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
62 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
63 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
64 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
65 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
66 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
67 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
68 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
69 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
70 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
71 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
72 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
73 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
74 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
75 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
76 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
77 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
78 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
79 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
80 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
81 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
82 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
83 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
84 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
85 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
86 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
87 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
88 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
89 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
90 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
91 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
92 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
93 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
94 Note 1265 Data truncated for column 'f150' at row 1
95 Note 1265 Data truncated for column 'f151' at row 1
96 Note 1265 Data truncated for column 'f152' at row 1
97 Warnings:
98 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
99
100 Testcase: 3.5:
101 --------------
102 Warnings:
103 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
104 create User test_general@localhost;
105 alter user test_general@localhost identified by 'PWD';
106 revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
107 create User test_super@localhost;
108 alter user test_super@localhost identified by 'PWD';
109 grant ALL on *.* to test_super@localhost with grant OPTION;
110 Warnings:
111 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
112
113 Testcase 3.5.8.1: (implied in previous tests)
114 ---------------------------------------------
115 Warnings:
116 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
117 Warnings:
118 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
119
120 Testcase 3.5.8.2: (implied in previous tests)
121 ---------------------------------------------
122 Warnings:
123 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
124 Warnings:
125 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
126
127 Testcase 3.5.8.3/4:
128 -------------------
129 Warnings:
130 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
131 create database db_test;
132 create user test_general;
133 grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
134 grant LOCK TABLES on db_test.* to test_general;
135 Use db_test;
136 create table t1_i (
137 i120 char ascii not null DEFAULT b'101',
138 i136 smallint zerofill not null DEFAULT 999,
139 i144 int zerofill not null DEFAULT 99999,
140 i163 decimal (63,30)) engine=<engine_to_be_used>;
141 Warnings:
142 Warning 1287 'ASCII' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
143 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
144 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
145 create table t1_u (
146 u120 char ascii not null DEFAULT b'101',
147 u136 smallint zerofill not null DEFAULT 999,
148 u144 int zerofill not null DEFAULT 99999,
149 u163 decimal (63,30)) engine=<engine_to_be_used>;
150 Warnings:
151 Warning 1287 'ASCII' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
152 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
153 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
154 create table t1_d (
155 d120 char ascii not null DEFAULT b'101',
156 d136 smallint zerofill not null DEFAULT 999,
157 d144 int zerofill not null DEFAULT 99999,
158 d163 decimal (63,30)) engine=<engine_to_be_used>;
159 Warnings:
160 Warning 1287 'ASCII' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
161 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
162 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
163 Insert into t1_u values ('a',111,99999,999.99);
164 Insert into t1_u values ('b',222,99999,999.99);
165 Insert into t1_u values ('c',333,99999,999.99);
166 Insert into t1_u values ('d',222,99999,999.99);
167 Insert into t1_u values ('e',222,99999,999.99);
168 Insert into t1_u values ('f',333,99999,999.99);
169 Insert into t1_d values ('a',111,99999,999.99);
170 Insert into t1_d values ('b',222,99999,999.99);
171 Insert into t1_d values ('c',333,99999,999.99);
172 Insert into t1_d values ('d',444,99999,999.99);
173 Insert into t1_d values ('e',222,99999,999.99);
174 Insert into t1_d values ('f',222,99999,999.99);
175 Warnings:
176 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
177
178 3.5.8.4 - multiple SQL
179 ----------------------
180 Warnings:
181 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
182 use test;
183 Create trigger trg1 AFTER INSERT on tb3 for each row
184 BEGIN
185 insert into db_test.t1_i
186 values (new.f120, new.f136, new.f144, new.f163);
187 update db_test.t1_u
188 set u144=new.f144, u163=new.f163
189 where u136=new.f136;
190 delete from db_test.t1_d where d136= new.f136;
191 select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
192 where u136= new.f136;
193 END//
194 Use test;
195 set @test_var=0;
196 Insert into tb3 (f120, f122, f136, f144, f163)
197 values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
198 Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
199 f120 f122 f136 f144 f163
200 1 Test 3.5.8.4 00222 0000023456 1.050000000000000000000000000000
201 select * from db_test.t1_i;
202 i120 i136 i144 i163
203 1 00222 0000023456 1.050000000000000000000000000000
204 select * from db_test.t1_u;
205 u120 u136 u144 u163
206 a 00111 0000099999 999.990000000000000000000000000000
207 b 00222 0000023456 1.050000000000000000000000000000
208 c 00333 0000099999 999.990000000000000000000000000000
209 d 00222 0000023456 1.050000000000000000000000000000
210 e 00222 0000023456 1.050000000000000000000000000000
211 f 00333 0000099999 999.990000000000000000000000000000
212 select * from db_test.t1_d;
213 d120 d136 d144 d163
214 a 00111 0000099999 999.990000000000000000000000000000
215 c 00333 0000099999 999.990000000000000000000000000000
216 d 00444 0000099999 999.990000000000000000000000000000
217 select @test_var;
218 @test_var
219 3.150000000000000000000000000000
220 Warnings:
221 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
222
223 3.5.8.4 - single SQL - insert
224 -----------------------------
225 Warnings:
226 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
227 Create trigger trg2 BEFORE UPDATE on tb3 for each row
228 BEGIN
229 insert into db_test.t1_i
230 values (new.f120, new.f136, new.f144, new.f163);
231 END//
232 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
233 f120 f122 f136 f144 f163
234 1 Test 3.5.8.4 00222 0000023456 1.050000000000000000000000000000
235 select * from db_test.t1_i order by i120;
236 i120 i136 i144 i163
237 1 00222 0000023456 1.050000000000000000000000000000
238 update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
239 where f122='Test 3.5.8.4';
240 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
241 f120 f122 f136 f144 f163
242 I Test 3.5.8.4-Single Insert 00222 0000023456 1.050000000000000000000000000000
243 select * from db_test.t1_i order by i120;
244 i120 i136 i144 i163
245 1 00222 0000023456 1.050000000000000000000000000000
246 I 00222 0000023456 1.050000000000000000000000000000
247 Warnings:
248 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
249
250 3.5.8.4 - single SQL - update
251 -----------------------------
252 Warnings:
253 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
254 drop trigger trg2;
255 Create trigger trg3 BEFORE UPDATE on tb3 for each row
256 update db_test.t1_u
257 set u120=new.f120
258 where u136=new.f136;
259 update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
260 where f122='Test 3.5.8.4-Single Insert';
261 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
262 f120 f122 f136 f144 f163
263 U Test 3.5.8.4-Single Update 00222 0000023456 1.050000000000000000000000000000
264 select * from db_test.t1_u order by u120;
265 u120 u136 u144 u163
266 a 00111 0000099999 999.990000000000000000000000000000
267 c 00333 0000099999 999.990000000000000000000000000000
268 f 00333 0000099999 999.990000000000000000000000000000
269 U 00222 0000023456 1.050000000000000000000000000000
270 U 00222 0000023456 1.050000000000000000000000000000
271 U 00222 0000023456 1.050000000000000000000000000000
272 Warnings:
273 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
274
275 3.5.8.3/4 - single SQL - delete
276 -------------------------------
277 Warnings:
278 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
279 drop trigger trg3;
280 Create trigger trg4 AFTER UPDATE on tb3 for each row
281 delete from db_test.t1_d where d136= new.f136;
282 update tb3 set f120='D', f136=444,
283 f122='Test 3.5.8.4-Single Delete'
284 where f122='Test 3.5.8.4-Single Update';
285 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
286 f120 f122 f136 f144 f163
287 D Test 3.5.8.4-Single Delete 00444 0000023456 1.050000000000000000000000000000
288 select * from db_test.t1_d order by d120;
289 d120 d136 d144 d163
290 a 00111 0000099999 999.990000000000000000000000000000
291 c 00333 0000099999 999.990000000000000000000000000000
292 Warnings:
293 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
294
295 3.5.8.3/4 - single SQL - select
296 -------------------------------
297 Warnings:
298 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
299 drop trigger trg4;
300 Create trigger trg5 AFTER UPDATE on tb3 for each row
301 select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
302 where u136= new.f136;
303 set @test_var=0;
304 update tb3 set f120='S', f136=111,
305 f122='Test 3.5.8.4-Single Select'
306 where f122='Test 3.5.8.4-Single Delete';
307 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
308 f120 f122 f136 f144 f163
309 S Test 3.5.8.4-Single Select 00111 0000023456 1.050000000000000000000000000000
310 select @test_var;
311 @test_var
312 999.990000000000000000000000000000
313 drop trigger trg1;
314 drop trigger trg5;
315 drop database if exists db_test;
316 delete from tb3 where f122 like 'Test 3.5.8.4%';
317 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
318 Warnings:
319 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
320
321 Testcase 3.5.8.5 (IF):
322 ----------------------
323 Warnings:
324 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
325 create trigger trg2 before insert on tb3 for each row
326 BEGIN
327 IF new.f120='1' then
328 set @test_var='one', new.f120='2';
329 ELSEIF new.f120='2' then
330 set @test_var='two', new.f120='3';
331 ELSEIF new.f120='3' then
332 set @test_var='three', new.f120='4';
333 END IF;
334 IF (new.f120='4') and (new.f136=10) then
335 set @test_var2='2nd if', new.f120='d';
336 ELSE
337 set @test_var2='2nd else', new.f120='D';
338 END IF;
339 END//
340 set @test_var='Empty', @test_var2=0;
341 Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
342 select f120, f122, f136, @test_var, @test_var2
343 from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
344 f120 f122 f136 @test_var @test_var2
345 D Test 3.5.8.5-if 00101 one 2nd else
346 Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
347 select f120, f122, f136, @test_var, @test_var2
348 from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
349 f120 f122 f136 @test_var @test_var2
350 D Test 3.5.8.5-if 00101 two 2nd else
351 D Test 3.5.8.5-if 00102 two 2nd else
352 Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
353 select f120, f122, f136, @test_var, @test_var2
354 from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
355 f120 f122 f136 @test_var @test_var2
356 d Test 3.5.8.5-if 00010 three 2nd if
357 D Test 3.5.8.5-if 00101 three 2nd if
358 D Test 3.5.8.5-if 00102 three 2nd if
359 Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
360 select f120, f122, f136, @test_var, @test_var2
361 from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
362 f120 f122 f136 @test_var @test_var2
363 d Test 3.5.8.5-if 00010 three 2nd else
364 D Test 3.5.8.5-if 00101 three 2nd else
365 D Test 3.5.8.5-if 00102 three 2nd else
366 D Test 3.5.8.5-if 00103 three 2nd else
367 create trigger trg3 before update on tb3 for each row
368 BEGIN
369 ELSEIF new.f120='2' then
370 END IF;
371 END//
372 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
373 END IF;
374 END' at line 3
375 drop trigger trg3//
376 create trigger trg4 before update on tb3 for each row
377 BEGIN
378 IF (new.f120='4') and (new.f136=10) then
379 set @test_var2='2nd if', new.f120='d';
380 ELSE
381 set @test_var2='2nd else', new.f120='D';
382 END//
383 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
384 drop trigger trg4;
385 drop trigger trg2;
386 delete from tb3 where f121='Test 3.5.8.5-if';
387 Warnings:
388 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
389
390 Testcase 3.5.8.5-case:
391 ----------------------
392 Warnings:
393 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
394 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
395 create trigger trg3 before insert on tb3 for each row
396 BEGIN
397 SET new.f120=char(ascii(new.f120)-32);
398 CASE
399 when new.f136<100 then set new.f136=new.f136+120;
400 when new.f136<10 then set new.f144=777;
401 when new.f136>100 then set new.f120=new.f136-1;
402 END case;
403 CASE
404 when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
405 ELSE set @test_var=concat(new.f120, '*');
406 END case;
407 CASE new.f144
408 when 1 then set @test_var=concat(@test_var, 'one');
409 when 2 then set @test_var=concat(@test_var, 'two');
410 when 3 then set @test_var=concat(@test_var, 'three');
411 when 4 then set @test_var=concat(@test_var, 'four');
412 when 5 then set @test_var=concat(@test_var, 'five');
413 when 6 then set @test_var=concat(@test_var, 'six');
414 when 7 then set @test_var=concat(@test_var, 'seven');
415 when 8 then set @test_var=concat(@test_var, 'eight');
416 when 9 then set @test_var=concat(@test_var, 'nine');
417 when 10 then set @test_var=concat(@test_var, 'ten');
418 when 11 then set @test_var=concat(@test_var, 'eleven');
419 when 12 then set @test_var=concat(@test_var, 'twelve');
420 when 13 then set @test_var=concat(@test_var, 'thirteen');
421 when 14 then set @test_var=concat(@test_var, 'fourteen');
422 when 15 then set @test_var=concat(@test_var, 'fifteen');
423 ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
424 END case;
425 END//
426 set @test_var='Empty';
427 Insert into tb3 (f120, f122, f136, f144)
428 values ('a', 'Test 3.5.8.5-case', 5, 7);
429 select f120, f122, f136, f144, @test_var
430 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
431 f120 f122 f136 f144 @test_var
432 A Test 3.5.8.5-case 00125 0000000007 A*seven
433 Insert into tb3 (f120, f122, f136, f144)
434 values ('b', 'Test 3.5.8.5-case', 71,16);
435 select f120, f122, f136, f144, @test_var
436 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
437 f120 f122 f136 f144 @test_var
438 A Test 3.5.8.5-case 00125 0000000007 B*0000000016
439 B Test 3.5.8.5-case 00191 0000000016 B*0000000016
440 Insert into tb3 (f120, f122, f136, f144)
441 values ('c', 'Test 3.5.8.5-case', 80,1);
442 select f120, f122, f136, f144, @test_var
443 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
444 f120 f122 f136 f144 @test_var
445 A Test 3.5.8.5-case 00125 0000000007 C=one
446 B Test 3.5.8.5-case 00191 0000000016 C=one
447 C Test 3.5.8.5-case 00200 0000000001 C=one
448 Insert into tb3 (f120, f122, f136)
449 values ('d', 'Test 3.5.8.5-case', 152);
450 select f120, f122, f136, f144, @test_var
451 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
452 f120 f122 f136 f144 @test_var
453 1 Test 3.5.8.5-case 00152 0000099999 1*0000099999
454 A Test 3.5.8.5-case 00125 0000000007 1*0000099999
455 B Test 3.5.8.5-case 00191 0000000016 1*0000099999
456 C Test 3.5.8.5-case 00200 0000000001 1*0000099999
457 Insert into tb3 (f120, f122, f136, f144)
458 values ('e', 'Test 3.5.8.5-case', 200, 8);
459 select f120, f122, f136, f144, @test_var
460 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
461 f120 f122 f136 f144 @test_var
462 1 Test 3.5.8.5-case 00152 0000099999 1=eight
463 1 Test 3.5.8.5-case 00200 0000000008 1=eight
464 A Test 3.5.8.5-case 00125 0000000007 1=eight
465 B Test 3.5.8.5-case 00191 0000000016 1=eight
466 C Test 3.5.8.5-case 00200 0000000001 1=eight
467 Insert into tb3 (f120, f122, f136, f144)
468 values ('f', 'Test 3.5.8.5-case', 100, 8);
469 select f120, f122, f136, f144, @test_var
470 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
471 f120 f122 f136 f144 @test_var
472 1 Test 3.5.8.5-case 00152 0000099999 1=eight
473 1 Test 3.5.8.5-case 00200 0000000008 1=eight
474 A Test 3.5.8.5-case 00125 0000000007 1=eight
475 B Test 3.5.8.5-case 00191 0000000016 1=eight
476 C Test 3.5.8.5-case 00200 0000000001 1=eight
477 create trigger trg3a before update on tb3 for each row
478 BEGIN
479 CASE
480 when new.f136<100 then set new.f120='p';
481 END//
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 '' at line 5
483 drop trigger trg3a;
484 drop trigger trg3;
485 delete from tb3 where f121='Test 3.5.8.5-case';
486 SET sql_mode = default;
487 Warnings:
488 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
489
490 Testcase 3.5.8.5-loop/leave:
491 ----------------------------
492 Warnings:
493 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
494 Create trigger trg4 after insert on tb3 for each row
495 BEGIN
496 set @counter=0, @flag='Initial';
497 Label1: loop
498 if new.f136<new.f144 then
499 set @counter='Nothing to loop';
500 leave Label1;
501 else
502 set @counter=@counter+1;
503 if new.f136=new.f144+@counter then
504 set @counter=concat(@counter, ' loops');
505 leave Label1;
506 end if;
507 end if;
508 iterate label1;
509 set @flag='Final';
510 END loop Label1;
511 END//
512 Insert into tb3 (f122, f136, f144)
513 values ('Test 3.5.8.5-loop', 2, 8);
514 select @counter, @flag;
515 @counter @flag
516 Nothing to loop Initial
517 Insert into tb3 (f122, f136, f144)
518 values ('Test 3.5.8.5-loop', 11, 8);
519 select @counter, @flag;
520 @counter @flag
521 3 loops Initial
522 Create trigger trg4_2 after update on tb3 for each row
523 BEGIN
524 Label1: loop
525 set @counter=@counter+1;
526 END;
527 END//
528 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 ';
529 END' at line 5
530 drop trigger trg4_2;
531 drop trigger trg4;
532 delete from tb3 where f122='Test 3.5.8.5-loop';
533 Warnings:
534 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
535
536 Testcase 3.5.8.5-repeat:
537 ------------------------
538 Warnings:
539 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
540 Create trigger trg6 after insert on tb3 for each row
541 BEGIN
542 rp_label: REPEAT
543 SET @counter1 = @counter1 + 1;
544 IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label;
545 END IF;
546 SET @counter2 = @counter2 + 1;
547 UNTIL @counter1> new.f136 END REPEAT rp_label;
548 END//
549 set @counter1= 0, @counter2= 0;
550 Insert into tb3 (f122, f136)
551 values ('Test 3.5.8.5-repeat', 13);
552 select @counter1, @counter2;
553 @counter1 @counter2
554 15 8
555 Create trigger trg6_2 after update on tb3 for each row
556 BEGIN
557 REPEAT
558 SET @counter2 = @counter2 + 1;
559 END//
560 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
561 drop trigger trg6;
562 delete from tb3 where f122='Test 3.5.8.5-repeat';
563 Warnings:
564 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
565
566 Testcase 3.5.8.5-while:
567 -----------------------
568 Warnings:
569 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
570 Create trigger trg7 after insert on tb3 for each row
571 wl_label: WHILE @counter1 < new.f136 DO
572 SET @counter1 = @counter1 + 1;
573 IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label;
574 END IF;
575 SET @counter2 = @counter2 + 1;
576 END WHILE wl_label//
577 set @counter1= 0, @counter2= 0;
578 Insert into tb3 (f122, f136)
579 values ('Test 3.5.8.5-while', 7);
580 select @counter1, @counter2;
581 @counter1 @counter2
582 7 4
583 Create trigger trg7_2 after update on tb3 for each row
584 BEGIN
585 WHILE @counter1 < new.f136
586 SET @counter1 = @counter1 + 1;
587 END//
588 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;
589 END' at line 4
590 delete from tb3 where f122='Test 3.5.8.5-while';
591 drop trigger trg7;
592 Warnings:
593 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
594
595 Testcase 3.5.8.6: (requirement void)
596 ------------------------------------
597 Warnings:
598 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
599 CREATE PROCEDURE sp_01 () BEGIN set @v1=1; END//
600 CREATE TRIGGER trg8_1 BEFORE UPDATE ON tb3 FOR EACH ROW
601 BEGIN
602 CALL sp_01 ();
603 END//
604 Insert into tb3 (f120, f122, f136) values ('6', 'Test 3.5.8.6-insert', 101);
605 update tb3 set f120='S', f136=111,
606 f122='Test 3.5.8.6-tr8_1'
607 where f122='Test 3.5.8.6-insert';
608 select f120, f122
609 from tb3 where f122 like 'Test 3.5.8.6%' order by f120;
610 f120 f122
611 S Test 3.5.8.6-tr8_1
612 DROP TRIGGER trg8_1;
613 DROP PROCEDURE sp_01;
614 Warnings:
615 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
616
617 Testcase 3.5.8.7
618 ----------------
619 Warnings:
620 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
621 Create trigger trg9_1 before update on tb3 for each row
622 BEGIN
623 Start transaction;
624 Set new.f120='U';
625 Commit;
626 END//
627 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
628 Create trigger trg9_2 before delete on tb3 for each row
629 BEGIN
630 Start transaction;
631 Set @var2=old.f120;
632 Rollback;
633 END//
634 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
635 drop user test_general@localhost;
636 drop user test_general;
637 drop user test_super@localhost;
638 DROP TABLE test.tb3;