Updating multiple records from trigger

[kbieber]

What I thought was a simple update....

I would like to update multiple rows of table2 upon insert or update of table1. My approach was to create a trigger on table1 .....

CREATE TRIGGER [trgOECusItemMaster] ON [table1]
FOR INSERT,UPDATE
AS

declare @item_no varchar(50)
declare @cus_item_no varchar(500)

select @item_no = item_no, @cus_item_no = cus_item_no from inserted

exec spUpdtSFOCustItemNbr @item_no, @cus_item_no



Then have the trigger call an sp to update the rows in table2.....

CREATE PROCEDURE [spUpdtSFOCustItemNbr]
(@item_no varchar(50), @cus_item_no varchar(500))
AS
update table2
set user_def_fld_2 = @cus_item_no
from table2
where item_no = @item_no


The error is: Subquery returned more than 1 value.


How do I get past this error?

 

[SQLDenis]

will this work for you??

CREATE TRIGGER [trgOECusItemMaster] ON [table1]
FOR INSERT,UPDATE
AS

update t2
set t2.user_def_fld_2 = i.cus_item_no
from table2 t2
join inserted i on t2.item_no = cus_item_no


“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[kbieber]

No, that's what I started with and I get the same error message.