SQL Server trigger on insert,delete & update on table

Question

I have a table Product and another table ProductLog.

The log table needs to track two columns in the Product table. Each time there is an insert, update or delete on those columns I need to update the log table.

Do I need to write three separate triggers, or can one trigger handle these operations?

I also need to know the type of operation, for example I will need to know if the entry in the log table was because of insert or delete or update. If any one give me an example that would be great.

Answer 1

You need just one trigger

CREATE TRIGGER [ProductAfter] ON [Product] AFTER INSERT, UPDATE, DELETE

You can determine which DML statement fires the trigger based on number of records in inserted and deleted tables available within trigger body. For INSERT, deleted is empty, for DELETE, inserted is empty, for UPDATE both inserted and deleted are not empty. For example,

IF @@ROWCOUNT = 0 -- exit trigger when zero records affected
BEGIN
   RETURN;
END;
DECLARE @type CHAR(1);-- 'U' for update, 'D' for delete, 'I' for insert
IF EXISTS(SELECT * FROM inserted)
BEGIN
  IF EXISTS(SELECT * FROM deleted)
  BEGIN
     SET @type ='U';
  END
  ELSE
  BEGIN
     SET @type ='I';
  END
END
ELSE
BEGIN
  SET @type = 'D';
END;

Also, take a look on Tracking Data Changes, there is another option for tracking changes without triggers.

Answer 2

or just

DECLARE @type CHAR(1)=
    case when not exists(SELECT * FROM inserted)
        then 'D'
    when exists(SELECT * FROM deleted)
        then 'U'
    else
        'I'
    end