February 10, 2004 at 8:15 pm
I think triggers are preventing my code from working...
I have two databases on a single server and I have created a trigger on Table_A in Database_A that executes StoredProcedure_A in Database_A whenever a record is updated or inserted. StoredProcedure_A does a few things and then executes StoredProcedure_B in Database_B which contains an if...else statement where it gets to updating Table_B in Database_B, the code finishes without an error message but when I look at Table_B in Database_B the record is not updated. I placed a print command within the if...else statement just before the update statement and the text I printed is displayed through Query Analyzer so I know the Update statement is reached. I also do a simple Update statement on Table_B in Database_B with Query Analyzer with the same results.
There are several existing triggers on Table_B in Database_B but since I haven't done too much with triggers I'm not sure if they are preventing me from updating the table. Is it possible that permissions are preventing me even though I am doing everything under the SA login?
I did find code on SQLServerCentral.com that disables a trigger so you can run code before re-enabling the trigger again and the code also can be SPID specific so that not every prcoess is disabling and re-enabling the trigger.
Does this seem like the thing to do if it is the triggers that are preventing the Update?
I am planning on testing out this trigger disabling code tomorrow.
Has anyone had something similar happen to them before?
Thanks for any help,
Scott
February 11, 2004 at 12:30 am
Short answer (imho of course) is No.
It's not appropriate to circumvent triggers by disabling / enabling them.
Having said that, I'm sure that there may be some circumstance that would warrant it, but I don't believe that it would be especially often.
Question is, is this such a case?. Have you thought of why the triggers are there in the first place?
Will anything break if you randomly switches them off and on?
What consequences will this bring for the consistency of the databases involved?
I wouldn't mess with this unless I knew the answers to all these questions...
Now, in order to aid you in your search, try to set up a profiler trace on your queries to see what's happening when you do your stuff.
After that's done, you should be able to decide if triggers are to blame, or if it's something else..
=;o)
/Kenneth
February 12, 2004 at 6:55 am
YOU can also place Print or Select statements in your trigger(s) to determine if or where the trigger(s) are firing and failing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 13, 2004 at 8:25 am
The next morning I looked at everything againg and realized that one of my where clauses did not make sense. Everything worked once I corrected the where clause.
Thanks for the help,
Scott
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply