I am using several INSERT statments in an INSTEAD OF Trigger on a view. There is more than one table in the view, and they are linked in a simple parent-child relationship.

Variables are declared, and values are set. The problem seems to be with the @@IDENTITY not returning the identity of the insert statment before. I.E.:

INSERT INTO Table1 (Value1, ...)
VALUES (@Value1, ...)

SET @Table1ID = (SELECT @@Identity)

INSERT INTO Table2 (Table1ID, ...)
VALUES (@Table1ID, ...)

This gives the error:

Cannot insert the value NULL into column 'Table1ID', table 'dbo.Table2'; column does not allow nulls. Insert fails.

Is there a built in transaction that is nt allowing the actual insert to occur until all inserts are validated, and therefore there is no value for @@IDENTITY? If so, how can I get the identity for the first insert so that I can place it in the next insert statement?

Thank You in advance!

Rollin