Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38

    Unanswered: Inserted Table when Inserting

    Hi,

    Now thanks to you good folks on here, I have recently found out that when inserting data into table, there is a system table which can be queried with triggers - specifically called "Inserted".

    What I am wondering is what are the limitations of what I can do with the data in this table?
    I know I can query it from within the trigger, but can I update data specifically in this table before it is inserted?
    (ie IF field1 FROM inserted = 'blah' UPDATE inserted SET field2 = 'something')

    If so is there anything that I need to look out for? Concerns? Etc?

    Thanks in advance for your help

    Cheers
    Sideways see's more scenery

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    you can use the data for comparisons or you can join the query argument in the trigger to the inserted and or the deleted tables

    I have never updated them directly so i cant speak to that but i can suggest that anything that you might want to change in these virtual tables (Inserted\Deleted) could just as easily be changed in the triggered or evaluated table directly from the trigger code.

    remember these tables contain data to give you a before and after look at the transaction that the trigger is a part of
    (a trigger is implicitly part of the X-act that calls it)
    so they dont technically exist when you are not in a X-act

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It is a very bad idea to try to modify either INSERTED or DELETED directly, they are implemented in "curious" ways. While you might be able to update them, it is still a very bad idea to do it.

    -PatP

  4. #4
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38

    Question

    Ahh haaa so if I understand correctly - what you are basically saying is that the information contained in this table, is ALREADY inserted into the table.
    So if the file I was inserting had a PK field = 1234, and I wanted to update something in this file once it was inserted I could say something to the effect of:

    update table1
    set field1 = blah
    from table1
    where table1.field2 = inserted.field2

    Rather than:

    update inserted
    set field1 = blah
    from inserted

    Hmm hopefully I have made a bit of sense here.....

    Thanks.
    Sideways see's more scenery

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Originally posted by Pat Phelan
    It is a very bad idea to try to modify either INSERTED or DELETED directly, they are implemented in "curious" ways. While you might be able to update them, it is still a very bad idea to do it.

    -PatP
    The logical tables INSERTED and DELETED cannot be updated.

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    E3xtc

    yes that is the case
    basically when you perform an insert on a table that has a trigger on it (for insert)
    1 the row is inserted to the table
    2 the row is also added into the "inserted" table
    (which is only available to the xact that calls it)
    3 the trigger actions are executed
    4 commit or rollback

    for deleted the same actions occur except the row to be deleted is added to the deleted table.

    an update (in some cases) is a insert and a delete so there is no actual "updated" table
    on an update the row as it existed before the update is added to the "deleted" table and the row with the updated column is added to the "inserted" table.

    while the table exists(during trigger execution) you can query it just as you would any table.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by E3xtc
    Ahh haaa so if I understand correctly - what you are basically saying is that the information contained in this table, is ALREADY inserted into the table.
    Yep, that you did understand that correctly!

    The rows are modified first, placed in a pair of "non-corporeal" tables named INSERTED and DELETED. These tables can be freely modified in an INSTEAD OF trigger if the database compatibility level is set to 80. In the first releases of sp1 and sp3, and in several PSS hot fixes you could update the INSERTED and DELETED tables in any kind of trigger, with any database compatibility level. It is still a bad idea!

    In general, it is considered "good form" to use a JOIN back to the primary (host) table to change the values of columns. This becomes much more important in the 64 bit version of SQL 2000, and will be even more so in Yukon.

    -PatP

  8. #8
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38
    brilliant!! Thanks all for your help - it is crystal clear now.

    Much appreciated!
    Sideways see's more scenery

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •