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

Question

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.

Answer 1

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.