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;
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][1], there is another option for tracking changes without triggers.
[1]: http://msdn.microsoft.com/en-us/library/bb933994.aspx