title: Trigger: BEFORE INSERT
body: <p>How to check if a <code>DATE</code> being <code>inserted</code> or <code>updated</code> in a <code>table</code> is between <code>two other dates</code> from another table.</p>
  
  <p><strong>Adicional Info:</strong>
  I have <code>2 tables</code>:</p>
  
  <p><code>Activity</code>:
      <ul>
          <li> <code>StartDate</code> date <strong>NOT NULL</strong> </li>
          <li> <code>EndDate</code> date <strong>NULLABLE</strong> </li>
      </ul></p>
  
  <p><code>SubActivity</code>:
      <ul>
          <li> <code>SubActivityDate</code> date <strong>NOT NULL</strong> </li>
      </ul></p>
  
  <blockquote>
    <p>When <code>EndDate IS NOT NULL</code> I check if: <code>StartDate</code> ≤ <code>SubActivityDate</code> ≤ <code>EndDate</code></p>
    
    <p>When <code>EndDate IS NULL</code> I check if: <code>StartDate</code> ≤ <code>SubActivityDate</code></p>
  </blockquote>
  
  <p>I was trying to write a <b>BEFORE INSERT trigger</b> but I figured out that it doesnt exist.</p>
  
  <p>So what I could do?</p>
  
  <ol>
  <li>AFTER INSERT?</li>
  <li>INSTEAD OF INSERT? looks better than 1st solution</li>
  <li>Is it possible just with CHECK Constraints?</li>
  </ol>
  
  <p>How do I solve this problem?</p>
  
  <h2>EDIT</h2>
  
  <p>I just went with the <strong>CHECK constraint</strong> + <strong>function</strong>:</p>
  
  <p><strong>constraint:</strong></p>
  
  <pre><code>ALTER TABLE SubActivity
      ADD CONSTRAINT CK_SubActivity_Date CHECK (dbo.ufnIsSubactivityDateValid(ActivityID, SubActivityDate) = 1);
  </code></pre>
  
  <p><strong>function:</strong></p>
  
  <pre><code>CREATE FUNCTION ufnIsSubactivityDateValid(@ActivityID [int], @SubActivityDate [date])
  RETURNS [bit]
  AS
  BEGIN
      DECLARE @StartDate date, @EndDate date;
  
      SELECT @StartDate = StartDate , @EndDate = EndDate 
      FROM Activity
      WHERE ActivityID = @ActivityID;
  
      IF (@SubActivityDate &lt; @StartDate )
          RETURN 0; -- out of range date
  
      IF (@EndDate IS NULL)
          RETURN 1; -- good date
      ELSE
          IF (@SubActivityDate &gt; @EndDate)
              RETURN 0; -- out of range date
  
      RETURN 1; -- good date
  END
  </code></pre>