title: SQL Trigger - How do I test for the operation?
body: <p>My trigger fires on <code>INSERT, UPDATE and DELETE</code>.  I need to insert from the appropriate in memory table (<code>inserted, deleted</code>) depending upon which operation triggered the trigger.  Since only <code>inserted</code> is populated on <code>INSERT</code> or <code>deleted</code> on <code>DELETE</code> I figure I can just do an insert and if there's no rows and I'm good.</p>
  
  <p>But, <code>UPDATE</code> populates both tables.  I only want the values from <code>deleted</code> on <code>UPDATE</code>.  I tried testing for update using <code>UPDATE(column)</code> function, but this returns <code>TRUE</code> even on <code>INSERT</code>.  So, how can I test for <code>UPDATE</code>?</p>
  
  <pre><code>ALTER TRIGGER CREATE_tableAudit
     ON dbo.table
     FOR INSERT, UPDATE, DELETE
  AS 
  BEGIN          
      IF(UPDATE([column1]))--returns true on INSERT :(
          BEGIN
              INSERT INTO [dbo].[tableAudit]
                 ([column1]
                 ,[CreateDate]
                 ,[UpdateDate])
                 SELECT * from deleted --update
          END
      ELSE
          BEGIN
          --only inserted is populated on INSERT, visa-versa with DELETE
          INSERT INTO [dbo].[tableAudit]
                 ([column1]
                 ,[CreateDate]
                 ,[UpdateDate])
                 select * from inserted --insert
  
  
          INSERT INTO [dbo].[tableAudit]
                 ([column1]
                 ,[CreateDate]
                 ,[UpdateDate])
                 select * from deleted --delete
  
          END
  </code></pre>