Triggers Mutually Exclusive Boolean Fields

Solution 1:

Using a Check constraint removes the need for a trigger:

SQL Fiddle

MS SQL Server 2017 Schema Setup:

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)

Query 1:

select *
from MyTable

Results:

| ID | FieldA | FieldB |
|----|--------|--------|
|  1 |  false |  false |
|  2 |  false |   true |
|  3 |   true |  false |

Query 2:

INSERT INTO MyTable(FieldA, FieldB)
VALUES (1,1),(0,1),(1,0)

Results:

The INSERT statement conflicted with the CHECK constraint "Chk_Fields". The conflict occurred in database "db_18_941bc9", table "dbo.MyTable".

Query 3:

UPDATE MyTable
  SET FieldB = 1
WHERE ID = 3

Results:

The UPDATE statement conflicted with the CHECK constraint "Chk_Fields". The conflict occurred in database "db_18_941bc9", table "dbo.MyTable".

EDIT As @CharlieFace mentions in his comment, the equivalent check constraint of (FieldA = 0 OR FieldB = 0) is more readable