kresa kresa - 1 year ago 70
SQL Question

How to ensure that a table has only a single record with Is_Deleted = 0 per Customer_Number?

I have a table with these columns:

ID (int)
Customer_Number (int)
Is_Deleted (bit)
Details (nvarchar)

I need to create a trigger for the table which is going to prevent update of a row when trying to change
column from 'true' to 'false' while already having another different existing row with the same customer number and a false value on
(I can't allow two rows with the same customer number with
value in the

Answer Source

No triggers.

create unique index t_ix_Customer_Number_Is_Deleted_0 
on t (Customer_Number) 
where Is_Deleted = 0

insert into t (ID,Customer_Number,Is_Deleted) values (1,1,1),(2,1,0),(3,1,1),(4,1,1)

(4 row(s) affected)

update t set Is_Deleted = 0 where ID = 1

Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object 'dbo.t' with unique index 't_ix_Customer_Number_Is_Deleted_0'. The duplicate key value is (1).
The statement has been terminated.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download