title: Comparing fields in a trigger
body: <p>So I've created a trigger that compares update before and after and determines if specific fields, specified in the where clause, have changed.  If so, I insert a snapshot of the prior information into a history table.</p>
  
  <p>The problem is when the field is created with null values (given the business rules, it is legitimately null) and when an update is made, it is unable to evaluate that the string is not equal to the null value.  I want to capture that it was sent to us empty and later filled in.</p>
  
  <p>What approach should I use to compare to null fields without impacting performance?</p>
  
  <p><br>
  This is for SQL Server 2005</p>
  
  <pre><code>CREATE TRIGGER [prj].[TRG_Master_Projection_Upd_History] ON [prj].[Master_Projections]
  AFTER UPDATE
  AS
      SET NOCOUNT ON -- Prevents the error that gets thrown after inserting multiple records 
                     -- if multiple records are being updated
  
  BEGIN
  
  
  INSERT INTO prj.History_Projections (ProjectionID, Cancelled, Appeal, Description, Response_PatternID, 
                                       Proj_Mail_date, [3602_Mail_Date], Proj_Qty, [3602_Qty], Proj_Resp_Rate,
                                       Bounce_Back, Nickels, Kits, Oversized_RE, ChangeComments, 
                                       Modification_Process, Modification_Date, Modification_User)
      SELECT D.ProjectionID, D.Cancelled, D.Appeal, D.Description, D.Response_PatternID, D.Proj_Mail_Date,
              D.[3602_Mail_Date], D.Proj_Qty, D.[3602_Qty], D.Proj_Resp_Rate, D.Bounce_Back, D.Nickels, D.Kits,
              D.Oversized_RE, D.ChangeComments, D.Modification_Process, D.Modification_Date, D.Modification_User
         FROM deleted as D
      JOIN inserted as I
           ON D.ProjectionID = I.ProjectionID
        WHERE (I.Cancelled &lt;&gt; D.Cancelled
            OR I.Appeal &lt;&gt; D.Appeal
            OR I.Description &lt;&gt; D.Description
            OR I.Response_PatternID &lt;&gt; D.Response_PatternID
            OR I.Proj_Mail_Date &lt;&gt; D.Proj_Mail_Date
            OR I.[3602_Mail_Date] &lt;&gt; D.[3602_Mail_Date]
            OR I.Proj_Qty &lt;&gt; D.Proj_Qty
            OR I.[3602_Qty] &lt;&gt; D.[3602_Qty]
            OR I.Proj_Resp_Rate &lt;&gt; D.Proj_Resp_Rate
            OR I.Bounce_Back &lt;&gt; D.Bounce_Back
            OR I.Nickels &lt;&gt; D.Nickels
            OR I.Kits &lt;&gt; D.Kits
            OR I.Oversized_RE &lt;&gt; D.Oversized_RE )
  END;
  SET NOCOUNT OFF;
  </code></pre>