Hi and thanks again for your input ...
I think you are making this way too complicated by having denormalized information. The relationship between Customer and rooms like you are doing should use at a minimum three tables.
Customer
Rooms
Reservations
I agree with you and this is what we had ... three different Tables with PK / FK Relationships.
Our business works slightly different ... it is not reservations that are made, people arrive and get a room (so every room has 1-3 Customers at a time) for a few days and we keep a Record of it (so far on the third table) ... when they check out the room is free and can be given to anyone else who arrives that day.
Each Customer can only stay in 1 Room at a time. He might decide to move to a different room whilst his stay, but then the Record would just have to be changed.
The problem with posting sometimes really is that we are not working in the same environment / business logic :).
And I can tell you, the ones entering data via asp-Forms in this case are Retards ... and make too many mistakes
So the Application-Side of things got changed that way that all of the Form Fields from the Bookings-Form got moved to the Customer-Form. Too many missing and double Records in the Bookings and no Overview which Customer is actually staying in which room as the reason behind it ...
I created all the Columns and moved the existing data from the Accommodation-Bookings to the Customer Table.
Now I got left with this Requirement to autoupdate fields between the two remaining Tables 'Customer' and 'Rooms'.
I found a solution that is working on Database-side using Triggers actually ...
I created a CheckInDate and CU_ID Column on the Rooms Table to have Comparison-Fields between the two tables. Then I created Insert-Triggers to Set the Occupied-Column on Rooms to 'True' when a Customer checks in, passing on the values of CU_ID and CheckInDate based on the latest entry. On Check-Out the user only has to untick the Box for Occupied (aka set the Field Value to 'False' and the Customers in that Room with that Check-In-Date get an Input for their DepartDate as getdate () (which will be needed for Invoices etc.) - so far, so good ... so I am a step further but still have a problem.
Here an example of the Insert-Trigger that works:
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
The Business Side of things though is that we update the Customer Records quite a lot - even when they checked out already. My Update-Trigger works fine in the DB:
ALTER trigger [dbo].[UpdateOccupied]
on [dbo].[Customer]
for update
As
Begin
if update (RoomNo)
Begin
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
END
it's linked only to changes in the RoomNo Column, so if a user changes any other column it doesn't fire anymore ...
which is what we need 🙂 ... otherwise someone who already checked out would suddenly be marked as the current Customer in that Room
We need to have this Update-Trigger though, seeing that Customers change their room sometimes throughout their stay, otherwise we would have to recreate the whole Customer-Record.
Again - it works fine when tested on Database-Level
The Problem now is that the Data-Update is done via a .asp-Application which uses a Stored Procedure to update Records when a User hits the Save-Button, and this Stored Procedure passes all Values as Updated, so the Trigger fires, even when the Value of RoomNo hasn't changed ...
Here's the code of the Stored Procedure (I took some fields out for readability):
CREATE PROCEDURE [dbo].[pALPHACustomerUpdate]
@pk_CU_ID int,
@p_Resort int,
@p_RoomNo int,
@p_DepartDate datetime,
@p_Paid_Accomm bit,
@p_prevConValue nvarchar(4000),
@p_force_update char(1)
AS
DECLARE
@l_newValue nvarchar(4000),
@return_status int,
@l_rowcount int
BEGIN
-- Check whether the record still exists before doing update
IF NOT EXISTS (SELECT * FROM [dbo].[Customer] WHERE [CU_ID] = @pk_CU_ID)
RAISERROR ('Concurrency Error: The record has been deleted by another user. Table [dbo].[Customer]', 16, 1)
-- If user wants to force update to happen even if
-- the record has been modified by a concurrent user,
-- then we do this.
IF (@p_force_update = 'Y')
BEGIN
-- Update the record with the passed parameters
UPDATE [dbo].[Customer]
SET
[Resort] = @p_Resort,
[RoomNo] = @p_RoomNo,
[DepartDate] = @p_DepartDate,
[Paid_Accomm] = @p_Paid_Accomm
WHERE [CU_ID] = @pk_CU_ID
-- Make sure only one record is affected
SET @l_rowcount = @@ROWCOUNT
IF @l_rowcount = 0
RAISERROR ('The record cannot be updated.', 16, 1)
IF @l_rowcount > 1
RAISERROR ('duplicate object instances.', 16, 1)
END
ELSE
BEGIN
-- Get the checksum value for the record
-- and put an update lock on the record to
-- ensure transactional integrity. The lock
-- will be release when the transaction is
-- later committed or rolled back.
Select @l_newValue = CAST(BINARY_CHECKSUM([CU_ID],[Resort],[RoomNo],[DepartDate],[Paid_Accomm]) AS nvarchar(4000))
FROM [dbo].[Customer] with (rowlock, holdlock)
WHERE [CU_ID] = @pk_CU_ID
-- Check concurrency by comparing the checksum values
IF (@p_prevConValue = @l_newValue)
SET @return_status = 0 -- pass
ElSE
SET @return_status = 1 -- fail
-- Concurrency check passed. Go ahead and
-- update the record
IF (@return_status = 0)
BEGIN
UPDATE [dbo].[Customer]
SET
[Resort] = @p_Resort,
[RoomNo] = @p_RoomNo,
[DepartDate] = @p_DepartDate,
[Paid_Accomm] = @p_Paid_Accomm
WHERE [CU_ID] = @pk_CU_ID
SET @l_rowcount = @@ROWCOUNT
IF @l_rowcount = 0
RAISERROR ('The record cannot be updated.', 16, 1)
IF @l_rowcount > 1
RAISERROR ('duplicate object instances.', 16, 1)
END
ELSE
-- Concurrency check failed. Inform the user by raising the error
RAISERROR ('Concurrency Error: The record has been updated by another user. Table [dbo].[Customer]', 16, 1)
END
END
GO
So this updates all my Field Values, even if the data hasn't changed, which then causes my Trigger to fire ...
I tried to change the behavior of that SP by adding either ISNULL or COALESCE in the SET Statement, but the Outcome is the same ...
[RoomNo] = COALESCE(@p_RoomNo,RoomNo),
or:
[RoomNo] = ISNULL(@p_RoomNo,RoomNo),
I don't know what I am doing wrong here ... but it's annoying and any help appreciated 🙂