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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-05-16 : 09:36:23
|
| Adrian writes "I have 2 tables: A and B.A has a structure like: ID int(4) indentity(1,1) PRIMARY KeyInfoA varchar(100)B has a structure like this:IDA int(4)InfoB varchar(100)When a piece of information is inserted into InfoA field in table A, I need that the InfoB information to be inserted into table B and IDA to be set to the identity of the last row inserted in table A (the one with the InfoA inserted).Also it is not correct to have information into TableB without a corresponding record into table a, we have a one-to-many relationship (for one Info A we can have any number of InfoB into table B)To accomplish this i am using a stored procedure designed in the following way:Procedure1(@InfoA,@InfoB)...BEGIN TRANINSERT INTO TableA(InfoA) VALUES(@InfoA)IF @@ERROR<>0 BEGINROLLBACK TRANRETURNENDSET @ID=@@IDENTITYINSERT INTO TableB(InfoB,IDA) VALUES(@InfoB,@ID)IF @@ERROR<>0 BEGINROLLBACK TRANRETURNENDCommit Tran...The problem is that the ID returned from the identity and inserted into TableB.IDA is not correct and it differs from the ID of the information contained in column TableA.ID.What is the explanation for this behaviour and what other options are to be considered in this case?Thanks,Adrian Constantinescu" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-16 : 11:54:27
|
| Only way I think this could happen is if there is a trigger on TableA which affects @@identity. Try SCOPE_IDENTITY( ) instead.==========================================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. |
 |
|
|
|
|
|
using @@identity in transaction blocks