mysql触发器设计构思goods(在仓库里的货号与总数量)numberpcs112012311203781130939out（每个月外出的货的件数,注意,分几次出货.）numberpcsdate1120323-41120343-51120334-51130924-9每出几件货,便在out中

Question

mysql触发器设计构思
goods(在仓库里的货号与总数量)
number pcs
11201 23
11203 78
11309 39
out（每个月外出的货的件数,注意,分几次出货.）
number pcs date
11203 2 3-4
11203 4 3-5
11203 3 4-5
11309 2 4-9
每出几件货,便在out中,把箱号,件数,日期记下来.
每当向out中记下一次出货件数时,goods的数量要随之减小.
比如：
今天出货11201出库3.则第一：out中记下11201,3 5-9第二,goods中11201数量要减小 3.

Answer 1

create trigger out_i on out after insert for each row
as
begin
declare @number_i varchar(10);
declare @pcs_i int;
declare @date_i datetime;
declare @n;
select @number_i=number,@pcs_i =pcs,@date_i=date from inserted;
select @n=count(*) from goods where number = @number_i ;
if @n=0 then
insert into goods(number ,pcs) values(@number_i,@pcs_i );
else update goods set pcs=pcs- @pcs_i where number = @number_i ;
END IF;
END$$
DELIMITER ;