December 11, 2012 at 10:01 am
I would really appreciate some feedback on an issue that's come to light lately in our SQL 2008 R2 environment.
We recently integrated a third-party vendor's application into our in-house CRM program. The vendor's app has its own SQL database and during the integration process I had to write some evil yet necessary INSERT and UPDATE triggers in my CRM app so that new customers created or existing customers who had account attributes updated in our CRM application would have those changes carried over to the corresponding third-party vendor's app.
There's also a SQL Agent job scheduled for the middle of the night that does route updates to the in-house CRM's account records (there are fields for customer's pricing and product lines that are kept up to date nightly). The fields have nothing to do with the third-party vendor's mapping application except the update statements fire the trigger to update the vendor's app even those there are no fields in the vendor's app that require updating. The SQL Agent job execution time has soared from just a few minutes to just under 7 hours because of that trigger.
Is there any way to get the trigger not to fire if the source is the stored procedure in the SQL Agent scheduled job?
Thanks in advance,
Andre Ranieri
December 11, 2012 at 10:09 am
Does your SQL Agent's execution/proxy account differ from the CRM account? If so you may be able to determine the "caller" and use a CASE statement to bypass the trigger - I can't remember off hand how you can determine the account executing the command, but am pretty certain there's a way to do it - just a thought :w00t:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 11, 2012 at 10:15 am
You could have the SQL Agent job disable the triggers.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 11, 2012 at 10:17 am
MDJ - that's a thought I hadn't considered.
Based on my own research (ie Google) I'm experimenting with setting context in the sproc:
SET Context_Info 0x55555
then filtering for the context info in the trigger's header:
DECLARE @Cinfo VARBINARY(128)
SELECT @Cinfo = Context_Info()
IF @Cinfo = 0x55555
RETURN
My source for this idea is
This might just work, but I thought it would be nice to post my question to this forum out of intellectual curiosity, to see what other solutions people might recommend.
Cheers,
Andre Ranieri
December 11, 2012 at 10:19 am
Todsdad:
I considered this but we do have some late shift employees who modify the account database through the CRM UI while the sproc is being run in the SQL Agent Job.
If I disable the trigger at the start of the job, execute the sproc then enable the trigger, there's the chance that some updates would be missed.
Thoughts?
December 11, 2012 at 10:49 am
Andre Ranieri (12/11/2012)
Todsdad:I considered this but we do have some late shift employees who modify the account database through the CRM UI while the sproc is being run in the SQL Agent Job.
If I disable the trigger at the start of the job, execute the sproc then enable the trigger, there's the chance that some updates would be missed.
Thoughts?
That's exactly what I was hoping, that no one was using the system at night and the triggers could be disabled without worry.
You say that the night job does not update columns that need to be affected by the trigger? Why not alter the triggers to not fire when those columns are updated by using COLUMNS_UPDATED? Then the triggers would exit before making the expensive cross-database update.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply