Proposal for MSSQL issue getting identity column
Posted: 15.11.2014, 14:04
On MSSQL we can use 3 ways of getting last inserted identity in a table
1.@@IDENTITY which gets last inserted identity
2. SCOPE_IDENTITY() which gets last inserted identity in current scope
3. IDENT_CURRENT( 'table_name' ) which gets the last identity value generated for a specified table or view.
If we have a table let's say PEOPLE with IDPeople int identity(1,1) all 3 functions return the same value if we are doing a simple insert.
If we have also a trigger that logs the operations made to the PEOPLE table into a separate log table which has also an identity column then 1st function will return the id inserted in the log table and
the other 2 function will return the last IDPeople inserted in the PEOPLE table.
My proposal is to use then SCOPE_IDENTITY function instead of @@IDENTITY function for getting the wright identity in the table used.
The 3rd function is the best option but we have to get current table name in order to use it.
I replaced the @@Identity with SCOPE_IDENTITY() for example in ZDbcDbLibResultSet.pas
What is your opinion regarding this ?
1.@@IDENTITY which gets last inserted identity
2. SCOPE_IDENTITY() which gets last inserted identity in current scope
3. IDENT_CURRENT( 'table_name' ) which gets the last identity value generated for a specified table or view.
If we have a table let's say PEOPLE with IDPeople int identity(1,1) all 3 functions return the same value if we are doing a simple insert.
If we have also a trigger that logs the operations made to the PEOPLE table into a separate log table which has also an identity column then 1st function will return the id inserted in the log table and
the other 2 function will return the last IDPeople inserted in the PEOPLE table.
My proposal is to use then SCOPE_IDENTITY function instead of @@IDENTITY function for getting the wright identity in the table used.
The 3rd function is the best option but we have to get current table name in order to use it.
I replaced the @@Identity with SCOPE_IDENTITY() for example in ZDbcDbLibResultSet.pas
Code: Select all
procedure TZDBLibCachedResolver.PostUpdates(Sender: IZCachedResultSet;
UpdateType: TZRowUpdateType; OldRowAccessor, NewRowAccessor: TZRowAccessor);
var
Statement: IZStatement;
ResultSet: IZResultSet;
I: Integer;
begin
inherited PostUpdates(Sender, UpdateType, OldRowAccessor, NewRowAccessor);
{ Defines an index of autoincrement field. }
if FAutoColumnIndex = -1 then
for I := FirstDbcIndex to Metadata.GetColumnCount{$IFDEF GENERIC_INDEX}-1{$ENDIF} do
if Metadata.IsAutoIncrement(I) then
begin
FAutoColumnIndex := I;
Break;
end;
if (UpdateType = utInserted) and (FAutoColumnIndex > InvalidDbcIndex)
and OldRowAccessor.IsNull(FAutoColumnIndex) then
begin
Statement := Connection.CreateStatement;
ResultSet := Statement.ExecuteQuery('SELECT SCOPE_IDENTITY()');
try
if ResultSet.Next then
NewRowAccessor.SetLong(FAutoColumnIndex, ResultSet.GetLong(FirstDbcIndex));
finally
ResultSet.Close;
Statement.Close;
end;
end;
end;