Q:

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 Is_Deleted column from 'true' to 'false' while already having another different existing row with the same customer number and a false value on Is_Deleted (I can't allow two rows with the same customer number with false value in the Is_Deleted column). 

A:

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.

