Trigger nesting level exceeded?

Question

When this trigger fires, which is contained in the below code block

alter TRIGGER [dbo].[locations_update_geometry_After]
ON  dbo.TBL_LOCATIONS   
for update   
AS   
  INSERT dbo.TBL_LOCATIONS(SHAPE, X_Coord, Y_Coord,objectid) 
  SELECT a.Shape, a.X_Coord, a.Y_Coord, a.objectid
  FROM  
  (  
    SELECT  
      SHAPE = CASE WHEN SHAPE IS NOT NULL   
        THEN SHAPE ELSE Geometry::STPointFromText('POINT('  
          + CAST(X_Coord AS VARCHAR(20)) + ' '   
          + CAST(Y_Coord AS VARCHAR(20)) + ')', 26917) END,  
      X_Coord = CASE WHEN SHAPE IS NULL THEN X_Coord ELSE SHAPE.STX END,  
      Y_Coord = CASE WHEN SHAPE IS NULL THEN Y_Coord ELSE SHAPE.STY END,  
      objectid  
     FROM inserted  
  ) AS a

I get the following error message:

Unable to save edits. Underlying DBMS error [Microsoft SQL Server Native Client 10: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

I do not have 32 of anything associated with this table. any ideas?

Answer 1

Yes, trigger executes for 32 times before throwing a message; in order to stop doing that use this

IF ((
SELECT TRIGGER_NESTLEVEL()) > 1 )
RETURN

Answer 2

you might should look at "INSTEAD OF UPDATE"

http://technet.microsoft.com/en-us/library/ms188601(v=sql.105).aspx