Trigger - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger

Author

Topic

sqlnew
Starting Member

2 Posts

Posted - 2003-04-23 : 18:00:43

For my last question we designed a table and put this trigger, but this trigger is not working correctly.
this table has date_of_arrival and date_of_departure.
If the user wants book the same room this trigger should look at two dates. If the new date is between these two dates the room should not be available for the booking.
Should we create other room availability table?

CREATE TRIGGER arrival_date
ON Booking_Details
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE --@Room_Code int,
@arrival_date datetime,
@prevarrival_date datetime,
@prevdeparture_date datetime
Select * into #temp from Booking_details
SELECT --@Room_Code = Room_Code,
@arrival_date=i.Booking_arrivaldate,
@prevarrival_date = c.Booking_arrivaldate,
@prevdeparture_date=c.Booking_DepartureDate
FROM Booking_Details b INNER JOIN inserted i ON b.Booking_Code = i.Booking_code
INNER JOIN deleted c ON c.room_code = i.room_code
IF (@arrival_date between @prevarrival_date and @prevdeparture_date)
BEGIN
RAISERROR ('Room is not available this time', 16, 1)
ROLLBACK TRANSACTION
END

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-23 : 22:18:14

Select * into #temp from Booking_details
why?

FROM Booking_Details b INNER JOIN inserted i ON b.Booking_Code = i.Booking_code
INNER JOIN deleted c ON c.room_code = i.room_code

????????
Joining to inserted and deleted on different fields to set variables?
Either theses are both unique indexes or this won't work.
Also this is a insert/update trigger. For the insert this will never return a record.

If you just want to check if the room is available for the new booking you don't need to check deleted.

somethin like

if exists
(select * from Booking_Details b, inserted i
where i.Booking_arrivaldate <= b.Booking_DepartureDate
and b.Booking_arrivaldate <= i.Booking_DepartureDate
and i.Room_Code = b.Room_Code
and i.PK <> b.PK
)
BEGIN
RAISERROR ('Room is not available this time', 16, 1)
ROLLBACK TRANSACTION
END

and i.PK <> b.PK
is checking against the primary key to see if this is the same booling being updated.

and i.Room_Code = b.Room_Code
Assumes Room_Code is what defines the individual room.

where i.Booking_arrivaldate <= b.Booking_DepartureDate
and b.Booking_arrivaldate <= i.Booking_DepartureDate
checks to see if the room is already booked.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-04-23 : 22:30:36

If you are designing this for a class project (as you said in your previous question) then you might want to question the use of triggers to see if a room is available or not. You should be able to do this using a simple select starement, not by trying to do the insert and raising an error and rolling back the transaction if it fails some conditions. If I was marking your project I would give you pretty low marks for taking this approach.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-04-24 : 02:18:24

darinh, I don't know this to be the case, but would you change your mind if the reason for the trigger was to prevent double-booking of a room in the slight chance that someone else just booked it for those same dates between the time user1 viewed availability and the time user1 chose "book it"?

I agree that viewing availability of a room is a totally separate creature, but this could be a good safety-catch in the booking process.

------------------------------------------------------
The more you know, the more you know you don't know.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-24 : 10:34:39

Uuummmm.

You're tryin to use a sledge hammer to do the work of what a good sound logical data model will accomplish.

You really need to read up on this subject...

For example:

quote:

If the user wants book the same room this trigger should look at two dates. If the new date is between these two dates the room should not be available for the booking.

Would be handled with a good data model and a simple existance check.

Just curious, what school is this?

Brett

8-)

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-04-27 : 17:14:53

quote:

darinh, I don't know this to be the case, but would you change your mind if the reason for the trigger was to prevent double-booking of a room in the slight chance that someone else just booked it for those same dates between the time user1 viewed availability and the time user1 chose "book it"?

I agree that viewing availability of a room is a totally separate creature, but this could be a good safety-catch in the booking process.

------------------------------------------------------
The more you know, the more you know you don't know.

It depends on the front-end. If any user can chuck whatever in the table using EM or something similar then I would agree with you. If (more likely) they had a front-end written in VB or similar then I would do all inserts with a sp which does the appropriate checking. Therefore the trigger would be unnecessary as the sp could raise the error or return an error value to the front-end. If you have a well thought out design and appropriate security then you don't need safety catches like this.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-27 : 21:26:27

Not sure what your requirements are, but I would design my "booking details" table differently. (Along the lines of what Brett said)

I assume only 1 person or group of people can only be in a room per night, so why not just a have this table:

BookingDetails
----------------
RoomID
Date
BookingID

and RoomID/Date is the composite primary key? BookingID would link to a table for this particular booking, with info like who booked it, how they paid, when they booked it, who took it, etc.

Yes, when you book a room for a series of nights, you will have to insert multiple rows into your details table, but doesn't this make sense? You would not need a trigger or anything fancy to maintain data integrity and you would not be able to accidently book two people in the same room for the same night.

(and then you can use the great articles and posts here at SQL team about detecting Gaps in sequences and/or Runs in your data to know when rooms are avialable or booked or a given # of nights in a row).

- Jeff

Edited by - jsmith8858 on 04/27/2003 21:29:51

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources