Update Values on one table based on another table - TRIGGER ???

  • Hi,

    I'm already sorry (especially if Sean is answering again) ... 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

    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 !!!

    P.S.: Sean ... still working on that Constraint 😉

  • carsten.crystaldive (5/12/2013)


    Hi,

    I'm already sorry (especially if Sean is answering again) ... I am quite new to this whole posting stuff, but I am trying my best ...

    LOL no worries. You did a fine job posting pretty much everything needed here.

    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

    The way you are trying to do this would mark a Room as Occupied if a reservation is made for a future date. I think that what you are doing in this issue (and the last one I helped you with) is using logic to try to capture the current status of things. What you should do is capture the data, then you can easily determine the status when you need it.

    In other words you have a customer. That data is independent of anything else. They can have any number of reservations. You also have rooms. This does not have a "status" and it is also independent of anything else.

    Now of course what you are trying to do is reserve these rooms. So a customer wants a room (since I don't know your business I am assuming hotel). Reserving this is no problem. You find out the requirements of the room from the customer (NumBeds, ArrivalDate, DepartueDate, etc...). Now all you have to do is look at the Rooms table, join it to the Reservations table to make sure you have a room that will work. Assuming you find a room you create a new row in Reservations with the CustomerID and the RoomNum along with the dates of the reservation.

    You now have a very clear view of all rooms at any given point in time. If they are reserved/occupied or not. I can assure that if you try to maintain a status on the Room like your trigger you are in for a long battle. It will constantly get out of synch.

    Here is a very basic rough sketch of what I would consider a better approach to maintaining the Occupied state of a room.

    create table MyCustomer

    (

    CustomerID int identity primary key,

    FName varchar(25),

    LName varchar(25)

    )

    create table Reservations

    (

    ReservationID int identity primary key,

    CustomerID int,

    RoomID int,

    CheckInDate datetime,

    DepartDate datetime

    )

    create table MyRooms

    (

    RoomNum int primary key,

    NumBeds int,

    CoffeeMaker bit

    )

    Of course you would need to create foreign keys and such. See if this type of thing would work for you. It would certainly make the need for your trigger to go away. Honestly, triggers are something you really don't need (aside from auditing) all that often if the system is properly normalized.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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 🙂

  • Hey Sean,

    I moved my last one about the Stored Procedure into a New Topic ... you're more than welcome to check it out 🙂 ...

    Is there a way to make this thread being 'Answered' or 'Inactive' or whatever you wanna call it and do you have something like a Voting system where I should vote for all your help so far? Seen this on other Forums I posted on ...

    I don't think it's worth posting my work around to my own question here, since it seems awkwardly complicated anyways with 7 Triggers firing back and forth 🙂 But in General it's good to post solutions for others to find them isn't it ?

    Cheers - 10 Days to the Champions League Final 🙂

  • carsten.crystaldive (5/14/2013)


    Hey Sean,

    I moved my last one about the Stored Procedure into a New Topic ... you're more than welcome to check it out 🙂 ...

    Is there a way to make this thread being 'Answered' or 'Inactive' or whatever you wanna call it and do you have something like a Voting system where I should vote for all your help so far? Seen this on other Forums I posted on ...

    I don't think it's worth posting my work around to my own question here, since it seems awkwardly complicated anyways with 7 Triggers firing back and forth 🙂 But in General it's good to post solutions for others to find them isn't it ?

    Cheers - 10 Days to the Champions League Final 🙂

    There is not an "Answered" status. That is sort of the nature of things around here. These threads will be out there for eternity visible to everyone. The upside is that sometimes someone will think there thread is answered and someone else will come long with a different idea that may just blow the "answer" away.

    Who'd a thunk an all German final??? Should be entertaining.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply