kresa kresa - 6 days ago 7
SQL Question

Create a trigger to prevent update in SQL Server

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).

Answer

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.

Comments