October 4, 2002 at 3:30 pm
Over time some of my triggers have gotten lenghthy as value is added to the procedures. Some functions need not roll back the transaction or it is not necessary to process synchronously in the trigger. Is it possible to disconnect some of the functions in a trigger, like Emailing or others that I do not want to wait on or stop the process if lengthy or failed?
October 4, 2002 at 3:49 pm
I don´t know if you can do it directly. Why don´t you create other similar triggers with the functions that you want to disable, and then enable or disable the triggers that you need to run.
October 4, 2002 at 4:12 pm
I want all of the trigger code to execute if possible. For example; I send emails when some records are added. I do not want the transaction to roll back just because the Email system was unavailable. I might also update other tables within the trigger that are not critical or worth holding up the original statement. I would like to start another process that lets the original trigger proceed without waiting on less important tasks.
October 4, 2002 at 4:19 pm
I have a couple of processes with similar behavior. Don't know how good my solution is, but it works. I code the trigger to execute a sql server job that does the asynchronous portions of the task, such as processing email, etc. The trigger fires off the job and immediately goes on to the next line of code, without waiting for results.
HTH,
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
October 4, 2002 at 4:25 pm
Thanks Steve.
I thought I had tried this before and found it not fully detaching the Job from the trigger. I am going to give it another shot... no pun intended.
October 5, 2002 at 5:35 am
At some point you reach the limits of what is practical in a trigger and need to move to more of a batch process mode. Inserting the pkey in a 'tobeprocessed' table or setting a flag in the table (using the trigger) are both valid. Then your job can run at whatever frequency is needed, no need to involve the trigger to run it.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply