我有兩個布爾（位）欄位，我想保持互斥。它們都可以為假，但如果其中一個設定為真，則另一個必須設定為假。
我嘗試創建 2 個觸發器，但最終創建了一個無限回圈（在 32 次迭代后停止）。顯然它們是相互觸發的。這是我嘗試過的代碼：
CREATE OR ALTER TRIGGER dbo.TriggerNameA
ON dbo.TableName
AFTER UPDATE
AS
IF UPDATE(FieldA)
BEGIN
UPDATE dbo.TableName
SET FieldB =
CASE WHEN dbo.TableName.FieldA = 1
THEN 0
ELSE 1
END
FROM dbo.TableName INNER JOIN inserted ON dbo.TableName.ID = inserted.ID
END;
;
GO
CREATE OR ALTER TRIGGER dbo.TriggerNameB
ON dbo.TableName
AFTER UPDATE
AS
IF UPDATE(FieldB)
BEGIN
UPDATE dbo.TableName
SET FieldA =
CASE WHEN dbo.TableName.FieldB = 1
THEN 0
ELSE 1
END
FROM dbo.TableName INNER JOIN inserted ON dbo.TableName.ID = inserted.ID
END;
;
GO
知道如何避免無限回圈嗎？
uj5u.com熱心網友回復：
使用 Check 約束消除了對觸發器的需要：
SQL小提琴
MS SQL Server 2017 架構設定：
create table MyTable
(
ID int Identity,
FieldA bit,
FieldB bit,
CONSTRAINT Chk_Fields CHECK (FieldA <> 1 OR FieldB <> 1)
)
INSERT INTO MyTable(FieldA, FieldB)
VALUES (0,0),(0,1),(1,0)
查詢 1：
select *
from MyTable
結果：
| ID | FieldA | FieldB |
|----|--------|--------|
| 1 | false | false |
| 2 | false | true |
| 3 | true | false |
查詢 2：
INSERT INTO MyTable(FieldA, FieldB)
VALUES (1,1),(0,1),(1,0)
結果：
The INSERT statement conflicted with the CHECK constraint "Chk_Fields". The conflict occurred in database "db_18_941bc9", table "dbo.MyTable".
查詢 3：
UPDATE MyTable
SET FieldB = 1
WHERE ID = 3
結果：
The UPDATE statement conflicted with the CHECK constraint "Chk_Fields". The conflict occurred in database "db_18_941bc9", table "dbo.MyTable".
編輯正如@CharlieFace 在他的評論中提到的那樣，等效的檢查約束
(FieldA = 0 OR FieldB = 0)更具可讀性
uj5u.com熱心網友回復：
好的，我為我之前的回答道歉，但我確定我現在明白了。感謝@DB<>Fiddle 指出它仍在經歷遞回（或嵌套，不確定）。
所以我使用了 trigger_nestlevel 函式。經過測驗，現在（終于）它可以作業了：
create table MyTable
(
ID int Identity,
FieldA bit,
FieldB bit
)
go
CREATE OR ALTER TRIGGER dbo.Trigger1
ON dbo.MyTable
AFTER UPDATE
AS
IF TRIGGER_NESTLEVEL() = 1
BEGIN
IF UPDATE(FieldA)
BEGIN
UPDATE dbo.MyTable SET dbo.MyTable.FieldB = 0
FROM dbo.MyTable INNER JOIN inserted ON dbo.MyTable.ID = inserted.ID;
END;
END;
;
GO
CREATE OR ALTER TRIGGER dbo.Trigger2
ON dbo.MyTable
AFTER UPDATE
AS
IF TRIGGER_NESTLEVEL() = 1
BEGIN
IF UPDATE(FieldB)
BEGIN
UPDATE dbo.MyTable SET dbo.MyTable.FieldA = 0
FROM dbo.MyTable INNER JOIN inserted ON dbo.MyTable.ID = inserted.ID;
END;
END;
;
GO
INSERT INTO MyTable(FieldA, FieldB)
VALUES (0,0)
SELECT * FROM MyTable;
-- 0 0
UPDATE MyTable
SET FieldA = 1
WHERE ID = 1;
SELECT * FROM MyTable;
-- 1 0
UPDATE MyTable
SET FieldB = 1
WHERE ID = 1;
SELECT * FROM MyTable;
-- 0 1
然后我可以來回切換，他們繼續切換我需要的方式。哇！
uj5u.com熱心網友回復：
這是使用 INSTEAD OF TRIGGER 的替代方法，以確保在 UPDATE 時只有一個欄位設定為 1。我在觸發器中做出了一個決定，如果虛擬 INSERTED 表中的 FieldA 設定為 1，那么我將 FieldB 設定為零：
設定
create table MyTable
(
ID int Identity,
FieldA bit,
FieldB bit
)
CREATE TRIGGER TriggerNameA ON MyTable
INSTEAD OF UPDATE
AS
IF UPDATE(FieldA)
BEGIN
MERGE [MyTable] AS t1
USING (SELECT * FROM INSERTED) AS t2
ON t1.ID = t2.ID
WHEN MATCHED THEN
UPDATE SET t1.[FieldA] = t2.FieldA,
t1.[FieldB] = CASE WHEN t2.FieldA = 1 THEN 0 ELSE t2.FieldB END;
END
ELSE IF UPDATE(FieldB)
BEGIN
MERGE [MyTable] AS t1
USING (SELECT * FROM INSERTED) AS t2
ON t1.ID = t2.ID
WHEN MATCHED THEN
UPDATE SET t1.[FieldB] = t2.FieldB,
t1.[FieldA] = CASE WHEN t2.FieldB = 1 THEN 0 ELSE t2.FieldA END;
END;
-- Add Else for cases where FieldA & FieldB weren't updated if required
結果 1
INSERT INTO MyTable(FieldA, FieldB)
VALUES (0,0),(0,1),(1,0);
SELECT * FROM MyTable
ID FieldA FieldB
1 False False
2 False True
3 True False
結果 2
UPDATE MyTable
SET FieldB = 1
WHERE ID = 1
SELECT * FROM MyTable
ID FieldA FieldB
1 False True
2 False True
3 True False
結果 3
UPDATE MyTable
SET FieldB = 1
WHERE ID = 3
SELECT * FROM MyTable
ID FieldA FieldB
1 False True
2 False True
3 False True
結果 4
UPDATE MyTable
SET FieldA = 1
WHERE ID = 2
SELECT * FROM MyTable
ID FieldA FieldB
1 False True
2 True False
3 True False
DB<>小提琴
根據 UPDATE 陳述句中更新的列，編輯為更新 fieldA 或 FieldB
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/411910.html
標籤：
上一篇：案例運算式-比較日期