title: Validating UPDATE and INSERT statements against an entire table
body: <p>I'm looking for the best way to go about adding a constraint to a table that is effectively a unique index on the <em>relationship</em> between the record and the rest of the records in that table.</p>
  
  <p>Imagine the following table describing the patrols of various guards (from the previous watchman scenario)</p>
  
  <pre><code>PK  PatrolID Integer
  FK  GuardID  Integer
      Starts   DateTime
      Ends     DateTime
  </code></pre>
  
  <p>We start with a constraint specifying that the start and end times must be logical:</p>
  
  <pre><code>Ends &gt;= Starts
  </code></pre>
  
  <p>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.</p>
  
  <p>I can think of two ways of trying to approach this:</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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?)</p>