CODEWITHSUNDEEP
sqlsql-servertriggers
AWeim
AWeim

Reputation: 269

SQL Insert/Update/Delete Trigger Efficiency

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

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.

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

I think that the "If Not Exists (Select * from Inserted) is killing my performance as more records get added.

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

Upvotes: 2

Views: 3906

Answers (3)

RBarryYoung
RBarryYoung

Reputation: 56735

Well you could make this simple change:

Create TRIGGER [Billing].[Installments_InsertDeleteUpdate_History]
ON [Billing].[Installments]
AFTER INSERT, DELETE, UPDATE
AS BEGIN

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

SET NOCOUNT ON;

-- Insert statements for trigger here

Which is logically more efficient, but whether it's physically more performant is an open question. If it is actually faster, it sure won't be by very much.

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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.

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

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

Upvotes: 1

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

Create one trigger for INSERTs and UPDATEs and a second for DELETEs. Then you don't have to use an IF statement and a slow query to check where to log.

From a design perspective, see if you can eliminate triggers. They're a mess.

Upvotes: 0

Related Questions