Triggers per second

Question

Triggers per second

jo-608789

Valued Member

Points: 65
More actions
February 20, 2008 at 9:29 am

#186902

Hi,
is there some limit in SQL Server 2005 on how often a trigger can be fired per second?
If you know some best practice guidelines on SQL Server 2005 triggers, could you please point me to them?
Thx,
jo

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

I don't know if there's a limitation,and I'm almost scared to ask why you're asking. In general, my opinion on triggers is as few as possible, executing as infrequently as possible.

Can you give us more of an idea what you're thinking of doing with the triggers?

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

jo-608789 Valued Member Points: 65 More actions · Answer 2

Hi,

we have two cases:

Case 1: around 200 new records per day are inserted in a table. Each time a trigger shall fire and call a couple of stored procedures, which among others generate some xml fragments.

Case 2: same as case 1, but up to 10000 new records per day.

The question is whether we shall use a trigger or create a job which runs e.g. every 5 minutes. For case 1, defining a trigger is probably the better choice. For case 2, I'm not so sure due to the much higher number of inserts.

What do you think?

Thx,

jo

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

Hi

10000 records is not that high a number.do the inserts occur as a batch. can u be more specific as to what you want to do. Generally calling procedures from triggers is a no-no.

"Keep Trying"

jo-608789 Valued Member Points: 65 More actions · Answer 4

The inserts are distributed over the day. Some xml fragment is created in the procedure. But that's not a big deal.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

Chirag has a very good point are you dealing with each insert on it's own or are you simply doing batch processing?

Some sample code would help a lot.

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

jo-608789 Valued Member Points: 65 More actions · Answer 6

It's not batch processing, the inserts are distributed over the day.

Anipaul SSC-Insane Points: 24681 More actions · Answer 7

There is no limition as such. Did you have any conditions within the triggers? If yes check whether the conditions are correct or not.