sql-servertriggers

Trigger on Update on SQL Server table


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.


Solution

  • 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.