Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Function / Stored Procedure to update field value

Author  Topic 

carstentao
Starting Member

6 Posts

Posted - 2013-05-12 : 16:12:00
Hi,

I'm already sorry ... I am quite new to this whole posting stuff, but I am trying my best ...

I got two tables ... 'Customer' and 'Rooms' ... a single Customer with a UNIQUE CU_ID is staying in a room (with a UNIQUE ROOM_ID, unfortunately for me, sometimes there are 2 or three CU_ID staying in the same Room ... they usually check in at the same day though, so the CheckInDate should be the same ...

When they check in and the RoomNo is entered on the 'Customer'-Table I would like the Bit-Field 'Occupied' in the 'Rooms'-Table to be set to "TRUE". That part I got done with a Trigger (see below) ...

The trick is when they are checking out ... If a User manually marks the "Occupied"-(Bit)Field for that Room-ID as "FALSE", then I would like to set the DepartDate on the Customer-Table for ANY Customer staying in that room at the MOMENT to Getdate().

Here's my tables, Trigger, and some test data:


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



Here's my Trigger for the first step ... updating the bit column Occupied to True when Room_ID is used for new Check-In:


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


I enter some test data into both of them ...


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


The Trigger works fine and it updates all the Records with the same Room_ID (RoomNo respectively on Customer Table).

I tried to solve my problem with other Triggers. And I get SQL Server to enter the Depart-Date based on the Check-In-Date of the specific Customer, if I pass that one on to the Room-Table. Unfortunately it only updates the Data with the 1st Entry made for that specific Room_ID on the Rooms-Table ... and it seems awkwardly much passing back and forth between the two tables. I guess I need a Stored Procedure/Function to actually accomplish that:

- On Insert of Customer Record pass NEWEST CheckInDate and Insert into Room-Table Field CheckInDate
- When Rooms.Occupied is marked as 'False', set the Check-Out-Date for all CU_ID with Customer.RoomNo = Rooms.Room_ID AND Customer.CheckInDate = Rooms.CheckInDate to GETDATE() ...

I struggle with the first part - how to pass the CheckInDate on Insert and if a value is existing update it with the newer date ...

No idea, again ... I'm all new :)

Thanks for any help in advance !!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-13 : 00:36:20
[code]
Create TRIGGER [dbo].[Occupied]
ON [dbo].[Customer]
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN
UPDATE r
SET r.CheckInDate =
FROM Room r
INNER JOIN (SELECT RoomNo,MAX(CheckInDate) AS MaxDate
FROM Customer
GROUP BY RomNo
)c
ON c.RoomNo = r.Room_ID
END
GO


CREATE TRIGGER [CheckOut]
ON Rooms
FOR UPDATE
AS
BEGIN
UPDATE c
SET c.CheckOutDate= GETDATE()
FROM Customer c
INNER JOIN INSERTED i
ON i.Room_ID = c.RoomNo
AND i.CheckInDate = c.CheckInDate

END
[/code]

between i dont see column CheckOutDate in Customer so i guess you may have to add it before you do the above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

carstentao
Starting Member

6 Posts

Posted - 2013-05-14 : 12:43:13
quote:

between i dont see column CheckOutDate in Customer so i guess you may have to add it before you do the above


Thanks a lot for your reply and solution, Visakh ...

CheckOutDate is actually named DepartDate, sorry I wasn't clear on that one ....

I found a different solution in the meantime, by passing on the CU_ID (better than CheckInDate, as there may be multiple Check-In for the same Room on the same day in crazy busy times) and just replacing the Inserted I was using with that CU_ID ...

Your option seems a lot more simple though and I'd definitely give it a try - thanks again:)

Now I got a a different problem with a Stored Procedure that's updating this field automatically all the times ... but I'd post that separate from this post, as this one is awkwardly long already thanks to me :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 00:29:30
you're welcome
Glad that you got it sorted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -