触发插入，更新，删除 [英] Trigger for insert, update, delete

问题描述

每当在主表"Table1"中进行插入，更新或删除操作时，我都希望在审计表中插入行-更改/插入哪一列无关紧要.我还想在插入，更新或删除时添加I，U或D.对于插入和删除，我正在检查插入和删除的表中是否存在行.进行更新的最佳方法是什么?

I want to insert rows into the audit table whenever an insert, update or delete takes place in the master table "Table1" - doesn't matter which column is changed/inserted. I also want to add I, U or D on insert, update or delete. For insert and delete I am checking if rows exist in the inserted and deleted table. What is the best way to approach update.

我用于插入和删除的代码是:

My code for insert and delete is :

CREATE TRIGGER [dbo].[tr_Table1_InsertUpdate_Table1History_Insert]
ON [dbo].[Table1]
FOR INSERT, DELETE, UPDATE

AS
BEGIN
 IF EXISTS(SELECT * FROM Inserted)
 BEGIN
  INSERT INTO Table1History(...., ModificationType)
  SELECT ..., 'I'
  FROM Inserted
 END


 IF EXISTS(SELECT * FROM Deleted)
 BEGIN
  INSERT INTO Table1History(..., ModificationType)
  SELECT ..., 'D'
  FROM Deleted
 END

END
GO

请帮助！

推荐答案

对于更新，该行的原始值将添加到已删除的表中，而该行的新值将添加到已插入的表中.因此，要确定插入，删除和更新，请执行以下操作

For updates, the original values for the row will be added to the deleted table, and the new values for the row will be added to the inserted table. So, to identify inserts, deletes and updates you would do the following

• 插入-从插入中获取未删除的行
• 删除-从删除中获取未插入的行.
• 更新-获取已插入和已删除的行

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