Help with trigger to prevent double booking

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

General SQL Server Forums

New to SQL Server Programming

Help with trigger to prevent double booking

Author

Topic

bxw689
Starting Member

4 Posts

Posted - 2008-12-13 : 22:36:18

I am trying to use the trigger below to prevent the double of appointments in a physician database. When I install the trigger, it rolls back all appointments, not just those that meet the give criteria. I don't see where the problem is. Can anyone help me figute this out. Thanks!

CREATE TRIGGER trg_physician_double_booking
on appointments
for insert
as
begin

declare @physician_id int, @appointment_date date, @start_time time, @end_time time

select @physician_id = (select physician_id from inserted)

select @appointment_date = (select appointment_date from inserted)

select @start_time = (select start_time time from inserted)

select @end_time = (select end_time time from inserted)

if exists (select 1 from appointments where physician_id = @physician_id and appointment_date = @appointment_date and end_time > @start_time)

begin

rollback transaction

print 'Duplicate Appointment'

end

end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 23:36:46

it should be an instead of trigger


CREATE TRIGGER trg_physician_double_booking
on appointments
instead of insert
as
begin

if not exists (select 1 from appointments a
inner join inserted i
on i.physician_id = a.physician_id 
and i.appointment_date = a.appointment_date 
and i.end_time > i.start_time)

begin
insert into appointments
SELECT fields...
FROM inserted
end
else
begin
rollback transaction

print 'Duplicate Appointment'
end
end

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-12-14 : 02:17:48

You need a unique constraint not a trigger.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources