SQL Server 在插入、删除和删除时触发在桌子上更新 [英] SQL Server trigger on insert,delete & update on table

问题描述

我有一个表 Product 和另一个表 ProductLog.

I have a table Product and another table ProductLog.

日志表需要跟踪Product 表中的两列.每次对这些列进行插入、更新或删除时，我都需要更新日志表.

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.

推荐答案

你只需要一个触发器

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

您可以根据触发器正文中可用的 inserted 和 deleted 表中的记录数确定哪个 DML 语句触发触发器.对于INSERT，deleted为空，对于DELETE，inserted为空，对于UPDATE> inserted 和 deleted 都不为空.例如，

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.

这篇关于SQL Server 在插入、删除和删除时触发在桌子上更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！