Problem: Trigger and multiple updates to the table

Hey, I have couple of triggers to one of the tables. It is failing if I update multiple records at the same time from the stored procedure.

UPDATE
table1
SET
col1 = 0
WHERE col2 between 10 and 20

Error I am getting is :

Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

What is the best possible way to make it work? Thank you.Figure out which trigger is blowing up, and fix it? Knowing that there is more code than we can see, I can't for the life of me figure out a way to help you find out where the problem is, much less how to fix it.

-PatP|||I guess this is the problematic trigger. Should I use cursor to handle multiple inserts/updates?

CREATE trigger t_thickupdate on dbo.sched_lot_hdr
for update
as

-- declare local variables
declare
@.lotno int,
@.charthick varchar(10),
@.decthick decimal(6,5)

-- populate local variables
set @.lotno = (select lotno from inserted)
set @.charthick = (select matcharthick from inserted)
set @.decthick = (select matthick from inserted)

-- determine which was updated: character thickness or decimal thickness
if update(matthick)

-- decimal thickness was updated
begin
set @.charthick = (select thkfrac from prod_thk_stds where thkdec = @.decthick)
update sched_lot_hdr set matcharthick = @.charthick where lotno = @.lotno
end
else

-- character thickness was updated
begin
set @.decthick = (select thkdec from prod_thk_stds where thkfrac = @.charthick)
update sched_lot_hdr set matthick = @.decthick where lotno = @.lotno
end|||A trigger act on 1 or more rows...depending on the DML

inserted and/or deleted can have more than 1 row...

You need to al;ter your thinking and think more set based...|||Also looking at it...

Your updating the same table in the trigger that is causing the trigger action because of an update....that doesn't make sense...|||I'd suggest using:CREATE TRIGGER t_thickupdate
ON dbo.sched_lot_hdr
FOR UPDATE
AS

-- determine which was updated: character thickness or decimal thickness
IF Update(matthick) -- decimal thickness was updated
UPDATE sched_lot_hdr
SET matcharthick = s.thkfrac
FROM inserted AS i
JOIN sched_lot_hdr
ON (sched_lot_hdr.lotno = i.lotno)
JOIN prod_thk_stds AS s
ON (s.thkdec = i.matthick)
ELSE -- character thickness was updated
update sched_lot_hdr
set matthick = s.thkdec
FROM inserted AS i
JOIN prod_thk_stds AS s
ON (s.thkfrac = i.matcharthick)
JOIN sched_lot_hdr
ON (sched_lot_hdr.lotno = i.lotno)

RETURNNote that you need to test this up one side and down the other before you put it into production, this was a quick paste up job with no opportunity for me to test it!

-PatP|||yeah :(

This already been developed. I came to this project recently and working on some enhancements. Thanks for the help. I think I understand the problem, I will resolve it.

Update: thats fast. Thanks Pat, I will test it.|||Originally posted by Brett Kaiser
A trigger act on 1 or more rows...depending on the DML

inserted and/or deleted can have more than 1 row...

You need to al;ter your thinking and think more set based... Yea vous! I'm with you on that!
Originally posted by Brett Kaiser
Also looking at it...

Your updating the same table in the trigger that is causing the trigger action because of an update....that doesn't make sense... Nah, that happens all the time when folks convert from what I call "unit-record" code to a database. They find ways to get the database to "clean up" data coming from disparate sources that would have required application changes when the only thing that could reach the data was their application.

-PatP