Transactions and getting next identity value on the fly

  • Thread starter Thread starter Kursat
  • Start date Start date
K

Kursat

Hi,

Consider these 2 ralated tables :

Users
------

UserId : int, identity
UserName : varchar

UserDetails
-------------

UserId : int, foreign key of users table
UserDetail : varchar

I would like to insert into these two tables within the same transaction
like this:

trans.Begin ();
try
{
insertUser(UserName);
insertUserDetail (UserId, UserDetails);
trans.Commit ();
}
catch
{
trans.RollBack();
}

The problem is that insertUserDetails function needs UserId of the newly
created user which is an identity value. I should get the next identity
value before commit. How can I do this with SQL Server and MS Access ?

Thanks in advance
 
Here is a TSQL workaround.

You're basically asking the question "How do I build relationships outside
of the database?"

Using Guid's is one answer.

........

Here is a different workaround.....but is TSQL, not based on 2 transactions:
My guess is that if you want to keep the two .Net transactions, you'll have
to go GUID based.






SET NOCOUNT ON
GO





DROP Table dbo.Person
GO


DROP Table dbo.Dept
GO


Create Table dbo.Dept
(
DeptID int primary key IDENTITY ( 101 , 1 ) ,
DeptName varchar(12) not null
)
GO



ALTER TABLE dbo.Dept
ADD CONSTRAINT DEPT_DEPTNAME_UNIQUE_CONSTR UNIQUE (DeptName)
GO



Create Table dbo.Person
(

PersonID int primary key IDENTITY ( 1001 , 1 ) ,
DeptID int not null FOREIGN KEY (DeptID) REFERENCES dbo.Dept(DeptID),
SSN varchar(9) not null ,
LastName varchar(12) not null ,
FirstName varchar(12) not null
)

GO




ALTER TABLE dbo.Person
ADD CONSTRAINT PER_SSN_UNIQUE UNIQUE (SSN)
GO




IF OBJECT_ID('tempdb..#TempDept') IS NOT NULL

begin

drop table #TempDept

end



IF OBJECT_ID('tempdb..#TempPerson') IS NOT NULL
begin
drop table #TempPerson
end

IF OBJECT_ID('tempdb..#AuditTablePerson') IS NOT NULL
begin
drop table #AuditTablePerson
end


IF OBJECT_ID('tempdb..#AuditTableDept') IS NOT NULL
begin
drop table #AuditTableDept
end






Create Table #TempPerson
(
PersonID int default 0 , --<<We don't know this one
FakeDeptID int ,
SSN varchar(9) not null ,
LastName varchar(12) not null ,
FirstName varchar(12) not null
)


Create Table #AuditTablePerson
(
SSN varchar(9) not null ,
NewIdentityPersonID int not null
)



Create Table #AuditTableDept
(
DeptName varchar(12) not null ,
NewIdentityDeptID int not null
)



Create Table #TempDept
(
FakeDeptID int not null ,
DeptName varchar(12) not null
)

GO


/*
Notice with the "-111" (and -222) I have a Relationship between the Person
and Dept, but this value
isn't the actual DeptID in the database. We just use this -111 (and -222)
as a holder for the relationship
until the IDENTITIES are actually created.
*/

Insert into #TempDept ( FakeDeptID , DeptName ) values ( -111 ,
'Resources' )
Insert into #TempDept ( FakeDeptID , DeptName ) values ( -222 ,
'Janitorial' )



Insert into #TempPerson ( FakeDeptID , SSN , LastName , FirstName ) values
( -111 , '222222222', 'Person2' , 'Two' )
Insert into #TempPerson ( FakeDeptID , SSN , LastName , FirstName ) values
( -111 , '333333333', 'Person3' , 'Three' )
Insert into #TempPerson ( FakeDeptID , SSN , LastName , FirstName ) values
( -222 , '444444444', 'Person4' , 'Four' )






print '/#TempPerson/'
select * from #TempPerson

print '/#TempDept/'
select * from #TempDept

print '------------<'


Insert Into dbo.Dept (DeptName)
output inserted.DeptName , inserted.DeptID into #AuditTableDept ( DeptName ,
NewIdentityDeptID )
select DeptName from #TempDept td
where not exists ( select null from dbo.Dept innerDept where
innerDept.DeptName = td.DeptName )

print '/#AuditTableDept/'
select * from #AuditTableDept



/*
--Here is where we can get the actual DeptID (created from the IDENTITY) by
using the FakeDeptID relationship we created and
--banking on that the DeptName's are unique
*/

/*
insert into dbo.Person (DeptID , SSN , LastName , FirstName)
output inserted.SSN , inserted.PersonID into #AuditTablePerson ( SSN ,
NewIdentityPersonID )
select ad.NewIdentityDeptID , SSN , LastName , FirstName from #TempPerson tp
join #TempDept td on tp.FakeDeptID = td.FakeDeptID
join #AuditTableDept ad on ad.DeptName = td.DeptName
*/

insert into dbo.Person (DeptID , SSN , LastName , FirstName)
output inserted.SSN , inserted.PersonID into #AuditTablePerson ( SSN ,
NewIdentityPersonID )
select d.DeptID , SSN , LastName , FirstName from #TempPerson tp
join #TempDept td on tp.FakeDeptID = td.FakeDeptID
join dbo.Dept d on d.DeptName = td.DeptName



print 'Hey, I have a list of the new personids as well'
select * from #AuditTablePerson

print 'Update the #TempPerson with the NewIdentityPersonID captured by the
output'

Update #TempPerson Set PersonID = at.NewIdentityPersonID
From #TempPerson tp , #AuditTablePerson at
Where tp.SSN = at.SSN





print '------------<<'
print 'This was stuff left over from a previous example...good for learning,
but you dont need it'
select * from #TempPerson

print '------------<<<'


-----------------
print ''
print '------------------'

print 'Did it work?'
Select p.SSN, d.DeptName from dbo.Person p join dbo.Dept d on p.DeptID =
d.DeptID
select * from dbo.Dept
select * from dbo.Person
print 'Sure Did!!'


IF OBJECT_ID('tempdb..#TempPerson') IS NOT NULL

begin

drop table #TempPerson

end

IF OBJECT_ID('tempdb..#AuditTablePerson') IS NOT NULL

begin

drop table #AuditTablePerson

end

IF OBJECT_ID('tempdb..#TempDept') IS NOT NULL

begin

drop table #TempDept

end



IF OBJECT_ID('tempdb..#AuditTableDept') IS NOT NULL

begin

drop table #AuditTableDept

end
 
Use transaction scope,

Call SP on Insert user first return ScopeIdentity. then insert UserDetails
using UserdetailsSP and the returned scopeidentity..

The fact this is a 1:1 releationship suggests to me this could be one table
in any case.
 
Paul said:
Use transaction scope,

Call SP on Insert user first return ScopeIdentity. then insert UserDetails
using UserdetailsSP and the returned scopeidentity..

The fact this is a 1:1 releationship suggests to me this could be one
table in any case.
Click to expand...

The relationship is not 1:1 and I want operations to be transactional. If I
use your technique then what should I do if db connection is down after I
insert into Users table but before inserting UserDetails? How can I solve
data inconsistency?
 
sloan said:
Here is a TSQL workaround.

You're basically asking the question "How do I build relationships
outside of the database?"

Using Guid's is one answer.

.......

Here is a different workaround.....but is TSQL, not based on 2
transactions:
My guess is that if you want to keep the two .Net transactions, you'll
have to go GUID based.






SET NOCOUNT ON
GO





DROP Table dbo.Person
GO


DROP Table dbo.Dept
GO


Create Table dbo.Dept
(
DeptID int primary key IDENTITY ( 101 , 1 ) ,
DeptName varchar(12) not null
)
GO



ALTER TABLE dbo.Dept
ADD CONSTRAINT DEPT_DEPTNAME_UNIQUE_CONSTR UNIQUE (DeptName)
GO



Create Table dbo.Person
(

PersonID int primary key IDENTITY ( 1001 , 1 ) ,
DeptID int not null FOREIGN KEY (DeptID) REFERENCES dbo.Dept(DeptID),
SSN varchar(9) not null ,
LastName varchar(12) not null ,
FirstName varchar(12) not null
)

GO




ALTER TABLE dbo.Person
ADD CONSTRAINT PER_SSN_UNIQUE UNIQUE (SSN)
GO




IF OBJECT_ID('tempdb..#TempDept') IS NOT NULL

begin

drop table #TempDept

end



IF OBJECT_ID('tempdb..#TempPerson') IS NOT NULL
begin
drop table #TempPerson
end

IF OBJECT_ID('tempdb..#AuditTablePerson') IS NOT NULL
begin
drop table #AuditTablePerson
end


IF OBJECT_ID('tempdb..#AuditTableDept') IS NOT NULL
begin
drop table #AuditTableDept
end






Create Table #TempPerson
(
PersonID int default 0 , --<<We don't know this one
FakeDeptID int ,
SSN varchar(9) not null ,
LastName varchar(12) not null ,
FirstName varchar(12) not null
)


Create Table #AuditTablePerson
(
SSN varchar(9) not null ,
NewIdentityPersonID int not null
)



Create Table #AuditTableDept
(
DeptName varchar(12) not null ,
NewIdentityDeptID int not null
)



Create Table #TempDept
(
FakeDeptID int not null ,
DeptName varchar(12) not null
)

GO


/*
Notice with the "-111" (and -222) I have a Relationship between the Person
and Dept, but this value
isn't the actual DeptID in the database. We just use this -111 (and -222)
as a holder for the relationship
until the IDENTITIES are actually created.
*/

Insert into #TempDept ( FakeDeptID , DeptName ) values ( -111 ,
'Resources' )
Insert into #TempDept ( FakeDeptID , DeptName ) values ( -222 ,
'Janitorial' )



Insert into #TempPerson ( FakeDeptID , SSN , LastName , FirstName )
values ( -111 , '222222222', 'Person2' , 'Two' )
Insert into #TempPerson ( FakeDeptID , SSN , LastName , FirstName )
values ( -111 , '333333333', 'Person3' , 'Three' )
Insert into #TempPerson ( FakeDeptID , SSN , LastName , FirstName )
values ( -222 , '444444444', 'Person4' , 'Four' )






print '/#TempPerson/'
select * from #TempPerson

print '/#TempDept/'
select * from #TempDept

print '------------<'


Insert Into dbo.Dept (DeptName)
output inserted.DeptName , inserted.DeptID into #AuditTableDept ( DeptName
, NewIdentityDeptID )
select DeptName from #TempDept td
where not exists ( select null from dbo.Dept innerDept where
innerDept.DeptName = td.DeptName )

print '/#AuditTableDept/'
select * from #AuditTableDept



/*
--Here is where we can get the actual DeptID (created from the IDENTITY)
by using the FakeDeptID relationship we created and
--banking on that the DeptName's are unique
*/

/*
insert into dbo.Person (DeptID , SSN , LastName , FirstName)
output inserted.SSN , inserted.PersonID into #AuditTablePerson ( SSN ,
NewIdentityPersonID )
select ad.NewIdentityDeptID , SSN , LastName , FirstName from #TempPerson
tp
join #TempDept td on tp.FakeDeptID = td.FakeDeptID
join #AuditTableDept ad on ad.DeptName = td.DeptName
*/

insert into dbo.Person (DeptID , SSN , LastName , FirstName)
output inserted.SSN , inserted.PersonID into #AuditTablePerson ( SSN ,
NewIdentityPersonID )
select d.DeptID , SSN , LastName , FirstName from #TempPerson tp
join #TempDept td on tp.FakeDeptID = td.FakeDeptID
join dbo.Dept d on d.DeptName = td.DeptName



print 'Hey, I have a list of the new personids as well'
select * from #AuditTablePerson

print 'Update the #TempPerson with the NewIdentityPersonID captured by the
output'

Update #TempPerson Set PersonID = at.NewIdentityPersonID
From #TempPerson tp , #AuditTablePerson at
Where tp.SSN = at.SSN





print '------------<<'
print 'This was stuff left over from a previous example...good for
learning, but you dont need it'
select * from #TempPerson

print '------------<<<'


-----------------
print ''
print '------------------'

print 'Did it work?'
Select p.SSN, d.DeptName from dbo.Person p join dbo.Dept d on p.DeptID =
d.DeptID
select * from dbo.Dept
select * from dbo.Person
print 'Sure Did!!'


IF OBJECT_ID('tempdb..#TempPerson') IS NOT NULL

begin

drop table #TempPerson

end

IF OBJECT_ID('tempdb..#AuditTablePerson') IS NOT NULL

begin

drop table #AuditTablePerson

end

IF OBJECT_ID('tempdb..#TempDept') IS NOT NULL

begin

drop table #TempDept

end



IF OBJECT_ID('tempdb..#AuditTableDept') IS NOT NULL

begin

drop table #AuditTableDept

end
Click to expand...

Two transactions ? I have never mention two transactions. What I try to do
is so simple, I want to do what I write in the below pseudocode in
transactional manner (i.e. committed or rolledback together):

BeginTrans;
try
{
insertIntoToTableA
insertIntoTableBWith
CommitTrans;
}
catch
{
RollbackTrans
}

The problem is how should I get identity column's value from tableA.
 
Back
Top