SQL插入/更新/删除触发器效率 [英] SQL Insert/Update/Delete Trigger Efficiency

问题描述

在数据库级别的应用程序中，我有一个名为Billing and Billing_History模式的表。

In our application at the database level, I have a table called Installments in schema Billing and Billing_History.

所示的触发器在Billing模式的Installments表上。

The trigger shown is on the Installments table in the Billing Schema.

这是每次在记帐模式中插入/更新记录时也将其写入历史记录文件。

What this does is everytime a record is inserted/updated in the billing schema it is also written into the history file.

如果从记帐表中删除该记录，则会使用已删除指示符= true将其写入历史记录表。

If the record is deleted from the billing table it is written to the history table with a "Deleted" indicator = true.

我认为如果

写此触发器是否更有效率？

Is there a more effecient was to write this trigger?

Create TRIGGER [Billing].[Installments_InsertDeleteUpdate_History]
ON [Billing].[Installments]
AFTER INSERT, DELETE, UPDATE
AS BEGIN
Insert Into Billing_History.Installments
    Select *, GetDate(), 0 From Inserted

If Not Exists (Select * From Inserted)
    Insert Into Billing_History.Installments
        Select *, GetDate(), 1 From Deleted

SET NOCOUNT ON;

-- Insert statements for trigger here

END

推荐答案

鉴于建议的任务，我建议您使用触发器形式的效果最好。确实没有更好的方法来获得相同的审核结果。

I would suggest that the trigger form you have is the best performing, given it's required tasks. There really aren't much better ways to achieve the same auditing result.

这里的答案会同意在SQL Server中创建审核触发器，这是关于审核解决方案性能的长期讨论。

The answer here would agree Creating audit triggers in SQL Server and here's a long discussion about performance of audit solutions.

您的情况略有不同，因为您实际上不希望在UPDATE情况下删除的（原始）表，因此是IF。

Your situation is slightly different, because you actually DON'T want the deleted (original) table in UPDATE situations, hence the IF.

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