diff options
Diffstat (limited to 'mysql-test/suite/funcs_1/r/myisam_trig_09.result')
| -rw-r--r-- | mysql-test/suite/funcs_1/r/myisam_trig_09.result | 271 |
1 files changed, 271 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/r/myisam_trig_09.result b/mysql-test/suite/funcs_1/r/myisam_trig_09.result new file mode 100644 index 00000000000..6a98e527244 --- /dev/null +++ b/mysql-test/suite/funcs_1/r/myisam_trig_09.result @@ -0,0 +1,271 @@ +SET @NO_REFRESH = IF( '' = '', 0, 1); +USE test; +drop table if exists tb3 ; +create table tb3 ( +f118 char not null DEFAULT 'a', +f119 char binary not null DEFAULT b'101', +f120 char ascii not null DEFAULT b'101', +f121 tinytext, +f122 text, +f123 mediumtext, +f124 longtext unicode, +f125 tinyblob, +f126 blob, +f127 mediumblob, +f128 longblob, +f129 binary not null DEFAULT b'101', +f130 tinyint not null DEFAULT 99, +f131 tinyint unsigned not null DEFAULT 99, +f132 tinyint zerofill not null DEFAULT 99, +f133 tinyint unsigned zerofill not null DEFAULT 99, +f134 smallint not null DEFAULT 999, +f135 smallint unsigned not null DEFAULT 999, +f136 smallint zerofill not null DEFAULT 999, +f137 smallint unsigned zerofill not null DEFAULT 999, +f138 mediumint not null DEFAULT 9999, +f139 mediumint unsigned not null DEFAULT 9999, +f140 mediumint zerofill not null DEFAULT 9999, +f141 mediumint unsigned zerofill not null DEFAULT 9999, +f142 int not null DEFAULT 99999, +f143 int unsigned not null DEFAULT 99999, +f144 int zerofill not null DEFAULT 99999, +f145 int unsigned zerofill not null DEFAULT 99999, +f146 bigint not null DEFAULT 999999, +f147 bigint unsigned not null DEFAULT 999999, +f148 bigint zerofill not null DEFAULT 999999, +f149 bigint unsigned zerofill not null DEFAULT 999999, +f150 decimal not null DEFAULT 999.999, +f151 decimal unsigned not null DEFAULT 999.17, +f152 decimal zerofill not null DEFAULT 999.999, +f153 decimal unsigned zerofill, +f154 decimal (0), +f155 decimal (64), +f156 decimal (0) unsigned, +f157 decimal (64) unsigned, +f158 decimal (0) zerofill, +f159 decimal (64) zerofill, +f160 decimal (0) unsigned zerofill, +f161 decimal (64) unsigned zerofill, +f162 decimal (0,0), +f163 decimal (63,30), +f164 decimal (0,0) unsigned, +f165 decimal (63,30) unsigned, +f166 decimal (0,0) zerofill, +f167 decimal (63,30) zerofill, +f168 decimal (0,0) unsigned zerofill, +f169 decimal (63,30) unsigned zerofill, +f170 numeric, +f171 numeric unsigned, +f172 numeric zerofill, +f173 numeric unsigned zerofill, +f174 numeric (0), +f175 numeric (64) +) Engine = myisam; +Warnings: +Note 1265 Data truncated for column 'f150' at row 1 +Note 1265 Data truncated for column 'f151' at row 1 +Note 1265 Data truncated for column 'f152' at row 1 +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/myisam_tb3.txt' into table tb3 ; + +Testcase 3.5.9.1/2: +------------------- +Create trigger trg1 BEFORE UPDATE on tb3 for each row +set new.f142 = 94087, @counter=@counter+1; +TotalRows +10 +Affected +8 +NotAffected +2 +NewValuew +0 +set @counter=0; +Update tb3 Set f142='1' where f130<100; +select count(*) as ExpectedChanged, @counter as TrigCounter +from tb3 where f142=94087; +ExpectedChanged TrigCounter +8 8 +select count(*) as ExpectedNotChange from tb3 +where f130<100 and f142<>94087; +ExpectedNotChange +0 +select count(*) as NonExpectedChanged from tb3 +where f130>=130 and f142=94087; +NonExpectedChanged +0 +drop trigger trg1; + +Testcase 3.5.9.3: +----------------- +Create trigger trg2_a before update on tb3 for each row +set @tr_var_b4_118=old.f118, @tr_var_b4_121=old.f121, +@tr_var_b4_122=old.f122, @tr_var_b4_136=old.f136, +@tr_var_b4_163=old.f163; +Create trigger trg2_b after update on tb3 for each row +set @tr_var_af_118=old.f118, @tr_var_af_121=old.f121, +@tr_var_af_122=old.f122, @tr_var_af_136=old.f136, +@tr_var_af_163=old.f163; +Create trigger trg2_c before delete on tb3 for each row +set @tr_var_b4_118=old.f118, @tr_var_b4_121=old.f121, +@tr_var_b4_122=old.f122, @tr_var_b4_136=old.f136, +@tr_var_b4_163=old.f163; +Create trigger trg2_d after delete on tb3 for each row +set @tr_var_af_118=old.f118, @tr_var_af_121=old.f121, +@tr_var_af_122=old.f122, @tr_var_af_136=old.f136, +@tr_var_af_163=old.f163; +@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_163 +0 0 0 0 0 +@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_163 +0 0 0 0 0 +Insert into tb3 (f122, f136, f163) +values ('Test 3.5.9.3', 7, 123.17); +Update tb3 Set f136=8 where f122='Test 3.5.9.3'; +select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3' order by f136; +f118 f121 f122 f136 f163 +a NULL Test 3.5.9.3 00008 123.170000000000000000000000000000 +select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, +@tr_var_b4_136, @tr_var_b4_163; +@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_163 +a NULL Test 3.5.9.3 7 123.170000000000000000000000000000 +select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, +@tr_var_af_136, @tr_var_af_163; +@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_163 +a NULL Test 3.5.9.3 7 123.170000000000000000000000000000 +@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_163 +0 0 0 0 0 +@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_163 +0 0 0 0 0 +delete from tb3 where f122='Test 3.5.9.3'; +select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3' order by f136; +f118 f121 f122 f136 f163 +select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, +@tr_var_b4_136, @tr_var_b4_163; +@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_163 +a NULL Test 3.5.9.3 8 123.170000000000000000000000000000 +select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, +@tr_var_af_136, @tr_var_af_163; +@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_163 +a NULL Test 3.5.9.3 8 123.170000000000000000000000000000 +drop trigger trg2_a; +drop trigger trg2_b; +drop trigger trg2_c; +drop trigger trg2_d; + +Testcase 3.5.9.4: +----------------- +Create trigger trg3_a before insert on tb3 for each row +set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121, +@tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136, +@tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163; +Create trigger trg3_b after insert on tb3 for each row +set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121, +@tr_var_af_122=new.f122, @tr_var_af_136=new.f136, +@tr_var_af_151=new.f151, @tr_var_af_163=new.f163; +Create trigger trg3_c before update on tb3 for each row +set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121, +@tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136, +@tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163; +Create trigger trg3_d after update on tb3 for each row +set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121, +@tr_var_af_122=new.f122, @tr_var_af_136=new.f136, +@tr_var_af_151=new.f151, @tr_var_af_163=new.f163; +@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163 +0 0 0 0 0 0 +@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163 +0 0 0 0 0 0 +Insert into tb3 (f122, f136, f151, f163) +values ('Test 3.5.9.4', 7, DEFAULT, 995.24); +select f118, f121, f122, f136, f151, f163 from tb3 +where f122 like 'Test 3.5.9.4%' order by f163; +f118 f121 f122 f136 f151 f163 +a NULL Test 3.5.9.4 00007 999 995.240000000000000000000000000000 +select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, +@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163; +@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163 +a NULL Test 3.5.9.4 7 999 995.240000000000000000000000000000 +select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, +@tr_var_af_136, @tr_var_af_151, @tr_var_af_163; +@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163 +a NULL Test 3.5.9.4 7 999 995.240000000000000000000000000000 +@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163 +0 0 0 0 0 0 +@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163 +0 0 0 0 0 0 +Update tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL +where f122='Test 3.5.9.4'; +Warnings: +Warning 1048 Column 'f136' cannot be null +select f118, f121, f122, f136, f151, f163 from tb3 +where f122 like 'Test 3.5.9.4-trig' order by f163; +f118 f121 f122 f136 f151 f163 +a NULL Test 3.5.9.4-trig 00000 999 NULL +select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, +@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163; +@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163 +a NULL Test 3.5.9.4-trig 0 999 NULL +select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, +@tr_var_af_136, @tr_var_af_151, @tr_var_af_163; +@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163 +a NULL Test 3.5.9.4-trig 0 999 NULL +drop trigger trg3_a; +drop trigger trg3_b; +drop trigger trg3_c; +drop trigger trg3_d; +delete from tb3 where f122='Test 3.5.9.4-trig'; + +Testcase 3.5.9.5: (implied in previous tests) +--------------------------------------------- + +Testcase 3.5.9.6: +----------------- +create trigger trg4a before insert on tb3 for each row +set @temp1= old.f120; +ERROR HY000: There is no OLD row in on INSERT trigger +create trigger trg4b after insert on tb3 for each row +set old.f120= 'test'; +ERROR HY000: Updating of OLD row is not allowed in trigger +drop trigger trg4a; +drop trigger trg4b; + +Testcase 3.5.9.7: (implied in previous tests) +--------------------------------------------- + +Testcase 3.5.9.8: (implied in previous tests) +--------------------------------------------- + +Testcase 3.5.9.9: +----------------- +create trigger trg5a before DELETE on tb3 for each row +set @temp1=new.f122; +ERROR HY000: There is no NEW row in on DELETE trigger +create trigger trg5b after DELETE on tb3 for each row +set new.f122='test'; +ERROR HY000: There is no NEW row in on DELETE trigger +drop trigger trg5b; + +Testcase 3.5.9.10: (implied in previous tests) +---------------------------------------------- + +Testcase 3.5.9.11: covered by 3.5.9.9 +------------------------------------- + +Testcase 3.5.9.12: covered by 3.5.9.6 +------------------------------------- + +Testcase 3.5.9.13: +------------------ +create trigger trg6a before UPDATE on tb3 for each row +set old.f118='C', new.f118='U'; +ERROR HY000: Updating of OLD row is not allowed in trigger +create trigger trg6b after INSERT on tb3 for each row +set old.f136=163, new.f118='U'; +ERROR HY000: Updating of OLD row is not allowed in trigger +create trigger trg6c after UPDATE on tb3 for each row +set old.f136=NULL; +ERROR HY000: Updating of OLD row is not allowed in trigger +drop trigger trg6a; +drop trigger trg6b; +drop trigger trg6c; + +Testcase 3.5.9.14: (implied in previous tests) +---------------------------------------------- |