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.
| Author |
Topic |
|
johnnyguy
Starting Member
2 Posts |
 Posted - 2004-04-14 : 09:51:23
|
| hi i am creating a t sql database for a holiday company. Each holiday has a number of places available.These places are reduced as bookings are made in the booking table.We need a trigger in the booking table to check if the number of places have reached their maximum and then display a message and stop the booking from being made.i have inlcluded the tables with the relationship being many bookings to 1 holiday.tblHolidayHoliday_IDHotel_ID*Event_ID*instructor_ID*Dsecription_Of_HolidayLocationStart_DateEnd_dateMinimum_No_Of_BookingsNo_Of_Places_AvailableNnumber_of_BookingspricecancelledtblBookingCustomer_ID*Holiday_ID*Booking_date ThanksCJ |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-14 : 09:58:33
|
| You need to have this logic in your "make booking" procedure, not a trigger. A trigger should be avoided unless absolutely necessary, or for archiving. This is not a case where it's necessary.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
johnnyguy
Starting Member
2 Posts |
Posted - 2004-04-14 : 10:06:45
|
| hi thanks for that, when u mean make booking procedure do u mean a check function? The problem is there are 2 tables tblBooking and tblHoliday involved.Do u mean we place a check in the tblBooking or tblHoliday?ThanksCJ |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-14 : 12:26:30
|
| In your procedure to insert into booking:INSERT tblBooking( Customer_ID, Holiday_ID, Booking_date) SELECT c.Customer_ID, h.Holiday_ID, b.Booking_dateFROM tblBooking b INNER JOIN tblCustomer c ON b.Customer_ID = c.Customer_ID INNER JOIN tblHoliday h ON b.Holiday_ID = b.Holiday_IDWHERE b.Booking_date = @booking_date AND c.Customer_ID = @customer_id AND h.Holiday_ID = @holiday_id AND (COUNT(b.HolidayID)<h.No_Of_Places_Available AND b.Booking_date BETWEEN h.Start_Date AND h.End_date)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
trigger