Validating UPDATE and INSERT statements against an entire table

Question

I'm looking for the best way to go about adding a constraint to a table that is effectively a unique index on the relationship between the record and the rest of the records in that table.

Imagine the following table describing the patrols of various guards (from the previous watchman scenario)

PK  PatrolID Integer
FK  GuardID  Integer
    Starts   DateTime
    Ends     DateTime

We start with a constraint specifying that the start and end times must be logical:

Ends >= Starts

However I want to add another logical constraint: A specific guard (GuardID) cannot be in two places at the same time, meaning that for any record the period specified by Start/Ends should not overlap with the period defined for any other patrol by the same guard.

I can think of two ways of trying to approach this:

Create an INSTEAD OF INSERT trigger. This trigger would then use cursors to go through the INSERTED table, checking each record. If any record conflicted with an existing record, an error would be raised. The two problems I have with this approach are: I dislike using cursors in a modern version of SQL Server, and I'm not sure how to go about implimenting the same logic for UPDATEs. There may also be the complexity of records within INSERTED conflicting with each other.

The second, seemingly better, approach would be to create a CONSTRAINT that calls a user defined function, passing the PatrolID, GuardID, Starts and Ends. The function would then do a WHERE EXISTS query checking for any records that overlap the GuardID/Starts/Ends parameters that are not the original PatrolID record. However I'm not sure of what potential side effects this approach might have.

Is the second approach better? Does anyone see any pitfalls, such as when inserting/updating multiple rows at once (here I'm concerned because rows within that group could conflict, meaning the order they are "inserted" makes a difference). Is there a better way of doing this (such as some fancy INDEX trick?)

Answer 1

The simplest way would be to use a stored procedure for the inserts. The stored procedure can do the insert in a single statement:

insert into YourTable
(GuardID, Starts, Ends)
select @GuardID, @Starts, @Ends
where not exists (
    select *
    from YourTable
    where GuardID = @GuardID
    and Starts <= @Ends
    and Ends >= @Start
)

if @@rowcount <> 1
    return -1 -- Failure

In my experience triggers and constraints with UDF's tend to become very complex. They have side effects that can require a lot of debugging to figure out.

Stored procedures just work, and they have the added advantage that you can deny INSERT permissions to clients, giving you fine-grained control over what enters your database.

Answer 2

Use an after trigger to check that the overlap constraint has not been violated:

create trigger Patrol_NoOverlap_AIU on Patrol for insert, update as
    begin
    if exists (select *
     from inserted i
     inner join Patrol p
      on i.GuardId = p.GuardId
      and i.PatrolId <> p.PatrolId
     where (i.Starts between p.starts and p.Ends)
     or (i.Ends between p.Starts and p.Ends))

     rollback transaction
    end

NOTE: Rolling back a transaction within a trigger will terminate the batch. Unlike a normal contraint violation, you will not be able to catch the error.

You may want a different where clause depending on how you define the time range and overlap. For instance if you want to be able to say Guard #1 is at X from 6:00 to 7:00 then Y 7:00 to 8:00 the above would not allow. You would want instead:

create trigger Patrol_NoOverlap_AIU on Patrol for insert, update as
    begin
    if exists (select *
     from inserted i
     inner join Patrol p
      on i.GuardId = p.GuardId
      and i.PatrolId <> p.PatrolId
     where (p.Starts <= i.Starts and i.Starts < p.Ends)
     or (p.Starts <= i.Ends and i.Ends < p.Ends))

     rollback transaction
    end

Where Starts is the time the guarding starts and Ends is the infinitesimal moment after guarding ends.

Answer 3

Can be done with constraints too:

http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/storing-intervals-of-time-with-no-overlaps.aspx