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
Thank You
Rollin Burr
Politicians and diapers have one thing in common. They should both be changed regularly, and for the same reason.