Content of inserted table when doing mass updates

[RonPro]

I have a trigger that fires for inserts and updates, the actual text of which may be immaterial as I am first seeking a conceptual understanding.

We are currently using SQLServer2K.

This trigger has worked for prior versions (still backended with SQLServer2K) of our data collection system, but we recently updated this system and now I'm experiencing what I perceive to be a trigger issue.

It uses the inserted table to get data for the inserted/updated entry. It is coded currently using the concept of

set @varname = (select top 1 ... from inserted)

It then goes on and does [possibly] an insert into another table if the updated entry meets certain criteria. The second table has no trigger and I do not update the subject table of the original trigger.

My problem is that our new App release appears to be using more backended logic via mass updates/inserts to the subject table whereas in the past these were (apparently) individual updates/inserts from the client.

Now my observations show that only 1 row gets the benefit of the trigger even when 20 rows were updated or inserted concurrently (based on looking at the 'inserted' or 'updated' timestamp which is exactly the same) for a batch of updates.

Is the inserted (and/or deleted for that matter) table truly populated with data for ALL of the rows impacted by a mass insert/update? Do I need to change this code out to either an SP or to a method that cycles thru all rows in the inserted table? (unfortunately the design of the app db does not use GUIDs or other convenient PK methods)

 

[pjw001]

If a single statement inserts many rows then the inserted table will contain many rows - i.e. one row for each row inserted.

The simplest way to deal with it is to use a cursor to read all the inserted rows (possibly joining to the rows in deleted) and process each row in turn.

I am not suggesting that a cursor is the only way to do it, I just use it to try to explain the concept.

I hope this helps.

 

[RonPro]

Thanks - That's pretty much what I figured. But I just couldn't find a reference to the issue anywhere on the web or even within this forum.

 

[vongrunt]

Oh, we mention this approximately every 3rd trigger-related topic :X

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[RonPro]

Here's what I used for searching...

inserted table mass update

Now I see that there was 1 (one) entry that applied (had I read thru it in its entirety).

I am sorry about that !!

Perhaps someone should make an FAQ with those key words in the caption (?)

 

[vongrunt]

Yes, TT search ain't google... "trigger multiple rows" should work better.

Actually I'm surprised there are no trigger FAQs yet.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba