As the title says, I need to check if a start and end date period is within the range of another start and end date period. I don't know if this needs to be done in a trigger or a stored procedure.
For example, if the start and end date values of a row in table
b are not between the start and end date values of a row in table
a, then it should prevent it from insert.
The relevant tables may be described as follows:
--table a a_code pk start_date end_date --table b b_code (pk) a_code (fk) start_date end_date
I saw this trigger example that I tried to apply to my situation,but it didn't work:
CREATE TRIGGER tI_Ranges ON Ranges INSTEAD OF INSERT AS BEGIN IF (SELECT COUNT() FROM Ranges t CROSS APPLY inserted WHERE inserted.rangefrom BETWEEN t.rangefrom AND t.rangeto) = 0 AND (SELECT COUNT() FROM Ranges t CROSS APPLY inserted WHERE inserted.rangeto BETWEEN t.rangefrom AND t.rangeto) = 0 BEGIN INSERT INTO Ranges(rangefrom, rangeto) SELECT inserted.rangefrom, inserted.rangeto FROM inserted END ELSE BEGIN ROLLBACK TRAN END END