VB客房管理系统设计 第3页
[Rstates] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Cid] [int] NULL
) ON [PRIMARY]
GO
F 在表GuestInfo中，创建一个触发器：
CREATE TRIGGER insert_tri ON dbo.GuestInfo
FOR INSERT
AS
update RoomInfo
set Rstates='满',Cid=(select Cid from inserted)
where Rnum=(select Rnum from inserted)
功能：当向表GuestInfo中插入一个客户信息时，触发表RoomInfo的某房间的状态变为“满”
(3)建立视图
A 管理员信息视图
CREATE VIEW dbo.ViewAdminInfo
AS
SELECT Aid, Aname, Alimit
FROM dbo.AdminInfo
B 总收入视图
CREATE VIEW dbo.ViewAllCost
AS
SELECT SUM(Ccost) AS AllCost
FROM dbo.GuestInfo
C 标准房收入视图
CREATE VIEW dbo.ViewBiaozhunCost
AS
SELECT SUM(a.Ccost) AS BiaozhunCost
FROM dbo.GuestInfo a INNER JOIN
dbo.RoomInfo b ON a.Rnum = b.Rnum
WHERE (b.Rtype = '标准')
D 收入视图
CREATE VIEW dbo.ViewCost
AS
SELECT dbo.ViewAllCost.AllCost, dbo.ViewPutongCost.putongCost,
dbo.ViewBiaozhunCost.BiaozhunCost, dbo.ViewHaohuaCost.HaohuaCost
FROM dbo.ViewAllCost CROSS JOIN
dbo.ViewPutongCost CROSS JOIN
dbo.ViewBiaozhunCost CROSS JOIN
dbo.ViewHaohuaCost
E 空房信息视图
CREATE VIEW dbo.ViewEmptyRoomInfo
AS
SELECT Rnum, Rtype, Rprice, Rstates
FROM dbo.RoomInfo
WHERE (Rstates = '空')
F 满房信息视图
CREATE VIEW dbo.ViewFullRoomInfo
AS
SELECT dbo.RoomInfo.*
FROM dbo.RoomInfo
WHERE (Rstates = '满')
G 客户信息视图
CREATE VIEW dbo.ViewGuestInfo
AS
SELECT dbo.GuestInfo.*
FROM dbo.GuestInfo
H 豪华房收入视图
CREATE VIEW dbo.ViewHaohuaCost
AS
SELECT SUM(a.Ccost) AS HaohuaCost
FROM dbo.GuestInfo a INNER JOIN
dbo.RoomInfo b ON a.Rnum = b.Rnum
WHERE (b.Rtype = '豪华'
I 普通房收入视图
CREATE VIEW dbo.ViewPutongCost
AS
SELECT SUM(a.Ccost) AS PutongCost
FROM dbo.GuestInfo a INNER JOIN
dbo.RoomInfo b ON a.Rnum = b.Rnum
WHERE (b.Rtype = '普通')
J 房间信息视图
CREATE VIEW dbo.ViewRoomInfo
AS
SELECT dbo.RoomInfo.*
上一页 [1] [2] [3] [4] 下一页
VB客房管理系统设计 第3页下载如图片无法显示或论文不完整，请联系qq752018766
