Reputation: 11
I have a table called tblReservations
with follwing columns:
reserv_ID (int), aptID (int), client_ID (int),
start_date (datetime), end_date (datetime),
details (nvarchar(max)), confirmation (bit)
What trigger should is to compare two dates start_date
for new reservation and end_date
for existing reservation in tblReservation
for specific aptID
.
If start_date < end_date
trigger must prevent insertion of new reservation for that aptID
.
I wrote this trigger:
CREATE TRIGGER NewReservation
on tblReservations
after insert
as
begin
declare @aptID int
declare @start_date datetime
declare @end_date datetime
select @aptID=aptID, @start_date=start_date from inserted
select @end_date=end_date from tblReservations
where aptID=@aptID
if @end_date>@start_date
BEGIN
ROLLBACK TRANSACTION
END
end
Why does this trigger not work?
please help
Upvotes: 1
Views: 1066
Reputation: 96658
Aside from the multiorw issue that others have brought up, you are likely not considering that there are many rows in the tblReservations for a particular apt id.
Plus you do not want to insert the record and then get rid of it, you want the record to not go in. therefore an instead of trigger is a better bet.
CREATE TRIGGER NewReservation
ON tblReservations
INSTEAD OF INSERT
AS
BEGIN
INSERT tblReservations (<put field list here>)
SELECT <put field list here>
FROM inserted i
JOIN (SELECT aptid, MAX(reservationid)AS reservationid FROM tblReservations GROUP BY aptid) maxid
ON i.aptid = r.aptid
JOIN tblReservations r
ON r.reservationid = maxid.reservationid
WHERE r.enddate<i.startdate
END
Upvotes: 1
Reputation: 2737
The first problem I see is you are assuming inserted is a single row.
Try:
Select top 1 @aptID=bb.aptID, @start_date=start_date
from inserted as aa
join
(
select Max(start_date) as Start_Date from inserted
) as bb
on aa.Start_Date =bb.Start_Date
But you should think about changing your logic so that the record never gets inserted in the first place if the date is wrong.
Upvotes: 0