我想為 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 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 OR 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).
任何機構都可以提供建議嗎？表中還有其他三個觸發器。對此，我需要作為臨時解決方案來解決。
uj5u.com熱心網友回復：
除非您選擇加入Recursive Triggers，否則更新其表的觸發器不會再次觸發，因此請檢查 RECURSIVE TRIGGERS 資料庫設定，如果已打開，請將其關閉：
alter database current set recursive_triggers off
或者將觸發器編碼為不執行零行的更新，例如
if not exists (select * from inserted) return
uj5u.com熱心網友回復：
您可以使用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在更新中使用正確的語法。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/363704.html
標籤：sql-server 触发器
上一篇：SQL查詢回傳高于平均工資的工資