Information générale
Forum:
Microsoft SQL Server
Titre:
I can use SCOPE_IDENTITY() with INSTEAD OF INSERT on a view?
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
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement