问题描述
每当在主表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 - 从删除中获取未插入的行.
- 更新 - 获取插入和删除的行
这篇关于插入、更新、删除的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！