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