TMsStoredProc not working with Transactions?

cfrazier007 · Post by **cfrazier007** » Tue 20 Jan 2009 15:28

EMSError - "The statement has been terminated. The INSERT statement conflicted with the FOREIGN KEY constraint 'fkHSTCPD_HSTALL'.
The conflict occurred in database 'ds_WMS_dev', table 'dbo.HSTALL', column 'HSTALLID'."

I have 2 TMSStoredProcs that call SPs to insert records into a Parent and Child Table.

1st inserts into Parent table (HSTALL). It also returns the primary key (autoinc) of the table.

2nd inserts into child table (HSTCPD), using the HSTALLID from the 1st as a parameter.

This code works fine outside of a transaction. However, when I use transactions (SqlConnection.StartTransaction), I get the error above.

I am using Delphi 2007 and I updated to 4.70.0.44 this morning.

Dimon · Post by **Dimon** » Wed 21 Jan 2009 12:59

I can not reproduce the problem.
Please send me a complete small sample to dmitryg*devart*com to demonstrate it, including a script to create and fill tables and stored procedures.

cfrazier007 · Post by **cfrazier007** » Wed 21 Jan 2009 16:20

I think I found the problem. We had an After Insert trigger on the main table. the Identity we were returning was not scoped (@@IDENTITY).

Thanks for the help.