Transaction across multiple procedures and FKs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Transaction across multiple procedures and FKs

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2004-10-16 : 16:47:01

Is it possible to run multiple stored procedures within a transaction, and not violate my foreign key constraints?

I have something like this:


begin tran

declare @P1 int
set @P1=0
exec cor_UpsertPartnerNote @NoteID = @P1 output, @PostDate = N'10/16/2004 01:23:51 PM', @NoteText = N'A new note.', @PostedByUserID = N'1', @PartnerID = N'2'
select @P1

exec cor_UpdatePartnerNoteVisibility @NoteID = @P1, @Visibility = N'1'

if (@@ERROR != 0) begin
	rollback
end else begin
	commit
end

Whenever I try to run the above code, where the first proc inserts a new note, and returns the ID, I get the following message when the second proc is run:

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_cor_PartnerNoteVisibility_cor_PartnerNotes'. The conflict occurred in database 'junk', table 'cor_PartnerNotes', column 'NoteID'.
The statement has been terminated.

Is what I'm trying to do possible? Because basically I don't want any of the data inserted if the second proc fails.

Thanks,
Steve

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-17 : 10:09:23

What you are trying to do is possible but you have a number of problems with the implementation.
@@error is reset by every sql statement so you have to check it after both sp calls - and the SPs will also have to check it after ever statement, re-raise the error and immediately return. They could also set a return code which is checked by your controlling sp.

The error is because you are trying to insert a noteid in a referncing table and the value isn't in cor_PartnerNotes.NoteID.
Looking at your code here it should work and you seem to be checking @P1 - put in a query to make sure that the record exists to ake sure. If that's ok it is cor_UpdatePartnerNoteVisibility that is inserting the wrong value.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Blastrix
Posting Yak Master

208 Posts

Posted - 2004-10-20 : 14:52:35

You were right...it was a completely boneheaded move on my part. The second procedure performed a DELETE, but was accidentally performing it on the wrong table(the one I just inserted into). I glanced over that bit of code I don't know how many times not even realizing it was the offending bit.

Thanks,
Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources