INSTEAD OF triggers

• Miquel Martí

  Ten Centuries

  Points: 1217

  More actions

  December 1, 2005 at 3:58 am

  #111017

  Hi all!

  I'd like to manage the inserts of table A with a stored procedure, so I created an instead of trigger that cached the info inserted in the row, and then called an SP.

  This SP contains the insert instructions.

  But here is my paradox... these SP instructions will trigger the first trigger again recursively because of the insert statment (so the analizer fires an error)

  What can I do to insert in a row using a stored procedure that inserts in the table that fired the trigger?

  TKX in advance

• padmakumar_pga

  Right there with Babe

  Points: 766

  More actions

  December 1, 2005 at 5:59 am

  #607010

  hi

  check recursive triggers  database propertity is on .

  if it is ON turn off .

  You can see this in database properity option tab

  You can use sp_dboption to alter this property

  regards

  padmakumar

   

• mtoronyi

  SSC Veteran

  Points: 238

  More actions

  December 2, 2005 at 7:19 am

  #607334

  I had similar issues with triggers firing recursively. I added the following line at the begining of the trigger. It prevents the recursive behavior.

  IF (SELECT trigger_nestlevel()) > 1

  RETURN