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

Problem with Updatetext in SQL 2000 trying to concatenate multiple text fields

Hi all, I am creating a search table where the keywords field is made
up of several text fields and this is causing me some problems. I can
concatentate the text ok but i can't seem to concatenate matching
records here is the cursor loop. I'm not a fan of cursors but also
didn't see another way of achieving this.

declare @.ptr1 varbinary(16)
declare @.Ptr2 varbinary(16)
declare @.profileid int
declare @.x int

set @.profileid = 0

while @.profileid is not null
begin

select
@.profileid = min([id]),
@.ptr1 = MIN(textptr(text1))
from #holding
where [id] @.profileid

declare c2 cursor fast_forward for
select textptr(searchterms), datalength(searchterms)
from search
where search.[id] = @.profileid

open c2

fetch c2 into @.ptr2, @.x

while @.@.fetch_status = 0
begin
updatetext search.searchterms @.ptr2 null 0 #holding.text1 @.ptr1
fetch c2 into @.ptr2, @.x
end

close c2
deallocate c2

end

The #holding table contains the fields that i want to concatenate and
the search table is the resulting table. This example would loop
through search and find id 1 in search and then append another field
matching id 1 in holding then move onto the next field in turn going
through the whole table.

i.e.

search holding result after each loop
id text id text
1 abc 1 def abcdef
2 ghi 2 jkl ghijkl

When I run this, some of the records concatenate properly but most dont
with the same text being appended to the end of searchterms. i.e loads
of results will end up with jkl tagged onto the end. I can't figure out
when my loop is falliing over!!! Can anyone help?

DanDan (dan_barber2003@.hotmail.com) writes:

Quote:

Originally Posted by

select
@.profileid = min([id]),
@.ptr1 = MIN(textptr(text1))
from #holding
where [id] @.profileid

This does not look good. Is there are any reason to assume that text
pointers are aligned with id?

Either do

SELECT @.profileid = MIN(id)
FROM #holding
WHERE id @.profileid

SELECT @.ptr1 = textptr(text1) FROM #holding WHERE id = @.profileid

Or run a second cursor over #holding.

And, yeah, this is ugly, painful and no fun at all. But it's the only
alternative.

In SQL 2005 there are new data types that replaces text & co, and
which does not have all the restrictions of text. Or the need to
use UPDATETEXT...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Dan wrote:

Quote:

Originally Posted by

When I run this, some of the records concatenate properly but most dont
with the same text being appended to the end of searchterms. i.e loads
of results will end up with jkl tagged onto the end. I can't figure out
when my loop is falliing over!!! Can anyone help?

I don't know /why/ the loop is doing the wrong thing, but adding some
debugging output after the UPDATETEXT line (e.g. SELECT * FROM SEARCH)
should at least reveal /when/ it's doing the wrong thing.

Also, I would seriously avoid columns named "id".|||Thanks Erland, you hit the nail on the head, i misunderstood what the
textptr did and thought both could be set at the same point. Hopefully
this will be the one and only time i have to use either text
concatenation or cursors. Is is the varchar(max) that replaces text in
SQL Server 2005?

Thanks again

Dan
Erland Sommarskog wrote:

Quote:

Originally Posted by

Dan (dan_barber2003@.hotmail.com) writes:

Quote:

Originally Posted by

select
@.profileid = min([id]),
@.ptr1 = MIN(textptr(text1))
from #holding
where [id] @.profileid

>
This does not look good. Is there are any reason to assume that text
pointers are aligned with id?
>
Either do
>
SELECT @.profileid = MIN(id)
FROM #holding
WHERE id @.profileid
>
SELECT @.ptr1 = textptr(text1) FROM #holding WHERE id = @.profileid
>
Or run a second cursor over #holding.
>
And, yeah, this is ugly, painful and no fun at all. But it's the only
alternative.
>
In SQL 2005 there are new data types that replaces text & co, and
which does not have all the restrictions of text. Or the need to
use UPDATETEXT...
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Dan (dan_barber2003@.hotmail.com) writes:

Quote:

Originally Posted by

Is is the varchar(max) that replaces text in SQL Server 2005?

Yes.

And to make it clear, text is still there but deprecated. You will
have to make an explicit change to the tables to use the new goodies.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx