Debian Codegraph @

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

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