How to prevent date matching in SQL?

Question

I have the following table for a rental table:

hireId int primary key
carId int not null foreign key
onHireDate datetime not null
offHireDate datetime not null

I am trying to program a multi-user system that does not allow to step on and off the period when cars overlap. I need to be able to add hiring in an unordered order. You must also enable hiring editing.

Any way to limit tables or use triggers, etc. to prevent duplication? I use an entity structure, so I would like to insert into the table as usual, and then, if it does not work, it throws some perceptible exception, etc.

Answer 1

Consider this query:

SELECT *
FROM Hire AS H1, Hire AS H2
WHERE H1.carId = H2.carId
AND H1.hireId < H2.hireId 
AND 
   CASE 
   WHEN H1.onHireDate > H2.onHireDate THEN H1.onHireDate 
   ELSE H2.onHireDate END
   <
   CASE 
   WHEN H1.offHireDate > H2.offHireDate THEN H2.offHireDate 
   ELSE H1.offHireDate END

-, ( , .. , ).

SQL Server CHECK, ( INSTEAD OF, ).

---

Fowler:

SELECT *
  FROM Hire AS H1, Hire AS H2
 WHERE H1.carId = H2.carId
       AND H1.hireId < H2.hireId 
       AND H1.onHireDate < H2.offHireDate 
       AND H2.onHireDate < H1.offHireDate;

Answer 2

CREATE TRIGGER tri_check_date_overlap ON your_table
INSTEAD OF INSERT 
AS
BEGIN
    IF @@ROWCOUNT = 0
       RETURN

    -- check for overlaps in table 'INSERTED'
    IF EXISTS(
        SELECT hireId FROM your_table WHERE 
             (INSERTED.onHireDate BETWEEN onHireDate AND offHireDate) OR
             (INSERTED.offHireDate BETWEEN onHireDate AND offHireDate) 
    )
        BEGIN   
           -- exception? or do nothing?
        END
    ELSE
        BEGIN
        END
END
GO

Answer 3

, . .. .Net. , .

, . - , concurrency (. - , ). , - .

. . AddHire(start,end) - , .

AS no overlap. I save the changes (through my repository) and verify that the database timestamp is still the same as at the beginning of the process. Assuming the timestamp is the same as when the object was received, the changes are saved and the database updates the timestamp.

If someone else is trying to save the changes when the aggregate root is processed, I will either commit it first or they will. If I do it first, the timestamps will not match, and the overlap check will be re-run to make sure they have not created an overlap in the elapsed time.