插入、更新、删除的触发器

2021-11-30 00:00:00 sql-server triggers audit

每当在主表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

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

相关文章