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]GOCREATE 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]GOALTER TABLE [dbo].[Customer] WITH CHECK ADD CONSTRAINT [FK_Customer_Rooms] FOREIGN KEY([RoomNo])REFERENCES [dbo].[Rooms] ([Room_ID])GOALTER 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 INSERTNOT FOR REPLICATIONASBEGIN 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]ENDGO
I enter some test data into both of them ...SET IDENTITY_INSERT Rooms ONINSERT INTO Rooms(Room_ID, Room_No, Occupied) SELECT 1, 'A14', 0 UNION ALLSELECT 2, 'B2', 0 UNION ALLSELECT 3, 'C3', 0 UNION ALLSELECT 4, 'D8', 0 UNION ALLSELECT 5, 'K9', 0 SET IDENTITY_INSERT Rooms OFFGOSET IDENTITY_INSERT Customer ONINSERT INTO Customer(CU_ID, CheckInDate, RoomNo, Nights_Booked, DepartDate) SELECT 5000, '2013-05-10', 1, 4, NULL UNION ALLSELECT 5001, '2013-05-10', 1, 4, NULL UNION ALLSELECT 5002, '2013-05-10', 2, 2, NULL UNION ALLSELECT 5003, '2013-05-10', 3, 3, NULL UNION ALLSELECT 5004, '2013-05-11', 4, 4, NULL UNION ALLSELECT 5005, '2013-05-11', 4, 4, NULL UNION ALLSELECT 5006, '2013-05-11', 4, 4, NULLSET 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 !!!