[SOLVED] Update trigger that only stores information if data is actually updated?

Update trigger that only stores information if data is actually updated?

CREATE TRIGGER AuditTrigger2 
ON authors
AFTER UPDATE
AS
    INSERT INTO audit (trackingUser, date_time)
    VALUES (SYSTEM_USER, getdate())
GO

What do I need to add to my update trigger for it to exclude update attempts that don't actually update anything?

Also my audit table has a column for transaction type but I am unsure of how to get the transaction type from the trigger and insert it into that column.

Solution

INSERT INTO audit (trackingUser, date_time)
  SELECT SYSTEM_USER, getdate()
  FROM Inserted I
  INNER JOIN Deleted D on D.id = I.id /* Use your PK condition */
  WHERE <some condition that compares columns in I & D>

EDIT: Based on your comment you may want:

CREATE TRIGGER AuditTrigger2 ON authors
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  -- Handle Insert
  INSERT INTO audit (trackingUser, date_time, trasactionType)
    SELECT SYSTEM_USER, getdate(), 'inserted'
    FROM Inserted I
    WHERE NOT EXISTS (SELECT 1 FROM Deleted)
    AND <some condition that compares columns in I & D>
  -- Handle Delete
  INSERT INTO audit (trackingUser, date_time, trasactionType)
    SELECT SYSTEM_USER, getdate(), 'deleted'
    FROM Deleted I
    WHERE NOT EXISTS (SELECT 1 FROM Inserted)
    AND <some condition that compares columns in I & D>
  -- Handle Update
  INSERT INTO audit (trackingUser, date_time, trasactionType)
    SELECT SYSTEM_USER, getdate(), 'updated'
    FROM Inserted I
    INNER JOIN Deleted D ON D.id = I.id /* Use your PK condition */
    WHERE <some condition that compares columns in I & D>
END
GO

Note: <some condition that compares fields in I & D> is to exclude attempts which don't update anything, so you would have to compare every column in the table to see if its changed or not. Inserted is a temp table which contains the new values, Deleted is a temp table which contains the old values.