SQL Server trigger on insert,delete & update on table

I have a table Product and another table ProductLog.

我有一个表Product和另一个表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.

日志表需要跟踪Product表中的两列。每次在这些列上插入，更新或删除时，我都需要更新日志表。

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.

我还需要知道操作的类型，例如，我需要知道日志表中的条目是否是因为插入或删除或更新。如果有人给我一个很好的例子。

2 个解决方案

#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,

您可以根据触发器主体中可用的已插入和已删除表中的记录数来确定触发触发器的DML语句。对于INSERT，删除为空，对于DELETE，inserted为空，对于UPDATE，插入和删除都不为空。例如，

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.

此外，看看跟踪数据更改，还有另一个选项，用于跟踪没有触发器的更改。

#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

#1