I am working on my project at university - database in MS SQL Server for some sort of travel agency.
I would like to create a trigger to check if I can add another reservation for specific Holiday (i.e. Hawaii 01-08 Sep 2013). I wrote a trigger: 'FOR UPDATE','FOR INSERT', but I have problem with checking availability. When I check No. of places left the inserted/updated reservation is already in DB so I cannot fill last place. Of course I can change the trigger to 'INSTEAD OF UPDATE' but I do not know if it is a good solution. I have more constraints in this trigger so I do not know if I should change them too. Additional constraints I have are:
check if someone has not already booked another holidays in the same time, X-Check dates of all reservations made by specific participant
check if selected transport is available for specific holiday, for each holiday(term) there are specific transports available etc.
Here you can find part of my DB Diagram: http://s11.postimage.org/t7m32amrn/Scheme.jpg
I hope it will help everyone to understand what I mean.
I would be very grateful for any help.