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; 65alter user test_general@localhost identified by 'PWD'; 66revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost; 67create User test_super@localhost; 68alter user test_super@localhost identified by '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; 80create user test_general; 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---------------------- 302SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 303Warnings: 304Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 305create trigger trg3 before insert on tb3 for each row 306BEGIN 307SET new.f120=char(ascii(new.f120)-32); 308CASE 309when new.f136<100 then set new.f136=new.f136+120; 310when new.f136<10 then set new.f144=777; 311when new.f136>100 then set new.f120=new.f136-1; 312END case; 313CASE 314when new.f136=200 then set @test_var=CONCAT(new.f120, '='); 315ELSE set @test_var=concat(new.f120, '*'); 316END case; 317CASE new.f144 318when 1 then set @test_var=concat(@test_var, 'one'); 319when 2 then set @test_var=concat(@test_var, 'two'); 320when 3 then set @test_var=concat(@test_var, 'three'); 321when 4 then set @test_var=concat(@test_var, 'four'); 322when 5 then set @test_var=concat(@test_var, 'five'); 323when 6 then set @test_var=concat(@test_var, 'six'); 324when 7 then set @test_var=concat(@test_var, 'seven'); 325when 8 then set @test_var=concat(@test_var, 'eight'); 326when 9 then set @test_var=concat(@test_var, 'nine'); 327when 10 then set @test_var=concat(@test_var, 'ten'); 328when 11 then set @test_var=concat(@test_var, 'eleven'); 329when 12 then set @test_var=concat(@test_var, 'twelve'); 330when 13 then set @test_var=concat(@test_var, 'thirteen'); 331when 14 then set @test_var=concat(@test_var, 'fourteen'); 332when 15 then set @test_var=concat(@test_var, 'fifteen'); 333ELSE set @test_var=CONCAT(new.f120, '*', new.f144); 334END case; 335END// 336set @test_var='Empty'; 337Insert into tb3 (f120, f122, f136, f144) 338values ('a', 'Test 3.5.8.5-case', 5, 7); 339select f120, f122, f136, f144, @test_var 340from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136; 341f120 f122 f136 f144 @test_var 342A Test 3.5.8.5-case 00125 0000000007 A*seven 343Insert into tb3 (f120, f122, f136, f144) 344values ('b', 'Test 3.5.8.5-case', 71,16); 345select f120, f122, f136, f144, @test_var 346from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136; 347f120 f122 f136 f144 @test_var 348A Test 3.5.8.5-case 00125 0000000007 B*0000000016 349B Test 3.5.8.5-case 00191 0000000016 B*0000000016 350Insert into tb3 (f120, f122, f136, f144) 351values ('c', 'Test 3.5.8.5-case', 80,1); 352select f120, f122, f136, f144, @test_var 353from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136; 354f120 f122 f136 f144 @test_var 355A Test 3.5.8.5-case 00125 0000000007 C=one 356B Test 3.5.8.5-case 00191 0000000016 C=one 357C Test 3.5.8.5-case 00200 0000000001 C=one 358Insert into tb3 (f120, f122, f136) 359values ('d', 'Test 3.5.8.5-case', 152); 360select f120, f122, f136, f144, @test_var 361from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136; 362f120 f122 f136 f144 @test_var 3631 Test 3.5.8.5-case 00152 0000099999 1*0000099999 364A Test 3.5.8.5-case 00125 0000000007 1*0000099999 365B Test 3.5.8.5-case 00191 0000000016 1*0000099999 366C Test 3.5.8.5-case 00200 0000000001 1*0000099999 367Insert into tb3 (f120, f122, f136, f144) 368values ('e', 'Test 3.5.8.5-case', 200, 8); 369select f120, f122, f136, f144, @test_var 370from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136; 371f120 f122 f136 f144 @test_var 3721 Test 3.5.8.5-case 00152 0000099999 1=eight 3731 Test 3.5.8.5-case 00200 0000000008 1=eight 374A Test 3.5.8.5-case 00125 0000000007 1=eight 375B Test 3.5.8.5-case 00191 0000000016 1=eight 376C Test 3.5.8.5-case 00200 0000000001 1=eight 377Insert into tb3 (f120, f122, f136, f144) 378values ('f', 'Test 3.5.8.5-case', 100, 8); 379select f120, f122, f136, f144, @test_var 380from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136; 381f120 f122 f136 f144 @test_var 3821 Test 3.5.8.5-case 00152 0000099999 1=eight 3831 Test 3.5.8.5-case 00200 0000000008 1=eight 384A Test 3.5.8.5-case 00125 0000000007 1=eight 385B Test 3.5.8.5-case 00191 0000000016 1=eight 386C Test 3.5.8.5-case 00200 0000000001 1=eight 387create trigger trg3a before update on tb3 for each row 388BEGIN 389CASE 390when new.f136<100 then set new.f120='p'; 391END// 392ERROR 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 393drop trigger trg3a; 394drop trigger trg3; 395delete from tb3 where f121='Test 3.5.8.5-case'; 396SET sql_mode = default; 397 398Testcase 3.5.8.5-loop/leave: 399---------------------------- 400Create trigger trg4 after insert on tb3 for each row 401BEGIN 402set @counter=0, @flag='Initial'; 403Label1: loop 404if new.f136<new.f144 then 405set @counter='Nothing to loop'; 406leave Label1; 407else 408set @counter=@counter+1; 409if new.f136=new.f144+@counter then 410set @counter=concat(@counter, ' loops'); 411leave Label1; 412end if; 413end if; 414iterate label1; 415set @flag='Final'; 416END loop Label1; 417END// 418Insert into tb3 (f122, f136, f144) 419values ('Test 3.5.8.5-loop', 2, 8); 420select @counter, @flag; 421@counter @flag 422Nothing to loop Initial 423Insert into tb3 (f122, f136, f144) 424values ('Test 3.5.8.5-loop', 11, 8); 425select @counter, @flag; 426@counter @flag 4273 loops Initial 428Create trigger trg4_2 after update on tb3 for each row 429BEGIN 430Label1: loop 431set @counter=@counter+1; 432END; 433END// 434ERROR 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 '; 435END' at line 5 436drop trigger trg4_2; 437drop trigger trg4; 438delete from tb3 where f122='Test 3.5.8.5-loop'; 439 440Testcase 3.5.8.5-repeat: 441------------------------ 442Create trigger trg6 after insert on tb3 for each row 443BEGIN 444rp_label: REPEAT 445SET @counter1 = @counter1 + 1; 446IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label; 447END IF; 448SET @counter2 = @counter2 + 1; 449UNTIL @counter1> new.f136 END REPEAT rp_label; 450END// 451set @counter1= 0, @counter2= 0; 452Insert into tb3 (f122, f136) 453values ('Test 3.5.8.5-repeat', 13); 454select @counter1, @counter2; 455@counter1 @counter2 45615 8 457Create trigger trg6_2 after update on tb3 for each row 458BEGIN 459REPEAT 460SET @counter2 = @counter2 + 1; 461END// 462ERROR 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 463drop trigger trg6; 464delete from tb3 where f122='Test 3.5.8.5-repeat'; 465 466Testcase 3.5.8.5-while: 467----------------------- 468Create trigger trg7 after insert on tb3 for each row 469wl_label: WHILE @counter1 < new.f136 DO 470SET @counter1 = @counter1 + 1; 471IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label; 472END IF; 473SET @counter2 = @counter2 + 1; 474END WHILE wl_label// 475set @counter1= 0, @counter2= 0; 476Insert into tb3 (f122, f136) 477values ('Test 3.5.8.5-while', 7); 478select @counter1, @counter2; 479@counter1 @counter2 4807 4 481Create trigger trg7_2 after update on tb3 for each row 482BEGIN 483WHILE @counter1 < new.f136 484SET @counter1 = @counter1 + 1; 485END// 486ERROR 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; 487END' at line 4 488delete from tb3 where f122='Test 3.5.8.5-while'; 489drop trigger trg7; 490 491Testcase 3.5.8.6: (requirement void) 492------------------------------------ 493CREATE PROCEDURE sp_01 () BEGIN set @v1=1; END// 494CREATE TRIGGER trg8_1 BEFORE UPDATE ON tb3 FOR EACH ROW 495BEGIN 496CALL sp_01 (); 497END// 498Insert into tb3 (f120, f122, f136) values ('6', 'Test 3.5.8.6-insert', 101); 499update tb3 set f120='S', f136=111, 500f122='Test 3.5.8.6-tr8_1' 501 where f122='Test 3.5.8.6-insert'; 502select f120, f122 503from tb3 where f122 like 'Test 3.5.8.6%' order by f120; 504f120 f122 505S Test 3.5.8.6-tr8_1 506DROP TRIGGER trg8_1; 507DROP PROCEDURE sp_01; 508 509Testcase 3.5.8.7 510---------------- 511Create trigger trg9_1 before update on tb3 for each row 512BEGIN 513Start transaction; 514Set new.f120='U'; 515Commit; 516END// 517ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. 518Create trigger trg9_2 before delete on tb3 for each row 519BEGIN 520Start transaction; 521Set @var2=old.f120; 522Rollback; 523END// 524ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. 525drop user test_general@localhost; 526drop user test_general; 527drop user test_super@localhost; 528DROP TABLE test.tb3; 529