njfuller (2/6/2013)
I'll back up a bit.In a single transaction, a material and a material_prod_version record will be inserted.
Each record has its own identity column.
The material_prod_version record has a column for the identity value of the material, but it is not filled.
I would like that column to be filled with the identity of the material so that I can do a join based on the pk, rather than the business keys.
It seems that since this is all done in a single transaction, that the data would be available to me somehow in the trigger.
The only reason I'd like to get the identity at the time of insert, is that it saves me a read to the database to retrieve the data.
The problem is the trigger code you posted will only work for single row inserts. It doesn't matter if today you have code that only does this one at a time. The time will come when multiple row inserts will happen and your trigger will leave you helpless. Triggers must be able to handle multiple row transactions. All the data is available you just have to learn how to best handle it. It is hard for us to help with the code because we don't have the table structure, sample data or desired output. Please take a few minutes and look at the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/