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