Parent-Child Insertion, SQLTransaction, and Foreign Key error

[Guest]

Hi,
I have an application that was working fine since last two months but all of
a sudden it stopped working!!!

This is what I do in my code:

(1) Open the connection
(2) Start the transaction and get the SQLTransaction object
(3) Call ProcedureA to insert the parent record and get the newly generated id
(4) Call ProcedureB to insert the child record and pass that procedure the
parent id generated in step 3.

and boom! the following error occurs:

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'p_p_fk2'.
The conflict occurred in database 'MyDB', table 'ChildTable', column
'parent_id'.\r\nThe statement has been terminated."

Looks like the 2nd procedure call does not see the newly generated id; it
should see the id b/c both of the procedures are being run under the same
transaction! since it does not see the id, hence I am thinking may be
procedure are not running under the same transaction for some reason or may
be some server setting changed last night which is causing this problem!

Please note that the code has been working fine since last two months and
stopped working all of a sudden!

Can you guys think of anything that would have triggered this behavior.
Please help.

Thanks,
Mansoor Siddiqui

 

[Marina Levit [MVP]]

I don't think this is a transaction scope issue. At least it doesn't sound
like one. If B was not running under the same transaction, you would
typically get a deadlock scenario because B is waiting for A to commit, but
of course A never does because the code is trying to run B.

It sounds like you are calling A from code then B from code. And it sounds
like B does not get a valid parent_id. It sounds like either A is not
correctly returnin the new parent_id, or the code is not correctly relaying
that parent_id to B.

Are you sure no one changed the code recently? Verify, that B is getting the
correct parent_id.

 

[Guest]

Yep, you are right!

After debugging and pulling hair for couple of hours, we came to know that
one of our co-workers put a new trigger on the parent table and that messed
up everything.

Before adding that trigger to the parent table, ProcedureA used to return
only one id, so we used SQLCommand.ExecuteScalar method;

After adding that trigger, ProcedureA was returning two recordsets of one
column each; first was from the trigger and the second (which used to be the
first and the only recordset) was from the procedure itself! Since our
ADO.NET code was calling ExecuteScalar, hence we were picking the wrong id
and were using that to insert the child record, hence the foreign key error.

Oh God, that was tricky to say the least and we had a tough time figure out
whats going on. I learnt two lessons from that:

(1) Don't use triggers @ all.
(2) Use explicit output parameters for returning singular values from the
procedure

Thanks for your help.

Mansoor Siddiqui

 

[Brian Smith]

There is a reliable way to fix that, regardless of whether you have
triggers or not.
e.g.
INSERT MyTable ....

-- get new IDENTITY value
SET @newId = SCOPE_IDENTITY()

-- refresh dataset
SELECT * FROM MyTable
WHERE Id = @newID

-- return new Id
RETURN @newID

Of course, if you have non integer keys then you'll have to use an
output parameter, although the SELECT fixes the problem for the dataset
(assuming you have a relation defined)

brian