Prevent Trigger Recursion when Updating Field

• jean 93062

  Say Hey Kid

  Points: 683

  More actions

  July 9, 2013 at 9:20 am

  #279652

  My Primary data base updates our satellite data bases periodically. This update is part of the application over which I do not have any control. The primary data base will insert new records or update existing records in a table at the satellite location. One of the fields it updates is called Location. The field gets touched on every sync. Unfortunately, this particular field is supposed to be unique to each satellite location and I have a trigger that will restore the Location field back to the correct value. This, of course, causes the trigger to fire again, which I do not want to do.

  I am not able to turn off Trigger recursion because I believe it will affect other parts of the system and other applications that depend on it.

  Any suggestions?

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  July 9, 2013 at 5:12 pm

  #1631028

  Check out TRIGGER_NESTLEVEL here : http://msdn.microsoft.com/en-us/library/ms182737(v=sql.105).aspx

  you can put something like this in your trigger to stop it firing itself.

  if TRIGGER_NESTLEVEL()>1

  RETURN;

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]