Very Computer

by **Rehan Chawdr** » Fri, 20 Jun 1997 04:00:00

Say we have the following:

****************************************************
Table A (has an identity field)
-----------
Table_A_Insert_Trigger -> Inserts an audit log in Table B (has an identity
field)

Now, say we have a stored procedure that inserts a new row into Table A,
and the same stored proc returns as an output parameter the newly inserted
identity field from table A.
******************************************************

field is overwritten by the result of the insert trigger on A.

-- Rehan --

by **Wilfred van Dij** » Fri, 20 Jun 1997 04:00:00

> Say we have the following:

> ****************************************************
> Table A (has an identity field)
> -----------
> Table_A_Insert_Trigger -> Inserts an audit log in Table B (has an
> identity
> field)

> Now, say we have a stored procedure that inserts a new row into Table
> A,
> and the same stored proc returns as an output parameter the newly
> inserted
> identity field from table A.
> ******************************************************

> The problem that we have been having is that in MSSQL 6.5, the

> field is overwritten by the result of the insert trigger on A.

> that

> -- Rehan --

value in a variable in the stored procedure and return this variable
from the SP.

If this won't work ask SQL server the column value (in the following
example called ID) , with a criteria you used in your insert command

sp_yourproc "Peters","1035XR",150.35

in sp_yourproc:

insert into table_a_ ...
select id from table_a_ where ZIP = "1035XR"

Wilfred van Dijk

by **Nayan Rava** » Fri, 20 Jun 1997 04:00:00

>Say we have the following:

>****************************************************
>Table A (has an identity field)
>-----------
> Table_A_Insert_Trigger -> Inserts an audit log in Table B (has an identity
>field)

>Now, say we have a stored procedure that inserts a new row into Table A,
>and the same stored proc returns as an output parameter the newly inserted
>identity field from table A.
>******************************************************

>field is overwritten by the result of the insert trigger on A.

> -- Rehan --

Hi Rehan,

Microsoft Technet article Q163446 gives a solution to this problem.
Basically, it suggests modifying your Table_A_Insert_Trigger to first

Table B. Your insert stored procedure should read the identity value out

HTH
--
Nayan Raval

Very Computer

How to save an @@IDENTITY field for multiple updates

How to save an @@IDENTITY field for multiple updates

How to save an @@IDENTITY field for multiple updates

How to save an @@IDENTITY field for multiple updates