x

Are Triggers Execute in parallel?

Is there any other alternative Insted of using 1000+ tigger??

We need to start 1000+ process (Select Query) Parallely,


We are working no MS SQL Server 2008 and a table called tblStory. We have lots of triggers (arround 1000) right now whenever a new story comes, we want to all triggers will fired and do some appropriate entry in tblUser table. but All triggers will fired like sequencially so have any way like 20 or 1000 triggers will execute at same time. so we will save time because 1000 triggers will take arround 1.5 mins. Approximate we have to come lots of stories in a minutes.

Question is How or What the way execute the all triggers at same time when inserting the new story ?


I am working with MS SQL 2008

I have around 1000+ Triggers on a single table,

Do all trigger fire in parallel, when I insert a record in that table?

Because I have written some time log information and based on that I found that all triggers are executed Sequentially

By
Ketan

more ▼

asked Oct 29 '09 at 07:06 AM in Default

lad_ket gravatar image

lad_ket
11 1 1 2

Triggers are executed sequentially, as you found out. Please edit to clarify what your question is.
Oct 29 '09 at 09:16 AM Kristen ♦
You need to explain what the 1000+ triggers are doing (an example or two should do). I think most people here probably cannot begin to understand what they might be doing - me included! Are you sending the Story to different sites, or publishers, or editors, or something else?
Oct 30 '09 at 06:28 AM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Triggers are ru sequentialy as the other pointed out, but you can not choose in which order they are run. Before you could add ";1" or ";2" but that has been deprecated.

more ▼

answered Nov 16 '09 at 04:36 AM

Peso gravatar image

Peso
1.6k 5 6 8

I think you can choose which one executes FIRST and which one LAST, or have I mis-remembered that?
Nov 16 '09 at 01:26 PM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

I agree with Kristen's comment - triggers run sequentially. But I have to ask, 1000+ triggers on a single table ??? What possible purpose is that serving?

more ▼

answered Oct 29 '09 at 09:21 AM

TG gravatar image

TG
1.8k 1 3

I agree. It sounds like the solution using Triggers has possibly been built because an alternative method coud not be found. Might be best to describe what the Triggers do and then maybe we can recommend a better method.
Oct 29 '09 at 10:25 AM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

1000 triggers on a table sounds completely outrageous to me. With all those triggers, if any one of them fails, the INSERT of the story will also fail and all the other actions will be rolled back. That is a lot of room for things to go sideways.

Whatever those triggers are doing is probably better suited to either pre or post INSERT processing within a stored procedure. Or perhaps one trigger that logs a record into a processing table and a scheduled job that monitors the contents of the processing table and then takes the necessary additional actions.

more ▼

answered Nov 18 '09 at 04:26 PM

AjarnMark gravatar image

AjarnMark
100 3 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x114

asked: Oct 29 '09 at 07:06 AM

Seen: 1523 times

Last Updated: Oct 30 '09 at 03:39 AM