Multi row update Trigger.

[kingstonr]

I have a after Update trigger in one table which will
insert into audit table.

If i update one row in a table it works fine.When I
update multiple row,The trigger fails can anybidy help me in this.

 

[jbenson001]

please show the code you are using?

 

[bborissov]

Trigger fires AFTER the job is done. That means in the trigger you may have multiple updated rows. You must work with INSERTED metatable to update every row you need. If you post the trigger code maybe we could help more.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[kingstonr]

HI,

My trigger has the code like below.

select @valueold=symbol from sec where rating_id=(select ratingid from deleted)
select @valuenew=symbol from sec where rating_id=(select ratingid from inserted)
insert into audit(@valueold,@valuenew)
-----------------------------------------
now I update the base table.
update table a set symbol='a' where id=2
Trigger works good values are inserted in audit.

Now
----
update table a sdet symbol='a' where id in (1,2)

trigger fails with error

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

 

[jbenson001]

You already posted this question:
thread183-1312955

 

[bborissov]

Change:

select @valueold=symbol from sec where rating_id=(select ratingid from deleted)
select @valuenew=symbol from sec  where rating_id=(select ratingid from inserted)
insert into audit(@valueold,@valuenew)

to:

select @valueold=symbol from sec where rating_id=(select ratingid from deleted)
select @valuenew=symbol from sec  where rating_id=(select ratingid from inserted)
insert into audit 
SELECT ValueOld,
       ValueNew
FROM (SELECT Ins.ratingid,
             Del.symbol AS ValueOld,
             Ins.symbol AS ValueNew
      FROM Inserted Ins
      INNER JOIN Deleted Del ON 
            Ins.ratingid = Del.ratingid) Tbl1

not tested at all.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP