数据库课程设计（酒店客房管理系统）论文[1]

2005级信管专业2班数据库应用系统课程设计课程论文

@money float as begin update guest set Account=@money where Gno=@Gno update RoomState

set Atime=@Atime,Ltime=@Ltime,Days=@Days,Stime=@Stime,flag='2' where Rno=@Rno and Gno=@Gno end

12.lsqInsertAmends的定义

create procedure lsqInsertAmends @Gno char(20), @Rno char(10), @Goodsno char(20), @Dnum int,

@Amendstime datetime as insert

into GoAmInfo(Gno,Rno,Goodsno,Dnum,Amendstime) values(@Gno,@Rno,@Goodsno,@Dnum,@Amendstime) 13.lsqDeleteRoom的定义

create procedure lsqDeleteRoom @Rno char(10), @Gno char(20) as delete from RoomState

where Rno=@Rno and Gno=@Gno

4.4建立触发器SQl语句

1.当插入订房信息（即插入房态信息），触发guest表，根据顾客积分计算顾客新的折扣度 CREATE TRIGGER tri_discount ON RoomState

FOR insert AS declare @Grade int, @Gno char(20)

33

2005级信管专业2班数据库应用系统课程设计课程论文

select @Gno=Gno from inserted

select @Grade=Grade from guest where Gno=@Gno if (@Grade >= 0 and @Grade<300) begin update guest set discount=1.00 where Gno=@Gno end

else if(@Grade<500 ) begin update guest set discount=0.95 where Gno=@Gno end

else if(@Grade<700) begin update guest set discount=0.90 where Gno=@Gno end

else if (@Grade<1000) begin update guest set discount=0.85 where Gno=@Gno end else begin update guest set discount=0.80

where Gno in(select Gno from inserted) end

2.当修改房态信息（即插入入住信息）时，触发guest表，计算新积分及余额 CREATE TRIGGER tri_grade_balance on RoomState for update as begin declare

@IntoPrice float, @Days int

34

2005级信管专业2班数据库应用系统课程设计课程论文

select @IntoPrice=IntoPrice from RoomState where Rno in(select Rno from inserted ) and Gno in(select Gno from inserted) select @Days=Days from RoomState where Rno in(select Rno from inserted ) and

Gno in(select Gno from inserted) update guest

set balance=Account-@IntoPrice*@Days,grade=grade+@IntoPrice*@Days where Gno in(select Gno from inserted) end

3.删除客房信息（即退房）时，触发guest表，把顾客的预付款和余额设为0 CREATE TRIGGER tri_delete on RoomState for delete as begin update guest

set Account=0,balance=0

where Gno in(select Gno from deleted) end

4.当插入新的娱乐消费信息时，触发guest表，从新计算顾客积分和余额 CREATE TRIGGER tri_grade1 ON Consumelist FOR insert As begin declare @Gno char(20), @Atno char(20), @Amount int, @Atprice float

select @Gno=Gno,@Atno=Atno,@Amount=Amount from inserted select @Atprice=Atprice from Atariff where Atno=@Atno update guest

SET grade=grade+@Atprice*@Amount/10,balance=balance-@Atprice*@Amount where Gno=@Gno END

5.当插入新的物品赔偿信息时，触发孤儿guest表，从新计算顾客积分和余额 CREATE TRIGGER tri_AmendsMoney ON GoAmInfo for insert as begin declare @Gno char(20),

35

2005级信管专业2班数据库应用系统课程设计课程论文

@Goodsno char(20), @Dnum int, @Oprice float, @Dmultiple float

select @Gno=Gno,@Goodsno=Goodsno,@Dnum=Dnum from inserted select @Oprice=Oprice,@Dmultiple=Dmultiple from RoGoInfo

where Goodsno=@Goodsno update guest

set balance=balance-@Oprice*@Dnum*@Dmultiple where @Gno=Gno end

36