Triggers doing offline or disconnected tasks

  • 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?

  • 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.

  • 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.

  • 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

  • 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.

  • 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

    http://www.sqlservercentral.com/columnists/awarren/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply