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 62load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/memory_tb3.txt' 63into table tb3; 64 65Testcase 3.5.9.1/2: 66------------------- 67Create trigger trg1 BEFORE UPDATE on tb3 for each row 68set new.f142 = 94087, @counter=@counter+1; 69TotalRows 7010 71Affected 729 73NotAffected 741 75NewValuew 760 77set @counter=0; 78Update tb3 Set f142='1' where f130<100; 79select count(*) as ExpectedChanged, @counter as TrigCounter 80from tb3 where f142=94087; 81ExpectedChanged TrigCounter 829 9 83select count(*) as ExpectedNotChange from tb3 84where f130<100 and f142<>94087; 85ExpectedNotChange 860 87select count(*) as NonExpectedChanged from tb3 88where f130>=130 and f142=94087; 89NonExpectedChanged 900 91drop trigger trg1; 92 93Testcase 3.5.9.3: 94----------------- 95Create trigger trg2_a before update on tb3 for each row 96set @tr_var_b4_118=old.f118, @tr_var_b4_121=old.f121, 97@tr_var_b4_122=old.f122, @tr_var_b4_136=old.f136, 98@tr_var_b4_163=old.f163; 99Create trigger trg2_b after update on tb3 for each row 100set @tr_var_af_118=old.f118, @tr_var_af_121=old.f121, 101@tr_var_af_122=old.f122, @tr_var_af_136=old.f136, 102@tr_var_af_163=old.f163; 103Create trigger trg2_c before delete on tb3 for each row 104set @tr_var_b4_118=old.f118, @tr_var_b4_121=old.f121, 105@tr_var_b4_122=old.f122, @tr_var_b4_136=old.f136, 106@tr_var_b4_163=old.f163; 107Create trigger trg2_d after delete on tb3 for each row 108set @tr_var_af_118=old.f118, @tr_var_af_121=old.f121, 109@tr_var_af_122=old.f122, @tr_var_af_136=old.f136, 110@tr_var_af_163=old.f163; 111@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_163 1120 0 0 0 0 113@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_163 1140 0 0 0 0 115Insert into tb3 (f122, f136, f163) 116values ('Test 3.5.9.3', 7, 123.17); 117Update tb3 Set f136=8 where f122='Test 3.5.9.3'; 118select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3' order by f136; 119f118 f121 f122 f136 f163 120a NULL Test 3.5.9.3 00008 123.170000000000000000000000000000 121select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 122@tr_var_b4_136, @tr_var_b4_163; 123@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_163 124a NULL Test 3.5.9.3 7 123.170000000000000000000000000000 125select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 126@tr_var_af_136, @tr_var_af_163; 127@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_163 128a NULL Test 3.5.9.3 7 123.170000000000000000000000000000 129@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_163 1300 0 0 0 0 131@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_163 1320 0 0 0 0 133delete from tb3 where f122='Test 3.5.9.3'; 134select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3' order by f136; 135f118 f121 f122 f136 f163 136select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 137@tr_var_b4_136, @tr_var_b4_163; 138@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_163 139a NULL Test 3.5.9.3 8 123.170000000000000000000000000000 140select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 141@tr_var_af_136, @tr_var_af_163; 142@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_163 143a NULL Test 3.5.9.3 8 123.170000000000000000000000000000 144drop trigger trg2_a; 145drop trigger trg2_b; 146drop trigger trg2_c; 147drop trigger trg2_d; 148 149Testcase 3.5.9.4: 150----------------- 151Create trigger trg3_a before insert on tb3 for each row 152set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121, 153@tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136, 154@tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163; 155Create trigger trg3_b after insert on tb3 for each row 156set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121, 157@tr_var_af_122=new.f122, @tr_var_af_136=new.f136, 158@tr_var_af_151=new.f151, @tr_var_af_163=new.f163; 159Create trigger trg3_c before update on tb3 for each row 160set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121, 161@tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136, 162@tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163; 163Create trigger trg3_d after update on tb3 for each row 164set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121, 165@tr_var_af_122=new.f122, @tr_var_af_136=new.f136, 166@tr_var_af_151=new.f151, @tr_var_af_163=new.f163; 167@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163 1680 0 0 0 0 0 169@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163 1700 0 0 0 0 0 171Insert into tb3 (f122, f136, f151, f163) 172values ('Test 3.5.9.4', 7, DEFAULT, 995.24); 173select f118, f121, f122, f136, f151, f163 from tb3 174where f122 like 'Test 3.5.9.4%' order by f163; 175f118 f121 f122 f136 f151 f163 176a NULL Test 3.5.9.4 00007 999 995.240000000000000000000000000000 177select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 178@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163; 179@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163 180a NULL Test 3.5.9.4 7 999 995.240000000000000000000000000000 181select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 182@tr_var_af_136, @tr_var_af_151, @tr_var_af_163; 183@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163 184a NULL Test 3.5.9.4 7 999 995.240000000000000000000000000000 185@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163 1860 0 0 0 0 0 187@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163 1880 0 0 0 0 0 189update ignore tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL 190where f122='Test 3.5.9.4'; 191Warnings: 192Warning 1048 Column 'f136' cannot be null 193select f118, f121, f122, f136, f151, f163 from tb3 194where f122 like 'Test 3.5.9.4-trig' order by f163; 195f118 f121 f122 f136 f151 f163 196a NULL Test 3.5.9.4-trig 00000 999 NULL 197select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 198@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163; 199@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163 200a NULL Test 3.5.9.4-trig NULL 999 NULL 201select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 202@tr_var_af_136, @tr_var_af_151, @tr_var_af_163; 203@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163 204a NULL Test 3.5.9.4-trig 0 999 NULL 205drop trigger trg3_a; 206drop trigger trg3_b; 207drop trigger trg3_c; 208drop trigger trg3_d; 209delete from tb3 where f122='Test 3.5.9.4-trig'; 210 211Testcase 3.5.9.5: (implied in previous tests) 212--------------------------------------------- 213 214Testcase 3.5.9.6: 215----------------- 216create trigger trg4a before insert on tb3 for each row 217set @temp1= old.f120; 218ERROR HY000: There is no OLD row in on INSERT trigger 219create trigger trg4b after insert on tb3 for each row 220set old.f120= 'test'; 221ERROR HY000: Updating of OLD row is not allowed in trigger 222drop trigger trg4a; 223drop trigger trg4b; 224 225Testcase 3.5.9.7: (implied in previous tests) 226--------------------------------------------- 227 228Testcase 3.5.9.8: (implied in previous tests) 229--------------------------------------------- 230 231Testcase 3.5.9.9: 232----------------- 233create trigger trg5a before DELETE on tb3 for each row 234set @temp1=new.f122; 235ERROR HY000: There is no NEW row in on DELETE trigger 236create trigger trg5b after DELETE on tb3 for each row 237set new.f122='test'; 238ERROR HY000: There is no NEW row in on DELETE trigger 239drop trigger trg5a; 240drop trigger trg5b; 241 242Testcase 3.5.9.10: (implied in previous tests) 243---------------------------------------------- 244 245Testcase 3.5.9.11: covered by 3.5.9.9 246------------------------------------- 247 248Testcase 3.5.9.12: covered by 3.5.9.6 249------------------------------------- 250 251Testcase 3.5.9.13: 252------------------ 253create trigger trg6a before UPDATE on tb3 for each row 254set old.f118='C', new.f118='U'; 255ERROR HY000: Updating of OLD row is not allowed in trigger 256create trigger trg6b after INSERT on tb3 for each row 257set old.f136=163, new.f118='U'; 258ERROR HY000: Updating of OLD row is not allowed in trigger 259create trigger trg6c after UPDATE on tb3 for each row 260set old.f136=NULL; 261ERROR HY000: Updating of OLD row is not allowed in trigger 262drop trigger trg6a; 263drop trigger trg6b; 264drop trigger trg6c; 265 266Testcase 3.5.9.14: (implied in previous tests) 267---------------------------------------------- 268DROP TABLE test.tb3; 269