You could use a Trigger for that, or make it part of the SProc that handles "Checking In".
How will Checking In work? You could have a Bit Field on the RESERVATION table that says "Checked In"? If so the act of changing the IsCheckedIn flag to 1 could trigger setting the corresponding ROOM record's ROOM_STATUS to "dirty"
If there are other means of setting IsCheckedIn then it might be best to have a Trigger on RESERVATION that sets ROOM_STATUS to Dirty on any INSERTS / UPDATES to RESERVATION where IsCheckedIn changes from 0 to 1.
I don't suppose you want to bother about it just now, but what about:
If IsCheckedIn is accidentally set and is then "reversed". The ROOM_STATUS will have been set to Dirty, should that be reversed?
What about if I stay for 3 nights - when I Check In the room status becomes Dirty, what will set that on the next two days so my room gets cleaned?
Missed your earlier post with SProcs. Some thoughts:
CREATE PROCEDURE prc_Add_Guest
, @GUEST_LNAME varchar(15)
, @GUEST_FNAME varchar(15)
, @GUEST_PHONE varchar(10)
, @GUEST_LICENSEPLATE varchar(10)
, @GUEST_LICENSEPLATE_STATE char(6)
, @ErrorNo int = 0 OUTPUT
* prc_Add_Guest adds a person to the guest table
* 08-Dec-2016 KLDR Started
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
-- Any validation code here, if errors found set @ErrNo and GOTO prc_Add_Guest_EXIT
INSERT INTO GUEST
GUEST_ID, GUEST_LNAME, GUEST_FNAME, GUEST_PHONE, GUEST_LICENSEPLATE
@GUEST_ID, @GUEST_LNAME, @GUEST_FNAME, @GUEST_PHONE, @GUEST_LICENSEPLATE
SELECT @ErrorNo = @@ERROR
IF @ErrorNo <> 0 GOTO prc_Add_Guest_ABORT
-- ... more Insert / Updates here if required ...
IF @ErrorNo = 0
Move the comment higher up - so that it is "at the top" when you review the SProc for any reason.
ALWAYS include a column list in your INSERT statement. Otherwise if you add a column to the table your INSERT will fail (because the VALUES list will be one-short). Also, without a column list your INSERT statement is dependent on the ORDER of the columns, its a really bad idea to rely on that anywhere because it might easily change in future.
We use SET XACT_ABORT ON in all our Sprocs, it causes any errors in child SProcs etc. to propagate up to this one and force this SProc to also fail, otherwise it is possible for some errors to go unnoticed. I'd be interested to know if other folk here have strong views on using this (or not using it!)
The other two SET statements just provide a consistent platform. You almost certainly do not want NOCOUNT OFF (silly name, its a double-negative)
It may feel a bit weird, but I suggest you START a continuation line with the delimiting comma, and not END the line with it. There's a thread on here somewhere about the discussion we had and generally speaking it was preferred. A few swings and roundabouts, but I converted relatively recently and I can say that I have found that the Pros FAR outweigh the occasional Con.
I've also put a TRANSACTION around your INSERT. Not actually needed as the INSERT will either succeed or fail, but if you were to add a second Insert / Update (e.g. to set the ROOM_STATUS during Check In) then you would have the possibility of one working and the other failing, and you don't want just part of the code to alter the database, you want either everything to succeed (COMMIT), or nothing (ROLLBACK)
I've added an ErrorNo value to trap any errors. This is returned as a Parameter so you can include an (optional) parameter for that in the EXEC if the "caller" needs to know success/fail, and it is also returned. So the caller can do:
DECLARE @MyErrorParam int
EXEC prc_Add_Guest @GUEST_ID=1234, ..., @ErrorNo = @MyErrorParam OUTPUT
EXEC @MyErrorParam = prc_Add_Guest @GUEST_ID=1234, ...,
We have LOTS of STUFF in our standard template for SProcs. I don't want to weigh you down right now but I would advise that before you build anything particularly significant that you ask what people here / elsewhere use, and form a comprehensive template. We have changed ours, through experience, on a couple of occasions over the last decade and going back and refactoring ALL the existing code to use the new template was a complete PITA ... no Profit from that exercise, just more resilience, which is very hard to put a cash / time value on ...
Same suggestions for prc_Add_Reservation
(I've kept your Sproc names, but I suggest you consider changing them to Noun-Verb as I mentioned earlier)
prc_Delete_Reservation is passing several parameters which are not used in the body of the Sproc. I would get rid of those extra parameters (or use them to validate that the row being deleted matches them all)
You might want to add a test that @RESERVATION_ID exists and return an error number / validation warning message if it does not.
You are passing @GUEST_ID to prc_Add_Guest - how will the operator know a suitable ID number (which is not already in use)? Same for @RESERVATION_ID in prc_Add_Reservation
One option would be to use the IDENTITY parameter on those columns in the CREATE TABLE statement. That will automatically assign next-available-ID-number.
If you do that I have a recommendation. The IDENTITY function takes a "Seed" value, i.e. the first ID number to allocate. Rather than starting all tables at 1 / the same number, start them at different numbers. e.g. GUEST_ID from 1000, RESERVATION_ID from 2000 and so on. Your test data will only have a few rows, so the IDs will never overlap. If you accidentally JOIN the wrong ID there will be no matching data, so you will find out quickly that there is a logic error in your code.