April 1, 2011 at 9:09 am
I want to create an after update trigger, which has two actions to perform. The second action should be executed after 2 minutes from the first action. Basically I use WAITFOR DELAY to do this, but the problem is that when I do some update, the trigger is fired and locks that particular table, so the update is done after the WAITFOR DELAY. In the meantime I can not perform anything in that table like SELECT.
Anyone knows how to do this?
Thank you!
April 1, 2011 at 9:33 am
You would be much better off moving this out of the trigger.
As in .....
Have your trigger insert the PK values for the updated records into another table along with a time.
Have a job set to run periodically that checks that table to perform your other actions.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 1, 2011 at 9:49 am
The trigger is part of the update. The update is not complete, and locks cannot be released, until the trigger completes. A Waitfor is a horrible idea in the trigger.
Jason's idea makes the most sense. It won't be exactly two minutes, but if you have a job running every minute, you can pick up the marker in another table that identifies the actions that need to take place.
April 1, 2011 at 10:24 am
Service Broker is another alternative as well for this application. You may want to read up on it in Books Online.
April 1, 2011 at 11:15 am
Great call in Service Broker. It could work, but service broker still tries to activate immediately when you read the message. I think even with SB, you'll want to have it drop stuff into a table and use a job to implement the delay.
April 1, 2011 at 11:47 am
Not really.
In the message sent to SB you include the datetime it is sent. The reader in SB uses the difference between now (when it reads the message) and that value to set a delay (waitfor) before executing the required task. You can have multiple readers on the queue so that it does not become the impediment to processing.
I realize that this is a simplistic view, but I can't spend time until tonight to think through the details more.
April 4, 2011 at 9:24 am
You could do that, but I think you'd have to architect the readers carefully and you might have a scale issue. I'm not sure that the complexity of setting up, monitoring, and managing the correct number of readers is simpler than just dropping data in a table, polling it, and executing the task when you are 2 minutes out.
April 4, 2011 at 9:26 am
The other thing I thought about here is what is the second process? What are you doing after 2 minutes. The type of process might dictate what I'd recommend.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply