SQL Trigger - How do I test for the operation?

My trigger fires on INSERT, UPDATE and DELETE. I need to insert from the appropriate in memory table (inserted, deleted) depending upon which operation triggered the trigger. Since only inserted is populated on INSERT or deleted on DELETE I figure I can just do an insert and if there's no rows and I'm good.

But, UPDATE populates both tables. I only want the values from deleted on UPDATE. I tried testing for update using UPDATE(column) function, but this retur开发者_如何学运维ns TRUE even on INSERT. So, how can I test for UPDATE?

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

To test for UPDATE, look for identical primary key values in both the Inserted and Deleted tables.

/* These rows have been updated */
SELECT i.PKColumn
    FROM inserted i
        INNER JOIN deleted d
            ON i.PKColumn = d.PKColumn

Assuming that the primary keys of rows didn't change, you can find updated rows by joining the Inserted and Deleted tables on the primary key field(s). If joining these two tables produces rows, then you can safely assume that those rows were updated.

If an update does change the primary key of a row, then you're probably better off just treating it as two operations, a delete and an insert.

The following has been useful to in triggers: The * in the queries could be changed to include only fields you want to have compared; could exclude the autonumber if it changes in the source table.

To check for Inserted:

if exists (Select * from inserted) and not exists(Select * from deleted)
begin
   ...
end

To check for Updated:

if exists(SELECT * from inserted) and exists (SELECT * from deleted) 
begin
   ...
end

To check for Deleted:

if exists(select * from deleted) and not exists(Select * from inserted)
begin
   ...
end