I would like to update one column value for certain condition for a SQL Server table. I have the following code for creation of table
CREATE TABLE [dbo].[EQUIPMENT](
[ID] [int] IDENTITY(10000,1) NOT NULL,
[Equipment] [nvarchar](80) NOT NULL,
[Facility] [nvarchar](40) NULL,
[EquipmentType] [smallint] NULL,
[Active] [bit] NOT NULL)
Following are the Insert and Update Statements
INSERT INTO [Equipment] ([Equipment],[Facility],[EquipmentType],[Active]) VALUES ('E02','1029',10,1)
UPDATE [Equipment] Set Active = 0 where [Equipment] = 'E01'
Following is the Trigger script
CREATE TRIGGER dbo.ATRG_EquipmentTypeUpdate
ON [dbo].[Equipment]
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- update your table, using a set-based approach
-- from the "Inserted" pseudo table which CAN and WILL
-- contain multiple rows!
UPDATE [dbo].[Equipment]
SET EquipmentType = 15
FROM Inserted i
WHERE [dbo].[Equipment].ID = i.ID
AND [dbo].[Equipment].EquipmentType = 10
END
GO
As I try to run the insert or update statement - I have the following error.
Msg 217, Level 16, State 1, Procedure ATRG_EquipmentTypeUpdate1, Line 12 [Batch Start Line 9] Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can anybody advise? There are three other triggers in the table. This, I need as a temporary solution, for workaround.
You can use TRIGGER_NESTLEVEL to check for recursive calls.
You should also check for no rows in inserted.
CREATE TRIGGER dbo.ATRG_EquipmentTypeUpdate
ON [dbo].[Equipment]
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF TRIGGER_NESTLEVEL(@@PROCID, 'AFTER', 'DML') > 1
OR NOT EXISTS (SELECT 1 FROM inserted)
RETURN;
-- update your table, using a set-based approach
-- from the "Inserted" pseudo table which CAN and WILL
-- contain multiple rows!
UPDATE e
SET EquipmentType = 15
FROM inserted i
JOIN [dbo].[Equipment] e ON e.ID = i.ID
AND e.EquipmentType = 10;
END
Note also the use of proper JOIN syntax in the update.