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


preface:
Table1 have not insert triggers, otherwise the problem is more complex.

rewrite the instead trigger with this:

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

SELECT @Table1ID = @@Identity

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

-- if you have only Table2 is sufficient
INSERT INTO Table2 (Table1ID, ...)
VALUES (@@Identity, ...)

Now you can update the view with two way:
way 1:

-- view is
-- SELECT Table1ID,.... FROM Table1
-- BECAUSE identity is not nullable and SQL check it before the trigger ( a bug for me ),
-- it fire a error for view.Table1ID fields
INSERT view (Table1ID,... ) VALUES (0,)
SELECT @@IDENTITY  -- THIS RETURN CORRECT NEW VALUE if Table2 have not triggered insert

way 2:

-- redefine the view with this
SELECT NULLIF(Table1ID,NULL) AS Table1ID,.... FROM Table1
-- Now the view field is nullable, and then you can
-- update the view without fill the identity field:
INSERT view (... ) VALUES (...)
SELECT @@IDENTITY  -- THIS RETURN CORRECT NEW VALUE if Table2 have not triggered insert

Fabio