Debian Codegraph @

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

raw
1 SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
2 set GLOBAL sql_mode="";
3 USE test;
4 drop table if exists tb3;
5 create table tb3 (
6 f118 char not null DEFAULT 'a',
7 f119 char binary not null DEFAULT b'101',
8 f120 char ascii not null DEFAULT b'101',
9 f121 char(50),
10 f122 char(50),
11 f129 binary not null DEFAULT b'101',
12 f130 tinyint not null DEFAULT 99,
13 f131 tinyint unsigned not null DEFAULT 99,
14 f132 tinyint zerofill not null DEFAULT 99,
15 f133 tinyint unsigned zerofill not null DEFAULT 99,
16 f134 smallint not null DEFAULT 999,
17 f135 smallint unsigned not null DEFAULT 999,
18 f136 smallint zerofill not null DEFAULT 999,
19 f137 smallint unsigned zerofill not null DEFAULT 999,
20 f138 mediumint not null DEFAULT 9999,
21 f139 mediumint unsigned not null DEFAULT 9999,
22 f140 mediumint zerofill not null DEFAULT 9999,
23 f141 mediumint unsigned zerofill not null DEFAULT 9999,
24 f142 int not null DEFAULT 99999,
25 f143 int unsigned not null DEFAULT 99999,
26 f144 int zerofill not null DEFAULT 99999,
27 f145 int unsigned zerofill not null DEFAULT 99999,
28 f146 bigint not null DEFAULT 999999,
29 f147 bigint unsigned not null DEFAULT 999999,
30 f148 bigint zerofill not null DEFAULT 999999,
31 f149 bigint unsigned zerofill not null DEFAULT 999999,
32 f150 decimal not null DEFAULT 999.999,
33 f151 decimal unsigned not null DEFAULT 999.17,
34 f152 decimal zerofill not null DEFAULT 999.999,
35 f153 decimal unsigned zerofill,
36 f154 decimal (0),
37 f155 decimal (64),
38 f156 decimal (0) unsigned,
39 f157 decimal (64) unsigned,
40 f158 decimal (0) zerofill,
41 f159 decimal (64) zerofill,
42 f160 decimal (0) unsigned zerofill,
43 f161 decimal (64) unsigned zerofill,
44 f162 decimal (0,0),
45 f163 decimal (63,30),
46 f164 decimal (0,0) unsigned,
47 f165 decimal (63,30) unsigned,
48 f166 decimal (0,0) zerofill,
49 f167 decimal (63,30) zerofill,
50 f168 decimal (0,0) unsigned zerofill,
51 f169 decimal (63,30) unsigned zerofill,
52 f170 numeric,
53 f171 numeric unsigned,
54 f172 numeric zerofill,
55 f173 numeric unsigned zerofill,
56 f174 numeric (0),
57 f175 numeric (64)
58 ) engine = <engine_to_be_used>;
59 Warnings:
60 Note 1265 Data truncated for column 'f150' at row 0
61 Note 1265 Data truncated for column 'f151' at row 0
62 Note 1265 Data truncated for column 'f152' at row 0
63
64 Testcase: 3.5:
65 --------------
66 create User test_general@localhost;
67 set password for test_general@localhost = password('PWD');
68 revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
69 grant SELECT,UPDATE,DELETE,INSERT on test.* to test_general@localhost;
70 create User test_super@localhost;
71 set password for test_super@localhost = password('PWD');
72 grant ALL on *.* to test_super@localhost with grant OPTION;
73 connect con2_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
74 connect con2_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
75 connection default;
76
77 Testcase 3.5.8.1: (implied in previous tests)
78 ---------------------------------------------
79
80 Testcase 3.5.8.2: (implied in previous tests)
81 ---------------------------------------------
82
83 Testcase 3.5.8.3/4:
84 -------------------
85 connection con2_super;
86 create database db_test;
87 grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
88 grant LOCK TABLES on db_test.* to test_general;
89 Use db_test;
90 create table t1_i (
91 i120 char ascii not null DEFAULT b'101',
92 i136 smallint zerofill not null DEFAULT 999,
93 i144 int zerofill not null DEFAULT 99999,
94 i163 decimal (63,30)) engine=<engine_to_be_used>;
95 create table t1_u (
96 u120 char ascii not null DEFAULT b'101',
97 u136 smallint zerofill not null DEFAULT 999,
98 u144 int zerofill not null DEFAULT 99999,
99 u163 decimal (63,30)) engine=<engine_to_be_used>;
100 create table t1_d (
101 d120 char ascii not null DEFAULT b'101',
102 d136 smallint zerofill not null DEFAULT 999,
103 d144 int zerofill not null DEFAULT 99999,
104 d163 decimal (63,30)) engine=<engine_to_be_used>;
105 Insert into t1_u values ('a',111,99999,999.99);
106 Insert into t1_u values ('b',222,99999,999.99);
107 Insert into t1_u values ('c',333,99999,999.99);
108 Insert into t1_u values ('d',222,99999,999.99);
109 Insert into t1_u values ('e',222,99999,999.99);
110 Insert into t1_u values ('f',333,99999,999.99);
111 Insert into t1_d values ('a',111,99999,999.99);
112 Insert into t1_d values ('b',222,99999,999.99);
113 Insert into t1_d values ('c',333,99999,999.99);
114 Insert into t1_d values ('d',444,99999,999.99);
115 Insert into t1_d values ('e',222,99999,999.99);
116 Insert into t1_d values ('f',222,99999,999.99);
117
118 3.5.8.4 - multiple SQL
119 ----------------------
120 use test;
121 Create trigger trg1 AFTER INSERT on tb3 for each row
122 BEGIN
123 insert into db_test.t1_i
124 values (new.f120, new.f136, new.f144, new.f163);
125 update db_test.t1_u
126 set u144=new.f144, u163=new.f163
127 where u136=new.f136;
128 delete from db_test.t1_d where d136= new.f136;
129 select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
130 where u136= new.f136;
131 END//
132 connection con2_general;
133 Use test;
134 set @test_var=0;
135 Insert into tb3 (f120, f122, f136, f144, f163)
136 values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
137 Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
138 f120 f122 f136 f144 f163
139 1 Test 3.5.8.4 00222 0000023456 1.050000000000000000000000000000
140 select * from db_test.t1_i;
141 i120 i136 i144 i163
142 1 00222 0000023456 1.050000000000000000000000000000
143 select * from db_test.t1_u;
144 u120 u136 u144 u163
145 a 00111 0000099999 999.990000000000000000000000000000
146 b 00222 0000023456 1.050000000000000000000000000000
147 c 00333 0000099999 999.990000000000000000000000000000
148 d 00222 0000023456 1.050000000000000000000000000000
149 e 00222 0000023456 1.050000000000000000000000000000
150 f 00333 0000099999 999.990000000000000000000000000000
151 select * from db_test.t1_d;
152 d120 d136 d144 d163
153 a 00111 0000099999 999.990000000000000000000000000000
154 c 00333 0000099999 999.990000000000000000000000000000
155 d 00444 0000099999 999.990000000000000000000000000000
156 select @test_var;
157 @test_var
158 3.150000000000000000000000000000
159
160 3.5.8.4 - single SQL - insert
161 -----------------------------
162 connection con2_super;
163 Create trigger trg2 BEFORE UPDATE on tb3 for each row
164 BEGIN
165 insert into db_test.t1_i
166 values (new.f120, new.f136, new.f144, new.f163);
167 END//
168 connection con2_general;
169 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
170 f120 f122 f136 f144 f163
171 1 Test 3.5.8.4 00222 0000023456 1.050000000000000000000000000000
172 select * from db_test.t1_i order by i120;
173 i120 i136 i144 i163
174 1 00222 0000023456 1.050000000000000000000000000000
175 update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
176 where f122='Test 3.5.8.4';
177 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
178 f120 f122 f136 f144 f163
179 I Test 3.5.8.4-Single Insert 00222 0000023456 1.050000000000000000000000000000
180 select * from db_test.t1_i order by i120;
181 i120 i136 i144 i163
182 1 00222 0000023456 1.050000000000000000000000000000
183 I 00222 0000023456 1.050000000000000000000000000000
184
185 3.5.8.4 - single SQL - update
186 -----------------------------
187 connection con2_super;
188 drop trigger trg2;
189 Create trigger trg3 BEFORE UPDATE on tb3 for each row
190 update db_test.t1_u
191 set u120=new.f120
192 where u136=new.f136;
193 connection con2_general;
194 update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
195 where f122='Test 3.5.8.4-Single Insert';
196 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
197 f120 f122 f136 f144 f163
198 U Test 3.5.8.4-Single Update 00222 0000023456 1.050000000000000000000000000000
199 select * from db_test.t1_u order by u120;
200 u120 u136 u144 u163
201 a 00111 0000099999 999.990000000000000000000000000000
202 c 00333 0000099999 999.990000000000000000000000000000
203 f 00333 0000099999 999.990000000000000000000000000000
204 U 00222 0000023456 1.050000000000000000000000000000
205 U 00222 0000023456 1.050000000000000000000000000000
206 U 00222 0000023456 1.050000000000000000000000000000
207
208 3.5.8.3/4 - single SQL - delete
209 -------------------------------
210 connection con2_super;
211 drop trigger trg3;
212 Create trigger trg4 AFTER UPDATE on tb3 for each row
213 delete from db_test.t1_d where d136= new.f136;
214 connection con2_general;
215 update tb3 set f120='D', f136=444,
216 f122='Test 3.5.8.4-Single Delete'
217 where f122='Test 3.5.8.4-Single Update';
218 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
219 f120 f122 f136 f144 f163
220 D Test 3.5.8.4-Single Delete 00444 0000023456 1.050000000000000000000000000000
221 select * from db_test.t1_d order by d120;
222 d120 d136 d144 d163
223 a 00111 0000099999 999.990000000000000000000000000000
224 c 00333 0000099999 999.990000000000000000000000000000
225
226 3.5.8.3/4 - single SQL - select
227 -------------------------------
228 connection con2_super;
229 drop trigger trg4;
230 Create trigger trg5 AFTER UPDATE on tb3 for each row
231 select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
232 where u136= new.f136;
233 connection con2_general;
234 set @test_var=0;
235 update tb3 set f120='S', f136=111,
236 f122='Test 3.5.8.4-Single Select'
237 where f122='Test 3.5.8.4-Single Delete';
238 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
239 f120 f122 f136 f144 f163
240 S Test 3.5.8.4-Single Select 00111 0000023456 1.050000000000000000000000000000
241 select @test_var;
242 @test_var
243 999.990000000000000000000000000000
244 connection default;
245 drop trigger trg1;
246 drop trigger trg5;
247 drop database if exists db_test;
248 delete from tb3 where f122 like 'Test 3.5.8.4%';
249 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
250
251 Testcase 3.5.8.5 (IF):
252 ----------------------
253 create trigger trg2 before insert on tb3 for each row
254 BEGIN
255 IF new.f120='1' then
256 set @test_var='one', new.f120='2';
257 ELSEIF new.f120='2' then
258 set @test_var='two', new.f120='3';
259 ELSEIF new.f120='3' then
260 set @test_var='three', new.f120='4';
261 END IF;
262 IF (new.f120='4') and (new.f136=10) then
263 set @test_var2='2nd if', new.f120='d';
264 ELSE
265 set @test_var2='2nd else', new.f120='D';
266 END IF;
267 END//
268 set @test_var='Empty', @test_var2=0;
269 Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
270 select f120, f122, f136, @test_var, @test_var2
271 from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
272 f120 f122 f136 @test_var @test_var2
273 D Test 3.5.8.5-if 00101 one 2nd else
274 Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
275 select f120, f122, f136, @test_var, @test_var2
276 from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
277 f120 f122 f136 @test_var @test_var2
278 D Test 3.5.8.5-if 00101 two 2nd else
279 D Test 3.5.8.5-if 00102 two 2nd else
280 Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
281 select f120, f122, f136, @test_var, @test_var2
282 from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
283 f120 f122 f136 @test_var @test_var2
284 d Test 3.5.8.5-if 00010 three 2nd if
285 D Test 3.5.8.5-if 00101 three 2nd if
286 D Test 3.5.8.5-if 00102 three 2nd if
287 Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
288 select f120, f122, f136, @test_var, @test_var2
289 from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
290 f120 f122 f136 @test_var @test_var2
291 d Test 3.5.8.5-if 00010 three 2nd else
292 D Test 3.5.8.5-if 00101 three 2nd else
293 D Test 3.5.8.5-if 00102 three 2nd else
294 D Test 3.5.8.5-if 00103 three 2nd else
295 create trigger trg3 before update on tb3 for each row
296 BEGIN
297 ELSEIF new.f120='2' then
298 END IF;
299 END//
300 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ELSEIF new.f120='2' then
301 END IF;
302 END' at line 3
303 drop trigger trg3//
304 create trigger trg4 before update on tb3 for each row
305 BEGIN
306 IF (new.f120='4') and (new.f136=10) then
307 set @test_var2='2nd if', new.f120='d';
308 ELSE
309 set @test_var2='2nd else', new.f120='D';
310 END//
311 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 7
312 drop trigger trg4;
313 drop trigger trg2;
314 delete from tb3 where f121='Test 3.5.8.5-if';
315
316 Testcase 3.5.8.5-case:
317 ----------------------
318 create trigger trg3 before insert on tb3 for each row
319 BEGIN
320 SET new.f120=char(ascii(new.f120)-32);
321 CASE
322 when new.f136<100 then set new.f136=new.f136+120;
323 when new.f136<10 then set new.f144=777;
324 when new.f136>100 then set new.f120=new.f136-1;
325 END case;
326 CASE
327 when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
328 ELSE set @test_var=concat(new.f120, '*');
329 END case;
330 CASE new.f144
331 when 1 then set @test_var=concat(@test_var, 'one');
332 when 2 then set @test_var=concat(@test_var, 'two');
333 when 3 then set @test_var=concat(@test_var, 'three');
334 when 4 then set @test_var=concat(@test_var, 'four');
335 when 5 then set @test_var=concat(@test_var, 'five');
336 when 6 then set @test_var=concat(@test_var, 'six');
337 when 7 then set @test_var=concat(@test_var, 'seven');
338 when 8 then set @test_var=concat(@test_var, 'eight');
339 when 9 then set @test_var=concat(@test_var, 'nine');
340 when 10 then set @test_var=concat(@test_var, 'ten');
341 when 11 then set @test_var=concat(@test_var, 'eleven');
342 when 12 then set @test_var=concat(@test_var, 'twelve');
343 when 13 then set @test_var=concat(@test_var, 'thirteen');
344 when 14 then set @test_var=concat(@test_var, 'fourteen');
345 when 15 then set @test_var=concat(@test_var, 'fifteen');
346 ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
347 END case;
348 END//
349 set @test_var='Empty';
350 Insert into tb3 (f120, f122, f136, f144)
351 values ('a', 'Test 3.5.8.5-case', 5, 7);
352 select f120, f122, f136, f144, @test_var
353 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
354 f120 f122 f136 f144 @test_var
355 A Test 3.5.8.5-case 00125 0000000007 A*seven
356 Insert into tb3 (f120, f122, f136, f144)
357 values ('b', 'Test 3.5.8.5-case', 71,16);
358 select f120, f122, f136, f144, @test_var
359 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
360 f120 f122 f136 f144 @test_var
361 A Test 3.5.8.5-case 00125 0000000007 B*0000000016
362 B Test 3.5.8.5-case 00191 0000000016 B*0000000016
363 Insert into tb3 (f120, f122, f136, f144)
364 values ('c', 'Test 3.5.8.5-case', 80,1);
365 select f120, f122, f136, f144, @test_var
366 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
367 f120 f122 f136 f144 @test_var
368 A Test 3.5.8.5-case 00125 0000000007 C=one
369 B Test 3.5.8.5-case 00191 0000000016 C=one
370 C Test 3.5.8.5-case 00200 0000000001 C=one
371 Insert into tb3 (f120, f122, f136)
372 values ('d', 'Test 3.5.8.5-case', 152);
373 select f120, f122, f136, f144, @test_var
374 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
375 f120 f122 f136 f144 @test_var
376 1 Test 3.5.8.5-case 00152 0000099999 1*0000099999
377 A Test 3.5.8.5-case 00125 0000000007 1*0000099999
378 B Test 3.5.8.5-case 00191 0000000016 1*0000099999
379 C Test 3.5.8.5-case 00200 0000000001 1*0000099999
380 Insert into tb3 (f120, f122, f136, f144)
381 values ('e', 'Test 3.5.8.5-case', 200, 8);
382 select f120, f122, f136, f144, @test_var
383 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
384 f120 f122 f136 f144 @test_var
385 1 Test 3.5.8.5-case 00152 0000099999 1=eight
386 1 Test 3.5.8.5-case 00200 0000000008 1=eight
387 A Test 3.5.8.5-case 00125 0000000007 1=eight
388 B Test 3.5.8.5-case 00191 0000000016 1=eight
389 C Test 3.5.8.5-case 00200 0000000001 1=eight
390 Insert into tb3 (f120, f122, f136, f144)
391 values ('f', 'Test 3.5.8.5-case', 100, 8);
392 select f120, f122, f136, f144, @test_var
393 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
394 f120 f122 f136 f144 @test_var
395 1 Test 3.5.8.5-case 00152 0000099999 1=eight
396 1 Test 3.5.8.5-case 00200 0000000008 1=eight
397 A Test 3.5.8.5-case 00125 0000000007 1=eight
398 B Test 3.5.8.5-case 00191 0000000016 1=eight
399 C Test 3.5.8.5-case 00200 0000000001 1=eight
400 create trigger trg3a before update on tb3 for each row
401 BEGIN
402 CASE
403 when new.f136<100 then set new.f120='p';
404 END//
405 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 5
406 drop trigger trg3a;
407 drop trigger trg3;
408 delete from tb3 where f121='Test 3.5.8.5-case';
409
410 Testcase 3.5.8.5-loop/leave:
411 ----------------------------
412 Create trigger trg4 after insert on tb3 for each row
413 BEGIN
414 set @counter=0, @flag='Initial';
415 Label1: loop
416 if new.f136<new.f144 then
417 set @counter='Nothing to loop';
418 leave Label1;
419 else
420 set @counter=@counter+1;
421 if new.f136=new.f144+@counter then
422 set @counter=concat(@counter, ' loops');
423 leave Label1;
424 end if;
425 end if;
426 iterate label1;
427 set @flag='Final';
428 END loop Label1;
429 END//
430 Insert into tb3 (f122, f136, f144)
431 values ('Test 3.5.8.5-loop', 2, 8);
432 select @counter, @flag;
433 @counter @flag
434 Nothing to loop Initial
435 Insert into tb3 (f122, f136, f144)
436 values ('Test 3.5.8.5-loop', 11, 8);
437 select @counter, @flag;
438 @counter @flag
439 3 loops Initial
440 Create trigger trg4_2 after update on tb3 for each row
441 BEGIN
442 Label1: loop
443 set @counter=@counter+1;
444 END;
445 END//
446 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ';
447 END' at line 5
448 drop trigger trg4_2;
449 drop trigger trg4;
450 delete from tb3 where f122='Test 3.5.8.5-loop';
451
452 Testcase 3.5.8.5-repeat:
453 ------------------------
454 Create trigger trg6 after insert on tb3 for each row
455 BEGIN
456 rp_label: REPEAT
457 SET @counter1 = @counter1 + 1;
458 IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label;
459 END IF;
460 SET @counter2 = @counter2 + 1;
461 UNTIL @counter1> new.f136 END REPEAT rp_label;
462 END//
463 set @counter1= 0, @counter2= 0;
464 Insert into tb3 (f122, f136)
465 values ('Test 3.5.8.5-repeat', 13);
466 select @counter1, @counter2;
467 @counter1 @counter2
468 15 8
469 Create trigger trg6_2 after update on tb3 for each row
470 BEGIN
471 REPEAT
472 SET @counter2 = @counter2 + 1;
473 END//
474 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END' at line 5
475 drop trigger trg6;
476 delete from tb3 where f122='Test 3.5.8.5-repeat';
477
478 Testcase 3.5.8.5-while:
479 -----------------------
480 Create trigger trg7 after insert on tb3 for each row
481 wl_label: WHILE @counter1 < new.f136 DO
482 SET @counter1 = @counter1 + 1;
483 IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label;
484 END IF;
485 SET @counter2 = @counter2 + 1;
486 END WHILE wl_label//
487 set @counter1= 0, @counter2= 0;
488 Insert into tb3 (f122, f136)
489 values ('Test 3.5.8.5-while', 7);
490 select @counter1, @counter2;
491 @counter1 @counter2
492 7 4
493 Create trigger trg7_2 after update on tb3 for each row
494 BEGIN
495 WHILE @counter1 < new.f136
496 SET @counter1 = @counter1 + 1;
497 END//
498 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SET @counter1 = @counter1 + 1;
499 END' at line 4
500 delete from tb3 where f122='Test 3.5.8.5-while';
501 drop trigger trg7;
502
503 Testcase 3.5.8.6: (requirement void)
504 ------------------------------------
505 CREATE PROCEDURE sp_01 () BEGIN set @v1=1; END//
506 CREATE TRIGGER trg8_1 BEFORE UPDATE ON tb3 FOR EACH ROW
507 BEGIN
508 CALL sp_01 ();
509 END//
510 Insert into tb3 (f120, f122, f136) values ('6', 'Test 3.5.8.6-insert', 101);
511 update tb3 set f120='S', f136=111,
512 f122='Test 3.5.8.6-tr8_1'
513 where f122='Test 3.5.8.6-insert';
514 select f120, f122
515 from tb3 where f122 like 'Test 3.5.8.6%' order by f120;
516 f120 f122
517 S Test 3.5.8.6-tr8_1
518 DROP TRIGGER trg8_1;
519 DROP PROCEDURE sp_01;
520
521 Testcase 3.5.8.7
522 ----------------
523 Create trigger trg9_1 before update on tb3 for each row
524 BEGIN
525 Start transaction;
526 Set new.f120='U';
527 Commit;
528 END//
529 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
530 Create trigger trg9_2 before delete on tb3 for each row
531 BEGIN
532 Start transaction;
533 Set @var2=old.f120;
534 Rollback;
535 END//
536 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
537 connection default;
538 drop user test_general@localhost;
539 drop user test_general;
540 drop user test_super@localhost;
541 DROP TABLE test.tb3;
542 set GLOBAL sql_mode=default;