1

Here is the scenario: There is Trigger A and Trigger B, both in the Person table. I can't trigger the Trigger A when the update on table Person comes from Trigger B.

Is there something, such as an IF that I could use to solve this situation?

Thanks in advance for any help!

André Luiz
  • 6,642
  • 9
  • 55
  • 105
  • Are the triggers setting values in Person that could be solved by instead making the given column computed? Some schema info, and the triggers would help give more meaningful answers. – Jaaz Cole Aug 21 '14 at 14:56
  • Why do you need to update the table at all when you're updating it currently anyway? Couldn't you include what the trigger does in the update-statement? Triggers are often the root of all evil, use stored-procedures. https://sqlblog.org/2009/10/12/bad-habits-to-kick-abusing-triggers – Tim Schmelter Aug 21 '14 at 14:58
  • Are nested triggers used in other scenarios (and are they valid)? If not, you could [disable nested triggers](http://msdn.microsoft.com/en-us/library/ms178101(v=sql.105).aspx) at the server level. – Dave Mason Aug 21 '14 at 15:33
  • Firstly I don't like triggers at all. Secondly the first trigger is in the Person table and the second is not. And I don´t have access to the source code, I just know that this field is used. Thanks for the suggestions! I will be trying to apply them! – André Luiz Aug 21 '14 at 15:59

2 Answers2

1

Right at the start, I will warn you that having multiple triggers on one table is not a good idea. Try and merge the actions of the two triggers into one if possible. However, if that is not a solution for you, then read on for my version. (I am not certain if it is practically valid, but go ahead and give it a shot anyway) 

CREATE TRIGGER triggerB
ON yourtable 
FOR UPDATE 
AS 
    BEGIN
    ALTER TABLE table_name DISABLE TRIGGER triggerA
    --your processing
    ALTER TABLE table_name ENABLE TRIGGER triggerA
    END

This question deals with disabling then enabling triggers inside a stored procedure. This is an application of the same in a trigger.

Disclaimer: I am counting on this to fail because altering a table while in a trigger defined on that same table seems like an impossible task. But I have no resources at hand to test my wacky theory, so please test it and let me know if I'm thinking too far outside the box.

Community
  • 1
  • 1
CodeNewbie
  • 2,003
  • 16
  • 29
  • Thanks for your answer! I will be trying it! – André Luiz Aug 21 '14 at 16:29
  • Let me know what happens. – CodeNewbie Aug 21 '14 at 16:37
  • I just noticed your last comment on the question. I think if the two triggers are on different tables, that should allow my proposed solution to work without an issue IMO. – CodeNewbie Aug 21 '14 at 16:56
  • Note that you may have a potential race condition in a multi-user situation unless you're serializing access somewhere along the way. For example: User 1 causes TriggerB to execute, which disables TriggerA on TableA. User 2 inserts directly into TableA, expecting the now-disabled trigger to fire... but it won't. You can get some subtle, hard-to-find-and-fix bugs this way. – pmbAustin Aug 21 '14 at 18:01
  • pmbAustin is right. In a high user traffic period, this bug could manifest quite frequently. Do consider that possibility, Andre Luiz. – CodeNewbie Aug 21 '14 at 18:06
  • @CodeNewbie No. I saw some danger in it. I did the tests and it worked all ok but then I realized that it is common to disable and enable triggers in the project that I´m working. Thus I decided not to do so. What happened is that I got the source code, which I didn't have before and I replaced the field by a computed field and took off the updates in this fields. By the way. Thanks everyone for answering and commenting in this question! – André Luiz Aug 26 '14 at 13:18
1

I got it!

IF TRIGGER_NESTLEVEL(OBJECT_ID('TRIGGER_NAME')) = 0
BEGIN

    -- Your Trigger STuff

END

Thanks for answers and comments.

André Luiz
  • 6,642
  • 9
  • 55
  • 105