June 3, 2008 at 7:03 am
Hi,
Assume there is a after insert trigger on a table named tbl_persons. In that trigger - among all kind of other interesting things - a record is inserted in the table: tbl_triggers. The table tbl_triggers is just created to notify some modification about the content of several tables.
After the record is inserted in tbl_triggers a procedure should be executed, for example: pr_update. For the sake of discussion: the procedure pr_update is not guaranteed to succeed, so there is a change of that procedure crashing while in execution. Please don't tell me that it shouldn't crash. That's not the subject of the discussion.
I've got choices of implementation:
option a)
a trigger on tbl_triggers is not wise since when pr_update crashed the trigger on tbl_persons does not succeed (oops)
option b)
a periodic job on tbl_triggers notices changes and executes pr_update. The minimum job resolution however is a minute, in some cases this is a eternity PLUS a lot of overhead for nothing.
option c)
a one time only job created dynamic upon a trigger from tbl_triggers, I've been experimenting with this, however: either the processor never runs idle-> job will never ecxecute our it will take to long OR the execution time of the job is in the past -> job will not execute
option d)
a periodic job that somehow is recursive and always running (with the delay statement this can be accomplished)
option e)
You tell me.... I'm looking for a mechanism which is activated due to the insertion of a record in tbl_persons which runs indepent at a moment as soon as possible after the insertion of that record. The mechanism should run on SQL 2005.
regards, Paul H.
June 3, 2008 at 8:52 am
Have you thought of setting up a Try...Catch in the trigger so that it runs the unstable process and deals with it gracefully if it does fail? Would that be an option?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2008 at 10:09 am
Paul (6/3/2008)
Hi,Assume there is a after insert trigger on a table named tbl_persons. In that trigger - among all kind of other interesting things - a record is inserted in the table: tbl_triggers. The table tbl_triggers is just created to notify some modification about the content of several tables.
After the record is inserted in tbl_triggers a procedure should be executed, for example: pr_update. For the sake of discussion: the procedure pr_update is not guaranteed to succeed, so there is a change of that procedure crashing while in execution. Please don't tell me that it shouldn't crash. That's not the subject of the discussion.
I've got choices of implementation:
option a)
a trigger on tbl_triggers is not wise since when pr_update crashed the trigger on tbl_persons does not succeed (oops)
option b)
a periodic job on tbl_triggers notices changes and executes pr_update. The minimum job resolution however is a minute, in some cases this is a eternity PLUS a lot of overhead for nothing.
option c)
a one time only job created dynamic upon a trigger from tbl_triggers, I've been experimenting with this, however: either the processor never runs idle-> job will never ecxecute our it will take to long OR the execution time of the job is in the past -> job will not execute
option d)
a periodic job that somehow is recursive and always running (with the delay statement this can be accomplished)
option e)
You tell me.... I'm looking for a mechanism which is activated due to the insertion of a record in tbl_persons which runs indepent at a moment as soon as possible after the insertion of that record. The mechanism should run on SQL 2005.
regards, Paul H.
How high is the transaction rate ? do you expect a lot of inserts on the "source" table ?
* Noel
June 3, 2008 at 11:33 am
What you are sescribing is building your own service broker.
When you send a message to a broker, you can have a procedure configured on the broker that will essentially process messages as they come in. The processing of the messages will be asynchronous from the procedure that sends the message to the broker.
The way this is implemented is similar to what you are doing. A broker queue is a table and records are inserted into it. A constantly running monitoring process picks up the records in the queue (removing them) and processes the records. The queue can be configured to run several processors at the same time if you expect excessive traffic, or one at a time if you think sending messages simultaneously will overwhelm your SMTP server.
It will be a little bit of work for you to learn how to use Service Broker and then configure it. There is no GUI for anything, just some templates in Management Studio. However, it is going to be worth it, it does exactly what you want it to do and will be stable and reliable. Here's a tip - service broker gets disabled automatically when you restore a database, so be sure to turn it back on if you ever restore.
June 4, 2008 at 1:20 am
Hi,
Thanks for the reactions, try catch in the trigger does raise the level of stability but still doesn't seperate the two processes.
The number of insertions is not extremly high. Maybe 10k per day.
I'll dive into writing my own service broker looks promising.
Thanks for the suggestions!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply