Instead of Insert and Insert Trigger

Question

Instead of Insert and Insert Trigger

APA0876

Hall of Fame

Points: 3853
More actions
February 19, 2013 at 12:49 pm

#274290

Hi,
I have a table, and I have enabled an Instead of Trigger and for Insert Trigger, the question is:
Has Instead of trigger preference over the for insert trigger?
Based on my understanding the instead of will override the for insert trigger.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

In general, I think the preference is to avoid triggers when possible.

after that, it's not a preference, but rather which method, a standard insert trigger vs an instead of trigger addresses the specific issue best.

I've seen lots and lots of unnecessary triggers, so the classic "it depends" answer applies for each situation.

a classic example is where someone wants to update some column in the same table to be a status, based on another column.(ie if the ShippedDate is not null, that the ShippedStatus = "shipped"

something like that can easily be replaced with a view or a calculated column...no trigger really required.

Other situations, where it's an auditing type thing to an audit whodunnit table, a classic Insert/Update/Delete trigger is fine.

For me, an instead of trigger is ideal for an updatable view, that is composed of multiple tables that the view ordinarily would not support updating for.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

APA0876 Hall of Fame Points: 3853 More actions · Answer 2

Thanks, if I have both triggers enabled on the same table are they both going to fire ?

Thanks again

Lowell SSC Guru Points: 323518 More actions · Answer 3

yes. both triggers will fire. two triggers same table, even if one is an instead of trigger.

That might be a logical error, having two insert triggers on the same table;

an instead of trigger does not replace ALL other triggers...it just replaces the logic that would have normally been performed inside the one trigger with alternate workflow or something.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 101118 More actions · Answer 4

The "instead of" trigger will fire first.

If the "instead of" trigger does actually insert a row(s), then the "after[for] trigger" will fire.

You may want to use the clearer "AFTER INSERT" instead of "FOR INSERT" (which is obsolete now really) when creating the other trigger to help you remember the firing order :-).

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".