Cant get identity after Insert into the view

[elf2k]

Hi, sorry for my english
I have SDAC 4.35+Delphi 6+MS SQL2k Ent sp4
After insert into view, record dosn't refresh.

Code: Select all

CREATE TABLE [dbo].[WareHouse] (
	[idWareHouse] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [varchar] (64) 
) ON [PRIMARY]
GO
CREATE view vWarehouse with view_metadata
as
select [idWarehouse], [Name]
  from Warehouse
GO
CREATE trigger trWarehouse_INSERT on vWarehouse
instead of insert
as
set nocount on
insert Warehouse([idManager], [Name])
  select [idManager], [Name] 
    from Inserted
set nocount off
GO
CREATE PROCEDURE upWarehouse_GetList
  @idWarehouse int=null
as
select [idWarehouse], [Name]
  from vWarehouse
  where isnull(@idWarehouse, idWarehouse) = idWarehouse
GO

Code: Select all

object sp: TMSStoredProc
    StoredProcName = 'upWarehouse_GetList;1'
    SQLInsert.Strings = (
      'INSERT INTO vWarehouse'
      '  (idWarehouse, Name)'
      'VALUES'
      '  (:idWarehouse, :Name)'
      'SET :idWarehouse = SCOPE_IDENTITY()')
    SQLRefresh.Strings = (
      'exec upWarehouse_GetList :idWarehouse')
    Connection = MSConnection1
    SQL.Strings = (
      '{:RETURN_VALUE = CALL upWarehouse_GetList;1(:idWarehouse)}')
    BeforeUpdateExecute = spBeforeUpdateExecute
    Options.UniqueRecords = True
    Options.QueryIdentity = True
    Options.ReturnParams = True
    RefreshOptions = [roAfterInsert]
    UpdatingTable = 'vWarehouse'
end

Insert record:

Code: Select all

sp.Insert;
  spidWarehouse.Value:=-1; //Set identity, because insert into view. 
  spName.Value:='Test'+DateTimeToStr(Now);
  sp.Post;

After insert I have error RefreshFailed. Found 0 records.
In profiler I see:

Code: Select all

declare @P1 int
set @P1=-1
declare @P2 int
set @P2=NULL
exec sp_executesql N'INSERT INTO vWarehouse
  (idWarehouse, Name)
VALUES
  (@P1, @P2)
SET @P3 = SCOPE_IDENTITY()', N'@P1 int OUTPUT,@P2 varchar(23),@P3 int OUTPUT', @P1 output, 'Test23.06.2008 17:51:08', @P2 output
select @P1, @P2

Why :idWarehouse don't set to new actual value? How to correct this?

[Antaeus]

You should do the following:

1. Place this code in the BeforeUpdateExecute event of your stored procedure

Code: Select all

    if stInsert in StatementTypes then
      Params.ParamByName('idWarehouse').ParamType := ptInputOutput;

2. Replace this line in SQLInsert:

Code: Select all

SET :idWarehouse = SCOPE_IDENTITY()

with this one:

Code: Select all

SET :idWarehouse = @@IDENTITY 

For more information about difference between SCOPE_IDENTITY() and @@IDENTITY you can read in this topic of MSDN.

[elf2k]

May be use of IDENT_CURRENT(UpdatingTable) - is better decision?
@@identity can return identity from another table?

When I left SQLInsert clean, it generated automatically.
Can I get this script in run-time and append

Code: Select all

SET :idWarehouse = @@IDENTITY 

?

[Antaeus]

>May be use of IDENT_CURRENT(UpdatingTable) - is better decision?
I think that you should decide by yourself whether of these statements to use.

>@@identity can return identity from another table?
Yes, it can.

> Can I get this script in run-time and append
There is no simple way to do this. You should write this statement at design time.