Trigger causing Maximum trigger nesting level exceeded-CodePudding

I have created a new trigger that updates my [Purchase Table] if the number of delivered items equals the ordered quantity, but every time it's run I get the following error.

ODBC--update on a linked table 'dbo_Purchase Table' failed.
Maximum stored procedure, function, trigger or view nesting level exceeded (limit 32). (#217)

This is the trigger that I have created:

USE [####]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trUpdatePurchaseTableStatus]
    ON [dbo].[Purchase Table]
    AFTER UPDATE
AS
    BEGIN
        SET NOCOUNT ON;
        UPDATE PT
        SET PT.Delivered = 1
        FROM inserted as I
        INNER JOIN dbo.[Purchase Table] AS PT
        ON [PT].[ID] = I.[ID]
        WHERE PT.deliveredQTY >= PT.QTY
    END

CodePudding user response：

If the value of Delivered is purely based on if the value of deliveredQTY is greater than or equal to QTY I would suggest using a computed column instead, then you don't need a TRIGGER.

You can't change an existing column to be a computed column (you can't even ALTER a computed column) you would need to DROP the column and then add it:

--You would also need to DROP any CONSTRAINTs and/or INDEXes, etc, that use the column
ALTER TABLE dbo.[Purchase Table] DROP COLUMN Delivered;
GO
ALTER TABLE dbo.[Purchase Table]
ADD Delivered AS CONVERT(bit,CASE WHEN deliveredQTY >= QTY THEN 1 ELSE 0 END) /*PERSISTED*/; --If the column is to be indexed, you'll likely want the PERSISTED
GO

CodePudding user response：

The real answer here is to use a computed column, like @Larnu says.

However, if you really want to use a trigger for this, you can place the following code at the top to bail out early

ALTER TRIGGER [dbo].[trUpdatePurchaseTableStatus]
    ON [dbo].[Purchase Table]
    AFTER UPDATE
AS

SET NOCOUNT ON;

IF TRIGGER_NESTLEVEL(OBJECT_ID('dbo.trUpdatePurchaseTableStatus')) > 1
  OR NOT EXISTS (SELECT 1 FROM inserted)
    RETURN;

UPDATE PT
..........