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

Cheersyou 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|||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|||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.|||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.|||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.|||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|||brilliant!! Thanks all for your help - it is crystal clear now.

Much appreciated!