February 9, 2004 at 3:44 am
I have applied a trigger on inserts of a table. The triger might execute for 10 or even more seconds depending upon the data in the table. The trigger also affect other tables as well.
When a record is inserted into the table, the triger fires and starts executing. Now suppose that as soon as the trigger fires, another record is inserted. Logically, another instance of the trigger must fire and start executing.
Now what I want to know is a method that stops other instance to start after the first finishes. In simple words only one instance of the trigger must execute at one time.
A code example will be appreciated alot.
February 9, 2004 at 6:27 am
So how would you determine when it's appropriate to run ?
Keep in mind you are preforming an in-transaction-operation.
What you are stating is that with your trigger, all other I/U/D-operations would not be tolerable ?
Please elaborate the perpose of your 10-seconds action.
Can't a near-time solution fullfill this need ? e.g. launched job
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 11, 2004 at 1:19 am
I seems to me that you need to do deferred processing. This can be done by letting the trigger insert an entry in a 'driver' table that another procedure can run through periodically and do the the necessary. If the actions that need to be done based on the inserted record are critical to be done in line, you will have to explore other alternatives.
Stephen Marais
Integration Architect
Digiata Technologies
www.digiata.com
May 20, 2004 at 2:18 am
You can turn off recursive triggers which is global for a database or include some code at the start of a trigger to terminate the trigger if an instance of the same trigger is already running:
stop after 1st trigger instance:
if trigger_nestlevel(object_ID(trigger_name)) > 0
return
stop after 2nd trigger instance:
if trigger_nestlevel(object_ID(trigger_name)) > 1
return
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply