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