title: SQL trigger for audit table getting out of sync
body: <p>I recently created a SQL trigger to replace a very expensive query I used to run to reduce the amount of updates my database does each day.</p>
  
  <p>Before I preform an update I check to see how many updates have already occurred for the day, this used to be done by querying:</p>
  
  <pre><code>SELECT COUNT(*) FROM Movies WHERE DateAdded = Date.Now
  </code></pre>
  
  <p>Well my database has over 1 million records and this query is run about 1-2k a minute so you can see why I wanted to take a new approach for this.</p>
  
  <p>So I created an audit table and setup a SQL Trigger to update this table when any INSERT or UPDATE happens on the Movie table. However I'm noticing the audit table is getting out of sync by a few hundred everyday (the audit table count is higher than the actual updates in the movie table). As this does not pose a huge issue I'm just curious what could be causing this or how to go about debugging it?</p>
  
  <p>SQL Trigger:</p>
  
  <pre><code>ALTER TRIGGER [dbo].[trg_Audit]
  ON [dbo].[Movies]
  AFTER UPDATE, INSERT
  AS
  BEGIN
      UPDATE Audit SET [count] = [count] + 1 WHERE [date] = CONVERT (date, GETDATE())
      IF @@ROWCOUNT=0
      INSERT INTO audit ([date], [count]) VALUES (GETDATE(), 1)
  END
  </code></pre>
  
  <p>The above trigger only happens after an UPDATE or INSERT on the Movie table and tries to update the count + 1 in the Audit table and if it doesn't exists (IF @@ROWCOUNT=0) it then creates it. Any help would be much appreciated! Thanks.</p>