sql-server - 根据条件在两个表之间插​​入的函数/存储过程

Question

我已经很抱歉了......我对这整个发布内容很陌生，但我正在尽力......

我有两个表......“客户”和“房间”......一个拥有唯一 CU_ID 的客户住在一个房间里(有一个唯一的房间 ID，对我来说不幸的是，有时会有 2 或三个 CU_ID 停留在同一个房间里……虽然他们通常在同一天入住，所以 CheckInDate 应该是相同的……

当他们登记入住并且 RoomNo 被输入到“Customer”表中时，我希望“Rooms”表中的位字段“Occupied”设置为“TRUE”。那部分我用触发器完成了(见下文)......

诀窍是当他们退房时......如果用户手动将“已占用”-(位)字段标记为该房间 ID 为“假”，那么我想在客户上设置 DepartDate-在 Getdate() 时刻留在该房间的任何客户的表格。

这是我的表、触发器和一些测试数据:

CREATE TABLE [dbo].[Rooms](
[Room_ID] [int] IDENTITY(1,1) NOT NULL,
[Room_No] [nvarchar](50) NULL,
[Occupied] [bit] NULL,
    [CheckInDate] [int] NULL,

CONSTRAINT [PK_Rooms] PRIMARY KEY CLUSTERED 
(
[Room_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Customer](
[CU_ID] [int] IDENTITY(5000,1) NOT NULL,
[CheckInDate] [datetime] NULL,
[RoomNo] [int] NOT NULL,
[Nights_Booked] [int] NULL,
[DepartDate] [datetime] NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
[CU_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Customer]  WITH CHECK ADD  CONSTRAINT [FK_Customer_Rooms] FOREIGN KEY([RoomNo])
REFERENCES [dbo].[Rooms] ([Room_ID])
GO

ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [FK_Customer_Rooms]
GO

-- 2 Tables created including PK/FK Relationship

这是我第一步的触发器......当 Room_ID 用于新的入住时，将位列 Occupied 更新为 True:

Create TRIGGER [dbo].[Occupied] 
ON [dbo].[Customer]
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN
IF TRIGGER_NESTLEVEL() > 1
RETURN
UPDATE Rooms
SET [Occupied] = 'True'
FROM  Rooms r
JOIN Customer cu
ON cu.[RoomNo] = r.[Room_ID]
Join INSERTED INS
ON cu.[RoomNo] = INS.[RoomNo]
END
GO

我将一些测试数据输入到它们中......

SET IDENTITY_INSERT Rooms ON

INSERT INTO Rooms
(Room_ID, Room_No, Occupied) 
SELECT 1, 'A14', 0 UNION ALL
SELECT 2, 'B2', 0 UNION ALL
SELECT 3, 'C3', 0 UNION ALL
SELECT 4, 'D8', 0 UNION ALL
SELECT 5, 'K9', 0 

SET IDENTITY_INSERT Rooms OFF

GO

SET IDENTITY_INSERT Customer ON

INSERT INTO Customer
(CU_ID, CheckInDate, RoomNo, Nights_Booked, DepartDate) 
SELECT 5000, '2013-05-10', 1, 4, NULL UNION ALL
SELECT 5001, '2013-05-10', 1, 4, NULL UNION ALL
SELECT 5002, '2013-05-10', 2, 2, NULL UNION ALL
SELECT 5003, '2013-05-10', 3, 3, NULL UNION ALL
SELECT 5004, '2013-05-11', 4, 4, NULL UNION ALL
SELECT 5005, '2013-05-11', 4, 4, NULL UNION ALL
SELECT 5006, '2013-05-11', 4, 4, NULL

SET IDENTITY_INSERT Customer OFF

-- Test Data entered in rows on 'Rooms' and 'Customer'-Tables

触发器工作正常，它更新所有具有相同 Room_ID 的记录(分别在 Customer 表上的 RoomNo)。

我试图用其他触发器解决我的问题。我让 SQL Server 根据特定客户的入住日期输入出发日期，如果我将那个日期传递给 Room-Table。不幸的是，它仅使用为 Rooms-Table 上的特定 Room_ID 创建的第一个条目更新数据……而且在两个表之间来回传递似乎很尴尬。我想我需要一个存储过程/函数来真正完成它:

• 在插入客户记录时传递 NEWEST CheckInDate 并插入 Room-Table 字段 CheckInDate
• 当 Rooms.Occupied 标记为“False”时，将 Customer.RoomNo = Rooms.Room_ID AND Customer.CheckInDate = Rooms.CheckInDate 的所有 CU_ID 的退房日期设置为 GETDATE() ...

我对第一部分很纠结——如何在插入时传递 CheckInDate，如果值存在则用较新的日期更新它 ...

不知道，又一次......我是新来的:)

提前感谢您的帮助!!!

Answer 1

欢迎来到 StackOverflow。将来，如果您将示例保持简短，这将对我们非常有帮助。例如，在这种情况下，我们所需要的只是每个表中的几列(没有选项/约束)。

您不应该手动更新位。请改用存储过程。

CREATE PROCEDURE [dbo].[usp_CheckoutRoom] (
    @RoomID
)
AS
    UPDATE [dbo].[Rooms]
    SET [Occupied] = 0
    WHERE [Room_ID] = @RoomID

    UPDATE [dbo].[Customer]
    SET [DepartDate] = GETDATE()
    WHERE [RoomNo] = @RoomID
END

我假设您仍在设计您的系统，但如果一个客户 checkout 多个房间会怎样？您需要一个客户/房间交叉引用表。

[dbo].[Customer]
[dbo].[Room]
[dbo].[CustomerRoom]

另请注意，[dbo].[Customer].[RoomNo] 实际上是 [RoomID] 并不明显。