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
Advertisement
Answer
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