Avoid Stored proc Fire triggers

Question

Avoid Stored proc Fire triggers

fasttrack

SSCertifiable

Points: 6071
More actions
July 4, 2008 at 3:18 am

#381507

Hello guys,
I have a questions:
I have a stored proc that delete lots of records by time in some tables, the problem is that these tables have a "delete" triggers.
We put into the trigger a clause that recognise this procedure with a "RETURN" to have not action by the trigger when the delete is operated by stored proc.
In this case we reached aou golas to avoid that stored proc delete records and do not make action internally the trigger.
At this point the problem is "Performance": when the store proc starts deleting record, triggers fires (because the clause is inside the triggers) and the application connected with the DB goes in Time-out.
I would like to develop something making sure that when the stored proc starts, it does not fire the triggers.
I also would like to say that triggers must be enabled to make sure that all "delete action" not executed by the stored procedures (eg the application) must fire the trigger and perform the action.
Any suggestions?
Thank

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You could disable the trigger, run the delete and reenable it. There's the risk that another delete runs while the trigger is disabled.

Is there anything about the session/connection properties that identifies the delete as coming from the app? If so, you could put a condition in the trigger so it doesn't do anything if the delete is from the specific application.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vladan SSC-Insane Points: 22072 More actions · Answer 2

I'm sorry - I think there is no solution that would meet all your requirements.

- if trigger is enabled, trigger will fire on any delete; if trigger is disabled, it will never fire

- some deletes should not result in any action -> this has to be checked inside the trigger, no way to check it before trigger fires

Result => you can not require that the trigger does not fire sometimes.

Maybe, if this is a serious problem, you should reconsider the design and choose some other way.

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

I'm surprised that the performance is poor just firing the trigger.

What is the "clause" you have put in the trigger to recognize the stored procedure... is this clause causing the slowdown?

fasttrack SSCertifiable Points: 6071 More actions · Answer 4

records that are to be deleted are around 10*5 records at time, lot of tables to be deleted so the performance decrease.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Can you maybe post the code of the trigger?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Actually, I think that the problem is more likely to be your stored procedure than your trigger.

Are you using a cursor or a While loop to delete these records?

Please show us the stored procedure's code.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Actually.... I want to see both the trigger and the proc... it would also be nice if you'd post the Create statements for the table being deleted from and any indexes it might have. Can't help unless we can see... 😉

--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)