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?

score 2 · Answer 1 · answered May 23 '09 at 17:23

2

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

answered May 23 '09 at 17:23

no_one

score 1 · Accepted Answer · answered May 23 '09 at 17:25

1

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.

answered May 23 '09 at 17:25

Sankar Reddy

It works fine as long as you have distinct records in table_2. Try to add more 1s in it. I have seen it in past. – no_one May 23 '09 at 17:52
I use SQL 2K5. You are right this works! It's interesting because I got this error message while editing records with Sql Server Managment Studio table_2 ->Open table and after changing record I couldn't commit it. I wonder why. – zby_szek May 23 '09 at 18:18
What's more when I add column with primary key to table_2 it works when I edit records this way table_2->Open table It must be some kind Sql Server Management Studio limitation. – zby_szek May 23 '09 at 18:38
>>It must be some kind Sql Server Management Studio limitation. Try avoiding the SSMS for these activities. SSMS is known for many bugs and its best done using TSQL. – Sankar Reddy May 24 '09 at 00:31

2 Answers2