发布时间 : 星期二 文章数据库大作业 物流管理系统附代码更新完毕开始阅读5b0469570740be1e650e9a5f
psno varchar(20) not null primary key, ydno varchar(20) not null, ygno varchar(20), cktime varchar(20), wctime varchar(20),
foreign key(ygno) references yuangong(ygno),
foreign key(ydno) references yundan(ydno) on update cascade )
配送表数据输入
insert into peisong values(70001,50001,20008,2014-05-11,2014-05-12) insert into peisong values(70002,50002,20008,2014-05-11,2014-05-12) insert into peisong values(70003,50003,20008,2014-05-11,2014-05-12) insert into peisong values(70004,50004,20009,2014-05-11,2014-05-12) insert into peisong values(70005,50005,20009,2014-05-11,2014-05-12) insert into peisong values(70006,50006,20009,2014-05-11,2014-05-12) insert into peisong values(70007,50007,20010,2014-05-11,2014-05-12)
(8)创建评价表 create table pingjia( pjno varchar(20) not null, ydno varchar(20) not null, speed int not null default 10, serve int not null default 10, total int not null default 10, pj varchar(100),
foreign key(ydno) references yundan(ydno), )
insert into pingjia values(80001,50001,10,10,10,'好') insert into pingjia values(80002,50002,8,10,9,'不错') insert into pingjia values(80003,50003,10,6,8,'不错') insert into pingjia values(80004,50004,10,8,9,'不错') insert into pingjia values(80005,50005,10,10,10,'好') insert into pingjia values(80006,50006,6,6,6,'有待加强') insert into pingjia values(80007,50007,10,8,9,'不错')
4.1.2派生数据说明
yundan表中的运费总价ydprice，根据货物重量乘以100 pingjia表中的总评total，是根据speed/2+serve/2
4.1.3业务规则存档
（给出使用约束、触发器等来实现业务规则的情况）
(1)在运单表yundan上创建触发器，若price为null，则其值设为货物重量hwweight×100
create trigger yd_price on yundan for insert as
declare @price int, @hwweight int,
@ydno varchar(20)
select @price=inserted.ydprice,@hwweight=inserted.hwweight,@ydno=inserted.ydno from inserted if(@price is null) begin
update yundan set ydprice=@hwweight*100 where ydno=@ydno
end
(2)转运表zhuanyun上创建触发器，若zhuanyun表中的cangku为null，则由仓库负责区域和收件人地址决定转运到哪个仓库
create trigger decide_ck on zhuanyun for insert as
declare @zyno varchar(20),
@ckno varchar(20), @ydno varchar(20), @shradd varchar(20)
select @ckno=inserted.ckno,@ydno=inserted.ydno,@zyno=inserted.zyno from inserted if(@ckno is null)
begin select @shradd=shr.shradd
from inserted,yundan yd,shouhuoren shr
where inserted.ydno=yd.ydno and yd.shrno=shr.shrno print @shradd
if(@shradd like '上海%' ) set @ckno=1 else if (@shradd like '北京%' ) set @ckno=2
else if (@shradd like '广州%' ) else if (@shradd like '武汉%' )
set @ckno=3 set @ckno=4
else set @ckno=5 update zhuanyun set ckno=@ckno where zhuanyun.zyno=@zyno end go
(3)当运单状态为完成时，将不能修改运单 create trigger yundan_nofix on yundan for update as
declare @state varchar(20) select @state=deleted.ydstate from deleted
if (@state='完成')
(4)当运单状态为完成时，不能在此运单上新建配送 create trigger peisong_nofix on yundan for insert as
declare @state varchar(20)
select @state=inserted.ydno from inserted if (@state='完成')
rollback transaction
(4)当运单状态为完成时，不能再在此运单上新建转运 create trigger zhuanyun_nofix on zhuanyun for insert as
declare @state varchar(20)
select @state=inserted.ydno from inserted if (@state='完成') rollback transaction
(5)当货物开始转运，新建转运项时，把订单状态设置为'派送' create trigger add_zy on zhuanyun for insert as
rollback transaction
declare @ydno varchar(20)
select @ydno=inserted.ydno from inserted
update yundan set yundan.ydstate='派送' where yundan.ydno=@ydno
(6)在zhuanyun表中设置触发器，计算仓库储存量ckweight的值，每当货物运进该仓库，ckweight加上该货物重量。
当仓库货物达到或超过仓库容量时，则给予预警, create trigger ckweight_up on zhuanyun for update,insert as
declare @hwweight bigint,
@ckno varchar(20), @ckweight bigint,
@ckcap bigint
select @hwweight=yd.hwweight,@ckno=inserted.ckno
from yundan yd,inserted
where yd.ydno=inserted.ydno
update cangku set cangku.ckweight=cangku.ckweight+@hwweight where cangku.ckno=@ckno
select @ckweight=ck.ckweight,@ckcap=ck.ckcap,@ckno=inserted.ckno from inserted,cangku ck
where inserted.ckno=ck.ckno if(@ckweight>=@ckcap) begin
rollback transaction
print '仓请注意'+@ckno+'仓库以满'
end go
(8)在zhuanyun表中设置触发器,当新建配送项时，表示运单已经完成，运单状态ydstate变为'完成'
create trigger add_ps on peisong for insert as
declare @ydno varchar(20) select @ydno=inserted.ydno
from inserted
update yundan set yundan.ydstate='完成' where yundan.ydno=@ydno
(9)在运单表中设置一个触发器，当运单状态为“接受”时,可以取消订单，为其他状态时，无法取消订单 create trigger kehu_drop on yundan for delete as