在SQL Server表上更新时触发

Question

我想为SQL Server表的某些条件更新一个列值。我有以下用于创建表的代码

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)

以下是Insert和Update语句

INSERT INTO [Equipment] ([Equipment],[Facility],[EquipmentType],[Active]) VALUES ('E02','1029',10,1)
UPDATE [Equipment] Set Active = 0 where [Equipment] = 'E01'

以下是触发器脚本

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

当我尝试运行Insert或update语句时，出现了以下错误。

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

有人能给出建议吗？表中还有另外三个触发器。这是我需要的临时解决方案。

Answer 1

您可以使用TRIGGER_NESTLEVEL检查递归调用。

您还应该检查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

还要注意在更新中使用了正确的JOIN语法。

Answer 2

除非您选择使用Recursive Triggers，否则更新其表的触发器将不会再次触发，因此请检查递归触发器数据库设置，如果它处于打开状态，请将其关闭：

alter database current set recursive_triggers off

或者将触发器编码为不执行零行的更新，例如

if not exists (select * from inserted) return