memory_trig_08.result

这个文件是windows mysql源码

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 char(50),
f122 char(50),
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 = <engine_to_be_used>;
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

Testcase: 3.5:
--------------
create User test_general@localhost;
set password for test_general@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
create User test_super@localhost;
set password for test_super@localhost = password('PWD');
grant ALL on *.* to test_super@localhost with grant OPTION;
connect(localhost,test_general,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_super,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);

Testcase 3.5.8.1: (implied in previous tests)
---------------------------------------------

Testcase 3.5.8.2: (implied in previous tests)
---------------------------------------------

Testcase 3.5.8.3/4:
-------------------
create database db_test;
grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
grant LOCK TABLES on db_test.* to test_general;
Use db_test;
create table t1_i (
i120 char ascii not null DEFAULT b'101',
i136 smallint zerofill not null DEFAULT 999,
i144 int zerofill not null DEFAULT 99999,
i163 decimal (63,30)) engine=<engine_to_be_used>;
create table t1_u (
u120 char ascii not null DEFAULT b'101',
u136 smallint zerofill not null DEFAULT 999,
u144 int zerofill not null DEFAULT 99999,
u163 decimal (63,30)) engine=<engine_to_be_used>;
create table t1_d (
d120 char ascii not null DEFAULT b'101',
d136 smallint zerofill not null DEFAULT 999,
d144 int zerofill not null DEFAULT 99999,
d163 decimal (63,30)) engine=<engine_to_be_used>;
Insert into t1_u values ('a',111,99999,999.99);
Insert into t1_u values ('b',222,99999,999.99);
Insert into t1_u values ('c',333,99999,999.99);
Insert into t1_u values ('d',222,99999,999.99);
Insert into t1_u values ('e',222,99999,999.99);
Insert into t1_u values ('f',333,99999,999.99);
Insert into t1_d values ('a',111,99999,999.99);
Insert into t1_d values ('b',222,99999,999.99);
Insert into t1_d values ('c',333,99999,999.99);
Insert into t1_d values ('d',444,99999,999.99);
Insert into t1_d values ('e',222,99999,999.99);
Insert into t1_d values ('f',222,99999,999.99);

3.5.8.4 - multiple SQL
----------------------
use test;
Create trigger trg1 AFTER INSERT on tb3 for each row
BEGIN
insert into db_test.t1_i
values (new.f120, new.f136, new.f144, new.f163);
update db_test.t1_u
set u144=new.f144, u163=new.f163
where u136=new.f136;
delete from db_test.t1_d where d136= new.f136;
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
where u136= new.f136;
END//
Use test;
set @test_var=0;
Insert into tb3 (f120, f122, f136, f144, f163)
values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
f120	f122	f136	f144	f163
1	Test 3.5.8.4	00222	0000023456	1.050000000000000000000000000000
select * from db_test.t1_i;
i120	i136	i144	i163
1	00222	0000023456	1.050000000000000000000000000000
select * from db_test.t1_u;
u120	u136	u144	u163
a	00111	0000099999	999.990000000000000000000000000000
b	00222	0000023456	1.050000000000000000000000000000
c	00333	0000099999	999.990000000000000000000000000000
d	00222	0000023456	1.050000000000000000000000000000
e	00222	0000023456	1.050000000000000000000000000000
f	00333	0000099999	999.990000000000000000000000000000
select * from db_test.t1_d;
d120	d136	d144	d163
a	00111	0000099999	999.990000000000000000000000000000
c	00333	0000099999	999.990000000000000000000000000000
d	00444	0000099999	999.990000000000000000000000000000
select @test_var;
@test_var
3.150000000000000000000000000000

3.5.8.4 - single SQL - insert
-----------------------------
Create trigger trg2 BEFORE UPDATE on tb3 for each row
BEGIN
insert into db_test.t1_i
values (new.f120, new.f136, new.f144, new.f163);
END//
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
f120	f122	f136	f144	f163
1	Test 3.5.8.4	00222	0000023456	1.050000000000000000000000000000
select * from db_test.t1_i order by i120;
i120	i136	i144	i163
1	00222	0000023456	1.050000000000000000000000000000
update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
		 where f122='Test 3.5.8.4';
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
f120	f122	f136	f144	f163
I	Test 3.5.8.4-Single Insert	00222	0000023456	1.050000000000000000000000000000
select * from db_test.t1_i order by i120;
i120	i136	i144	i163
1	00222	0000023456	1.050000000000000000000000000000
I	00222	0000023456	1.050000000000000000000000000000

3.5.8.4 - single SQL - update
-----------------------------
drop trigger trg2;
Create trigger trg3 BEFORE UPDATE on tb3 for each row
update db_test.t1_u
set u120=new.f120
where u136=new.f136;
update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
		 where f122='Test 3.5.8.4-Single Insert';
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
f120	f122	f136	f144	f163
U	Test 3.5.8.4-Single Update	00222	0000023456	1.050000000000000000000000000000
select * from db_test.t1_u order by u120;
u120	u136	u144	u163
a	00111	0000099999	999.990000000000000000000000000000
c	00333	0000099999	999.990000000000000000000000000000
f	00333	0000099999	999.990000000000000000000000000000
U	00222	0000023456	1.050000000000000000000000000000
U	00222	0000023456	1.050000000000000000000000000000
U	00222	0000023456	1.050000000000000000000000000000

3.5.8.3/4 - single SQL - delete
-------------------------------
drop trigger trg3;
Create trigger trg4 AFTER UPDATE on tb3 for each row
delete from db_test.t1_d where d136= new.f136;
update tb3 set f120='D', f136=444,
f122='Test 3.5.8.4-Single Delete'
		 where f122='Test 3.5.8.4-Single Update';
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
f120	f122	f136	f144	f163
D	Test 3.5.8.4-Single Delete	00444	0000023456	1.050000000000000000000000000000
select * from db_test.t1_d order by d120;
d120	d136	d144	d163
a	00111	0000099999	999.990000000000000000000000000000
c	00333	0000099999	999.990000000000000000000000000000

3.5.8.3/4 - single SQL - select
-------------------------------
drop trigger trg4;
Create trigger trg5 AFTER UPDATE on tb3 for each row
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
where u136= new.f136;
set @test_var=0;
update tb3 set f120='S', f136=111,
f122='Test 3.5.8.4-Single Select'
		 where f122='Test 3.5.8.4-Single Delete';
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
f120	f122	f136	f144	f163
S	Test 3.5.8.4-Single Select	00111	0000023456	1.050000000000000000000000000000
select @test_var;
@test_var
999.990000000000000000000000000000
drop trigger trg1;
drop trigger trg5;
drop database if exists db_test;
delete from tb3 where f122 like 'Test 3.5.8.4%';
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';

Testcase 3.5.8.5 (IF):
----------------------
create trigger trg2 before insert on tb3 for each row
BEGIN
IF new.f120='1' then
set @test_var='one', new.f120='2';
ELSEIF new.f120='2' then
set @test_var='two', new.f120='3';
ELSEIF new.f120='3' then
set @test_var='three', new.f120='4';
END IF;
IF (new.f120='4') and (new.f136=10) then
set @test_var2='2nd if', new.f120='d';
ELSE
set @test_var2='2nd else', new.f120='D';
END IF;
END//
set @test_var='Empty', @test_var2=0;
Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
select f120, f122, f136, @test_var, @test_var2
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
f120	f122	f136	@test_var	@test_var2
D	Test 3.5.8.5-if	00101	one	2nd else
Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
select f120, f122, f136, @test_var, @test_var2
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
f120	f122	f136	@test_var	@test_var2
D	Test 3.5.8.5-if	00101	two	2nd else
D	Test 3.5.8.5-if	00102	two	2nd else
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);