Could you call a stored procedure to do the insert instead of trying to make use of the view and the INSTEAD OF trigger?
-Mike
>Hi,
>
>i have one view VW1 defined as join of two tables T1 and T2.
>
>T1 have a 'id' field IDENTITY.
>T2 have a 'idRef' field in relation 1-1 to T1.id.
>
>I need INSERT one row on View, and split view fields on T1 and T2.
>
>I define one TRIGGER vw1Insert ON dbo.VW1 INSTEAD OF INSERT AS ....
>
>My problem:
>i want new id (identity) value insert into T1 in the trigger.
>
>Because INSERT commands exec on the trigger can fired other trigger or sp that do other INSERT command on other table with identity, i cannot use
>@@IDENTITY.
>
>I cannot use IDENT_CURRENT('table_name') because i want lock T1 for INSERT command on other sessions.
>
>I use SCOPE_IDENTITY() but it return NULL!
>
>I think this because view is ambiguos, but i test SCOPE_IDENTITY() with
>INSTEAD OF INSERT for a table. result is NULL.
>
>Fabio