question

reamades avatar image
reamades asked

Overlapping dates trigger

Hello all, I need some help with a trigger to prevent overlapping dates. I feel so close but I'm not sure what I'm missing. The details; Rates table holds all day rates for contractors per project. So I need to prevent any date overlap for the same contractor for the same project. Columns: RateID(primary key), AgentID (contractor), ProjectID, StartDate, EndDate Below is the trigger I am trying but prevents any new records from being created; ALTER TRIGGER [dbo].[t_RateOverlap] ON [dbo].[Rates] AFTER INSERT, UPDATE AS IF EXISTS( SELECT * FROM Rates inner join inserted on (Rates.AgentID=inserted.AgentID AND Rates.ProjectID=inserted.ProjectID AND inserted.StartDate BETWEEN Rates.StartDate AND Rates.EndDate) OR (Rates.AgentID=inserted.AgentID AND Rates.ProjectID=inserted.ProjectID AND inserted.EndDate BETWEEN Rates.StartDate AND Rates.EndDate) ) BEGIN RAISERROR ('Error: Rates cannot overlap.', 16, 1) ROLLBACK TRANSACTION END GO If anyone has suggestions they would be greatly appreciated, thanks.
triggerdates
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

reamades avatar image
reamades answered
I actually received an answer from another thread that worked perfectly. Below is the full trigger in case anyone else is looking for a way of checking overlapping dates. ALTER TRIGGER [dbo].[t_RateOverlap] ON [dbo].[Rates] AFTER INSERT, UPDATE AS IF EXISTS( SELECT * FROM Rates INNER JOIN inserted ON inserted.AgentID = Rates.AgentID AND inserted.ProjectID = Rates.ProjectID AND inserted.RateID <> Rates.RateID WHERE Rates.StartDate <= inserted.EndDate AND Rates.EndDate >= inserted.StartDate ) BEGIN RAISERROR ('Error: Rates cannot overlap.', 16, 1) ROLLBACK TRANSACTION END
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Your trigger checks if the rows you just inserted clashes with any rows that you just inserted - and of course they do - the same rows appear in both data sets! Add another predicate that checks that the RateID is different between the 2.... IF EXISTS( SELECT * from Rates inner join inserted on Rates.RateIDinserted.RateID and Rates.AgentID=inserted.AgentID and Rates.ProjectID=inserted.ProjectID and ((inserted.StartDate BETWEEN Rates.StartDate AND Rates.EndDate) or (inserted.EndDate BETWEEN Rates.StartDate AND Rates.EndDate) ) )
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
Simply replace your code with this snippet: update Rates set DayRate = 9000 where AgentId = (select AgentId from Agents where AgentName = 'IainRobertson') Haha, only kidding. Don't do that. I'd question whether a trigger was the right place for this logic. In fact, I'd question whether a trigger was the right place for anything but that's another debate. Presumably, you are updating the table via a stored procedure of some kind. I'd put the logic in the procedure. Triggers are a bit spaghetti-ish, can cause performance problems and tend to be difficult to find.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.