inserted/deleted tables

Does the data in the rows in the inserted and deleted tables always correspond? For instance, row 1 in inserted corresponds with row 1 in deleted.
Thanks,

OK, if you

-Insert n rows you have n rows in the inserted.
-Delete n rows you have n rows in the deleted table.
-Update n rows you have n rows in the inserted and n rows in the deleted table.

So for an update the rowcount is always corresponding.

HTH, Jens Suessmeyer

|||

Well, what I was really wanting to know is if I could assume that the data in row [1] (the new data to be inserted) of the inserted table corresponds with the data in row [1] (the data that was deleted) in the deleted table.

Example:

Update people

Set person_id = (select person_id from inserted)

Where people.person_id = (select person_id from deleted)

This type of update statement will only work if there is a single row being updated. I wanted to step through the inserted and deleted tables one row at a time for multiple row updates, but I did not know if it was safe to say that the data in inserted row # corresponded with the data in deleted row #.

|||

> Update people

>

> Set person_id = (select person_id from inserted)

>

> Where people.person_id = (select person_id from deleted)

What table is this trigger attached to? People, or another table? Are you

just trying to undo the update to people, or replicate the update to another

table? In what scenario?

> This type of update statement will only work if there is a single row

> being updated.

Absolutely correct, and a very common tripping point for hundreds of people

before you.

> I wanted to step through the inserted and deleted tables

> one row at a time for multiple row updates

No, no, no. You are going about this all wrong. Think about it in SETS.

If you give some proper DDL and specs (see http://www.aspfaq.com/5006) we

can help you do this in one statement and abandon this idea of iterating

through every row and trying to match some hypothetical "row number"...

|||

> Does the data in the rows in the inserted and deleted tables always

> correspond? For instance, row 1 in inserted corresponds with row 1 in

> deleted.

There is no "row 1"... a table, by definition, is an unordered set of rows.

Typically you identify a row by some unique value, like a primary key, not

whether it came first or last or somewhere in between.

|||

Hello to everyone.

here i want to know some more details regarding inserted/deleted tables.

consider the scenario that more than 100 users are inserting/updating rows of same or othere tables of a database and tiggers of after update upon each insert and/or update is been fired.

what will be the response of the SQL 2005 server to these operations as i am moving the updated data to the audit tables from the delted table. by using the following trigger.

CREATE TRIGGER [TrigAUTblA]
ON [TblA]
AFTER UPDATE AS
BEGIN
INSERT INTO [TblAHistory]
(
[guidA],
[Description]
)
SELECT deleted.guidA,
deleted.Description
FROM deleted

Also what issues can emerge using this scenario

|||

It is possible to update the unique key for multiple rows in a table. In that case, there is nothing to correlate the rows in "inserted" to the rows in "deleted" other than the order in which they are returned by a select statement.

So the question is a valid one, I think: If a table contains one unique key, and multiple rows in that table are updated such that the value of that key changes, can we count on the rows in the "inserted" and "deleted" tables being returned in the same order so that they can be matched up one to one?

Thanks,

Ron