none
inserted/deleted tables

    Question

  • 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,

    Wednesday, November 30, 2005 2:01 PM

Answers

  • > 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. 
    
    
    

    Wednesday, November 30, 2005 7:54 PM

All replies

  • 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

    Wednesday, November 30, 2005 2:36 PM
  • 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 #.

    Wednesday, November 30, 2005 3:58 PM
  • > 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"... 
    
    
    

    Wednesday, November 30, 2005 7:52 PM
  • > 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. 
    
    
    

    Wednesday, November 30, 2005 7:54 PM
  • 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

    Monday, December 11, 2006 7:31 AM
  •  

    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

    Monday, December 11, 2006 6:49 PM