title: Using Triggers To Enforce Constraints
body: <p>I'm trying to enforce integrity on a table in a way which I do not think a Constraint can...</p>
  
  <pre><code>CREATE TABLE myData
  (
     id             INTEGER IDENTITY(1,1) NOT NULL,
     fk_item_id     INTEGER               NOT NULL,
     valid_from     DATETIME              NOT NULL,
     invlaid_from   DATETIME              NOT NULL
  )
  </code></pre>
  
  <p>The constraint I want to apply is that there should never be any entries for the same "fk_item_id" with overlapping dates.</p>
  
  <p>Note:</p>
  
  <p>invalid_from is the instant immediately after the valid period.<br>
  This means that the following two periods are fine...  </p>
  
  <ul>
  <li>'2008 Jan 01 00:00' -> '2008 Feb 01 00:00'  (All of Jan)  </li>
  <li>'2008 Feb 01 00:00' -> '2008 Mar 01 00:00'  (All of Feb)  </li>
  </ul>
  
  <p>I can check that rule in a trigger.  When the trigger does find an illegal insert/update, however, what is the best way to prevent the "illegal" inserts/updates from happening?  </p>
  
  <p>(If <em>inserted</em> includes two valid records and two invalid records, can I stop just the two invalid records?)  </p>
  
  <p>Cheers,<br>
  Dems.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>In the case I had above, a constraint using a function worked well. But I never worked out why the RAISERROR didn't work in the trigger version. </p>
  
  <p>I thought it was because the trigger is an AFTER trigger, and that I'd need a BEFORE trigger, but that doesn't appear to be an option...</p>