>> i want identity out of the trigger.
SCOPE_IDENTITY() is not going to work for you. It returns the identity value that was assigned within the scope of the current procedure, in this case, the procedure is the trigger. I don't have a realiable way for you to do this except to write the identity value to a temp table that you can then reference outside of the trigger. My fear is that your performance my suffer severly by this approach.
Again, if you use a stored procedure to do the insert into the two tables, everything will be much easier - at least, from the database's POV.
-Mike
>Hi Michael,
>
>I don't have problem into the trigger, i want identity out of the trigger.
>
>If TRIGGER is FOR (AFTER) type, SCOPE_IDENTITY() return id insert on the table where i exec INSERT, but if i define a INSTEAD trigger, SCOPE_IDENTITY() return NULL.
>
>Example:
>
>-- Batch
>
>INSERT INTO tableWithInsteadInsertTrigger VALUES (....)
>SELECT SCOPE_IDENTITY()
>
>
>Note*. This fired a INSTEAD OF INSERT trigger, and on this a INSERT command is done (within INSTEAD SCOPE_IDENTITY is ok )
>
>Fabio