【SQL开源代码栏目提醒】:网学会员鉴于大家对SQL开源代码十分关注,论文会员在此为大家搜集整理了“dbHotel.sql”一文,供大家参考学习
/* Microsoft SQL Server - Scripting */
/* Server: SSHAFIQ */
/* Database: dbHotel */
/* Creation Date 7/15/00 5:12:08 PM */
/****** Object: Trigger dbo.trg_Update_Rooms Script Date: 7/15/00 5:12:08 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.trg_Update_Rooms') and sysstat & 0xf = 8)
drop trigger dbo.trg_Update_Rooms
GO
/****** Object: Trigger dbo.Web_784005824_1 Script Date: 7/15/00 5:12:08 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.Web_784005824_1') and sysstat & 0xf = 8)
drop trigger dbo.Web_784005824_1
GO
/****** Object: Trigger dbo.Web_784005824_2 Script Date: 7/15/00 5:12:08 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.Web_784005824_2') and sysstat & 0xf = 8)
drop trigger dbo.Web_784005824_2
GO
/****** Object: Trigger dbo.Web_784005824_3 Script Date: 7/15/00 5:12:08 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.Web_784005824_3') and sysstat & 0xf = 8)
drop trigger dbo.Web_784005824_3
GO
/****** Object: Stored Procedure dbo.proc_RoomDesc Script Date: 7/15/00 5:12:08 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.proc_RoomDesc') and sysstat & 0xf = 4)
drop procedure dbo.proc_RoomDesc
GO
/****** Object: View dbo.vr_Reservations Script Date: 7/15/00 5:12:08 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.vr_Reservations') and sysstat & 0xf = 2)
drop view dbo.vr_Reservations
GO
/****** Object: Table dbo.tbl_Hotels Script Date: 7/15/00 5:12:08 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.tbl_Hotels') and sysstat & 0xf = 3)
drop table dbo.tbl_Hotels
GO
/****** Object: Table dbo.tbl_PaymentTypes Script Date: 7/15/00 5:12:08 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.tbl_PaymentTypes') and sysstat & 0xf = 3)
drop table dbo.tbl_PaymentTypes
GO
/****** Object: Table dbo.tbl_Reservations Script Date: 7/15/00 5:12:08 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.tbl_Reservations') and sysstat & 0xf = 3)
drop table dbo.tbl_Reservations
GO
/****** Object: Table dbo.tbl_Rooms Script Date: 7/15/00 5:12:08 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.tbl_Rooms') and sysstat & 0xf = 3)
drop table dbo.tbl_Rooms
GO
/****** Object: Table dbo.tbl_RoomTypes Script Date: 7/15/00 5:12:08 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.tbl_RoomTypes') and sysstat & 0xf = 3)
drop table dbo.tbl_RoomTypes
GO
/****** Object: Table dbo.tbl_Hotels Script Date: 7/15/00 5:12:08 PM ******/
CREATE TABLE dbo.tbl_Hotels (
Hotel int NOT NULL ,
Name varchar (50) NULL
)
GO
/****** Object: Table dbo.tbl_PaymentTypes Script Date: 7/15/00 5:12:08 PM ******/
CREATE TABLE dbo.tbl_PaymentTypes (
Payment int NOT NULL ,
Description varchar (50) NULL
)
GO
/****** Object: Table dbo.tbl_Reservations Script Date: 7/15/00 5:12:08 PM ******/
CREATE TABLE dbo.tbl_Reservations (
ResNo int NOT NULL ,
LastName varchar (25) NULL ,
FirstName varchar (20) NULL ,
Address varchar (50) NULL ,
City varchar (30) NULL ,
State varchar (2) NULL ,
Postal varchar (10) NULL ,
Phone varchar (15) NULL ,
Payment int NULL ,
Amount money NULL ,
Hotel int NOT NULL ,
Room smallint NULL ,
DateIn datetime NULL ,
DateOut datetime NULL ,
DateNow datetime NULL
)
GO
/****** Object: Table dbo.tbl_Rooms Script Date: 7/15/00 5:12:08 PM ******/
CREATE TABLE dbo.tbl_Rooms (
Hotel int NOT NULL ,
Room smallint NOT NULL ,
RoomType int NULL ,
Price money NULL ,
Comments text NULL ,
RoomStatus varchar (1) NULL
)
GO
/****** Object: Table dbo.tbl_RoomTypes Script Date: 7/15/00 5:12:08 PM ******/
CREATE TABLE dbo.tbl_RoomTypes (
RoomType int NOT NULL ,
Beds smallint NULL ,
Description varchar (254) NULL
)
GO
/****** Object: View dbo.vr_Reservations Script Date: 7/15/00 5:12:08 PM ******/
/****** Object: View dbo.vr_Reservations Script Date: 1/20/97 2:28:56 AM ******/
CREATE VIEW vr_Reservations
(ResNo, LastName, FirstName, PaymentDesc, Room, Beds, RoomDesc)
AS
SELECT A.ResNo, A.LastName, A.FirstName
, B.Description
, C.Room
, D.Beds, D.Description
FROM tbl_Reservations A, tbl_PaymentTypes B
, tbl_Rooms C, tbl_RoomTypes D
WHERE B.Payment = A.Payment
AND C.Room = A.Room
AND C.Hotel = A.Hotel
AND D.RoomType = C.RoomTyp