Triggers

Question

Triggers

dastagiri16

Hall of Fame

Points: 3544
More actions
February 24, 2018 at 12:03 am

#329434

Hi Team,
I have created one single trigger to capture information modified by the insert/update/delete commands.
is there any performance difference if we create 3 separate triggers(like after insert ,after update ,after delete) ?
Thanks in Advance.
Dastagiri D

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

dastagiri16 - Saturday, February 24, 2018 12:03 AM
Hi Team,
I have created one single trigger to capture information modified by the insert/update/delete commands.
is there any performance difference if we create 3 separate triggers(like after insert ,after update ,after delete) ?
Thanks in Advance.
Dastagiri D

Maybe... "It Depends" on how you write them. I've not done such a study but SQL Server is pretty fast about deciding what the triggering event was and, since any given event can only be one of the three (Insert, Update, or Delete), it may cost you more to write code in a single trigger to make a decision as to which event occurred than if you had three different dedicated triggers.

The real key is to ask what the/these triggers will be used for. For example, if you're using triggers to populate audit tables, then it's a serious mistake to use the "INSERTED" logical table of any trigger and you certainly should not have a separate "INSERT" trigger because auditing inserts instantly doubles your storage requirements for what will likely become the biggest table in your entire database.

With that in mind, what are you using this/these triggers for?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

No. In fact, generally they are a bit faster since you don't have to decide within the trigger which action -- DELETE / INSERT / UPDATE -- has occurred.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".