数据库大作业 物流管理系统附代码

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