【正文】 datetime, @days int, @Stime datetime, @money float as begin update guest set PreAccount=@money update RoomState set Atime=@Atime,Ltime=@Ltime,days=@days,Stime=@Stime,flag=39。239。 where Rno=@Rno and Gno=@Gno end （ 12） IsqInsertAmends 的定義： create procedure IsqInsertAmends @Gno char(20), @Rno char(10), @Obno char(20), @Dnum int, @Dtime datetime as insert into ReturnObject(Gno,Rno,Obno,Dnum,Dtime) 各專業(yè)畢業(yè)論文范文盡在道客巴巴下載 各專業(yè)畢業(yè)論文范文 盡在道客巴巴下載 values(@Gno,@Rno,@Obno,@Dnum,@Dtime) （ 13） lsqDeleteRoom 的定義： create procedure IsqDeleteRoom @Rno char(10), @Gno char(20) as delete from RoomState where Rno=@Rno and Gno=@Gno 創(chuàng)建觸發(fā)器 （ 1） 當(dāng)插入訂房信息（即插入房態(tài) 信息），觸發(fā) guest 表，根據(jù)顧客積分計算顧客新的折扣度： create trigger tri_Discount on RoomState for insert as declare @Grade int, @Gno char(20) select @Gno=Gno from inserted select @Grade=Grade from guest where Gno=@Gno if(@Grade=0 and @Grade300) begin update guest set Discount= where Gno=@Gno end else if(@Grade500) begin update guest set Discount= where Gno=@Gno end else if(@Grade700) begin update guest set Discount= where Gno=@Gno end else if(@Grade1000) 各專業(yè)畢業(yè)論文范文盡在道客巴巴下載 各專業(yè)畢業(yè)論文范文 盡在道客巴巴下載 begin update guest set Discount= where Gno=@Gno end else begin update guest set Discount= where Gno=@Gno end （ 2）當(dāng)修改房態(tài)信息（即插入入住信息）時，觸發(fā) guest 表，計算新積分及余額： create trigger tri_grade_balance on RoomState for update as begin declare @IntoPrice float, @days int 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=PreAccount@IntoPrice*@days,grade=grade+@IntoPrice*@days where Gno in(select Gno from inserted) end （ 3）當(dāng)修改房態(tài)信息（即插入入住信息）時，觸發(fā) guest 表，計算新積分及余額： create trigger tri_delete on RoomState for delete as begin update guest set PreAccount=0,balance=0 where Gno in(select Gno from deleted) end （ 4）刪除客房 信息（即退房）時，觸發(fā) guest 表，把顧客的預(yù)付款和余額設(shè)為 0： create trigger tri_grade on ConsumeList for insert 各專業(yè)畢業(yè)論文范文盡在道客巴巴下載 各專業(yè)畢業(yè)論文范文 盡在道客巴巴下載 as begin declare @Gno char(20), @Yno char(20), @Amount int, @Yprice float select @Gno=Gno,@Yno=Yno,@Amount=Amount from inserted select @Yprice=Yprice from Yproject where Yno=@Yno update guest set grade=grade+@Yprice*@Amount/10,balance=balance@Yprice*@Amount where Gno=@Gno end （ 5） .當(dāng)插入新的物品賠償信息時，觸發(fā)孤兒 guest 表，從新計算顧客積分和余額： create trigger tri_AmendsMoney on ReturnObject for insert as begin declare @Gno char(20), @Obno char(20), @Dnum int, @Obprice float, @Obmultiple float select @Gno=Gno,@Obno=Obno,@Dnum=Dnum from inserted select @Obprice=Obprice,@Obmultiple=Obmultiple from RoomObjectInformation where Obno=@Obno update guest set balance=balance@Obprice*@Dnum*@Obmultiple where @Gno=Gno end 插入具體數(shù)據(jù) （ 1）顧客表： insert guest values (39。G000139。,39。方華 39。,39。男 39。,39。34082219911202244139。,39。1352738349839。,39。上海市 39。,50,) insert guest values (39。G000239。,39。張星 39。,39。女 39。,39。34082219911202244239。,39。1352738349739。,39。重慶市 39。,50,) insert guest values 各專業(yè)畢業(yè)論文范文盡在道客巴巴下載 各專業(yè)畢業(yè)論文范文 盡在道客巴巴下載 (39。G000339。,39。鄭誠 39。,39。男 39。,39。34082219911202244339。,39。1352738349639。,39。北京市 39。,50,) insert guest values (39。G000439。,39。許佳 39。,39。女 39。,39。34082219911202244439。,39。1352738349539。,39。重慶市 39。,50,) insert guest values (39。G000539。,39。田惠 39。,39。女 39。,39。34082219911202244539。,39。1352738349439。,39。長沙市 39。,50,) insert guest values (39。G000639。,39。陳東 39。,39。男 39。,39。34082219911202244639。,39。1352738349339。,39。天津市 39。,50,) （ 2）房間基本信息表： insert RoomInformation values (39。R00139。,39。標(biāo)準(zhǔn) 139。,1,39。正北 39。) insert RoomInformation values (39。R00239。,39。標(biāo)準(zhǔn) 239。,2,39。正南 39。) insert RoomInformation values (39。R00339。,39。豪華 139。,3,39。正西 39。) insert RoomInformation values (39。R00439。,39。豪華 239。,4,39。正東 39。) insert RoomInformation values (39。R00539。,39。高級 139。,5,39。東北 39。) insert RoomInformation values (39。R00639。,39。高級 239。,6,39。西南 39。) （ 3）房態(tài)表： insert RoomState values (39。R00139。,39。G000139。,39。20205339。,39。20205539。,39。20205139。,39。20205439。,2,39。20205239。,1) insert RoomState values (39。R00239。,39。G000239。,39。20204139。,39。202041139。,39。202032539。,39。20204539。,10,39。20205239。,2) insert RoomState values (39。R00339。,39。G000339。,39。20204839。,39。202041439。,39。20204139。,39。202041039。,6,39。202041539。,3) insert RoomState values (39。R00439。,39。G000439。,39。202041039。,39。202041039。,39。202041539。,39。20204439。,5,39。20204639。,3) insert RoomState values (39。R00539。,39。G000539。,39。202041539。,39。202042339。,39。20204539。,39。202041639。,8,39。20204839。,2) insert RoomState values (39。R00639。,39。G000639。,39。202042039。,39。202043039。,39。202041039。,39。202042239。,10,39。20204539。,1) （ 4）娛樂項目表： insert Yproject values (39。Y000139。,39。溫泉 39。,) insert Yproject values (39。Y000239。,39。唱歌 39。,) insert Yproject values (39。Y000339。,39。桑拿 39。,) 各專業(yè)畢業(yè)論文范文盡在道客巴巴下載 各專業(yè)畢業(yè)論文范文 盡在道客巴巴下載 insert Yproject values (39。Y000439。,39。汗蒸 39。,) insert Yproject values (39。Y000539。,39。游泳 39。,) insert Yproject values (39。Y000639。,39。足療 39。,) （ 5）娛樂消費(fèi)表： insert ConsumeList values (39。G000139。,39。Y000139。,39。20205339。) insert ConsumeList values (39。G000239。,39。Y000239。,39。20204139。) insert ConsumeList values (39。G000339。,39。Y000339。,39。20204839。) insert ConsumeList values (39。G000439。,39。Y000439。,39。202041039。) insert ConsumeList values (39。G000539。,39。Y000539。,39。202041539。) insert ConsumeList values (39。G000639。,39。Y000639。,39。202042039。) （ 6）房間物品信息表： insert RoomObjectInformation values (39。Rb00139。,39。電話機(jī) 39。,) insert RoomObjectInformation values (39。Rb00239。,39。電視 39。,) insert RoomObjectInformation va