Trigger on Update on SQL Server table-CodePudding

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 any body advise? There are three other triggers in the table. This, I need as a temporary solution, for workaround.

CodePudding user response：

A trigger that updates its table won't fire again unless you opt-in to Recursive Triggers, so check the RECURSIVE TRIGGERS database setting, and if it's on, turn it off:

alter database current set recursive_triggers off

Or code your trigger to not perform an UPDATE of zero rows, eg

if not exists (select * from inserted) return

CodePudding user response：

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(OBJECT_ID('dbo.ATRG_EquipmentTypeUpdate', '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 
   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.