1

I am getting this error after I add a trigger to a table: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). Here is the trigger:

CREATE TRIGGER dbo.tu_PeopleGeneral
   ON  dbo.PEOPLEGENERAL
   AFTER UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @PeopleCodeID nvarchar(10)
    Select @PeopleCodeID = i.People_Code_ID from inserted i

    UPDATE PEOPLEGENERAL SET PICTURE_NAME = RTRIM(PICTURE_NAME)
    WHERE PEOPLE_CODE_ID = @PeopleCodeID

END

I want a trigger that will trim any spaces from the end of the data that the user is attempting to update. I have a similar insert trigger that works just fine. I'm not sure why it will not work for updates.

azenk
  • 11
  • 2
  • Add tag for RDBMS used. – June7 Mar 27 '23 at 21:10
  • MS SQL SERVER is being used. – azenk Mar 27 '23 at 21:12
  • They meant to add the tag to your question – Andrew Mar 27 '23 at 21:15
  • Oh, of course. I will add that. – azenk Mar 27 '23 at 21:16
  • This doesn't answer the question of why your attempt doesn't work, but have you considered using an `instead of insert` trigger instead of `after insert` and changing the data before it is inserted? – EdmCoff Mar 27 '23 at 21:31
  • 2
    Aside... your trigger assumes that there will only ever be one row. Triggers can get executed for 0, 1 or many rows - you need to treat the `inserted` virtual table as a table, because it is, and use set-based operations. – AlwaysLearning Mar 27 '23 at 23:53
  • Have a read through [Remarks for DML Triggers](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql#remarks-for-dml-triggers). I'm guessing you also have an `AFTER UPDATE` trigger on this table that is retriggering itself and so going recursive. – AlwaysLearning Mar 27 '23 at 23:55
  • Do you have an `UPDATE` trigger also? If so please show. Note that your trigger is fundamentally flawed due to the issue mentioned by @AlwaysLearning see also https://www.brentozar.com/archive/2019/05/the-silent-bug-i-find-in-most-triggers/ – Charlieface Mar 28 '23 at 00:38
  • And the solution is mentioned in the duplicate: `IF TRIGGER_NESTLEVEL( OBJECT_ID('dbo.mytrigger') ) > 1 RETURN;` – Charlieface Mar 28 '23 at 00:40
  • Sorry. Just realized I copied the insert trigger and posted it instead of the update trigger. Here is the update trigger: – azenk Mar 28 '23 at 13:04
  • You still have similar problems, there's nothing stopping you from updating the same table. I suggest two things: add : IF @@ROWCOUNT = 0 RETURN at the start of the trigger. Remove the variable and join your inserted table so you only update the changed data. And finally, don't trim if it's already trimmed. Trigger_nestlevel is just a bandaid IMHO – siggemannen Mar 28 '23 at 16:15

0 Answers0