Trigger that modifies multiple rows on diffrent table then it was invoked on in SQL Server 2005

Question

I have tried to perform update on table which was trigger by update on other table and I got error message:

The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.

For example I have this tables:

table_1
===========
int id   (primary_key,identity)
nchar(10)  state_name

table_2
===========
int number

And after updating table_2 I want to change all values in column 'state_name' to 'false'

create trigger tr on table_2
after update
as
update table_1 set state_name = 'false'

And when I try to update table_2 I receive error message. Is there a way to walk around this limitation?

Answer 1

Add a primary key constraint in Table_2 (for example an auto inc no) and you will be fine.

Answer 2

create table table_1(id int identity(1,1) primary key, state_name char(10))

create table table_2 ( number int) go

create trigger tr on table_2 after update as update table_1 set state_name = 'false' go

insert table_1 select 'true' insert table_2 select 1

go

update table_2 set number = 2

select * from table_1

select * from table_2

Which version do you use? It worked out nicely in SQL 2K8 & SQL 2K5. Check your code again.