Trigger qns for multiple Update.

[kingstonr]

I am using a update trigger in a table where after update
The trigger will insert the changed values in audit table
I am using like below in trigger.

select @valueold=symbol from Sec where id=(select id from deleted)
select @valuenew=symbol from Sec where id=(select id from inserted)
insert into audit(@valueold,@valuenew).

If I do update row by row it works fine.

But when I update with more than one row like below

update issuer set UpdDate=getdate()
where IssuerId in (1,1334)

I am getting the following Error.

Subquery returned more than 1 value.
This is not permitted when the
subquery follows =, !=, <, <= , >, >= or
when the subquery is used as an expression.

Can somebody help me out in this.

 

[jbenson001]

I think the error is occuring on the lines above the update code where you use a sub select to get id values. That is the only place i see that a subselect could cause problems and return more than one value

 

[SQLSister]

you need to write a set-based insert not a single line insert. Triggers should never be written as if only one record will be processed. something like

insert into audit (id, oldvalue, Newvalue)
select i.id, d.symbol, i.symbol from inserted i 
join deleted d on i.idfield = d.idfield

Questions about posting. See faq183-874