But it doesn't work with instead of insert triggers because they are executed in a different scope then the insert itself and the SCOPE_IDENTITY() function returns null after insert.
We use adapter.save(Entity, true) method and it fails because select that refeches values after insert fails and the because of that whole transaction fails.
The folowing sql statements are result of calling an adapter.save(entity, true) (sql profiler):
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION [RecursiveSave]
declare @P1 numeric(15,1)
set @P1=NULL
exec sp_executesql N'INSERT INTO [BLAGAJNA].[dbo].[dktDokumenti]
([Vdk_Id],[Ojd_Id],[Vlt_Id],[Npl_Id],[Prt_Id],[Vpd_Id],[Blg_Id],[PoslovnaGodina]
,[Broj],[Datum]) VALUES
(@Vdk_Id,@Ojd_Id,@Vlt_Id,@Npl_Id,@Prt_Id,@Vpd_Id,@Blg_Id,@PoslovnaGodina,@Broj,@
Datum);SELECT @Id=SCOPE_IDENTITY()', N'@Id decimal(15,1) output,@Vdk_Id
decimal(15,1),@Ojd_Id decimal(15,1),@Vlt_Id decimal(15,1),@Npl_Id
decimal(15,1),@Prt_Id decimal(15,1),@Vpd_Id decimal(15,1),@Blg_Id
decimal(15,1),@PoslovnaGodina decimal(15,1),@Broj int,@Datum smalldatetime', @Id
= @P1 output, @Vdk_Id = 1.0, @Ojd_Id = 1.0, @Vlt_Id = 1.0, @Npl_Id = 1.0,
@Prt_Id = 45738.0, @Vpd_Id = 1.0, @Blg_Id = 1.0, @PoslovnaGodina = 2004.0, @Broj
= 99, @Datum = 'svi 7 2004 6:26PM'
select @P1
exec sp_executesql N'SELECT
[BLAGAJNA].[dbo].[dktDokumenti].[Id],[BLAGAJNA].[dbo].[dktDokumenti].[Vdk_Id],[B
LAGAJNA].[dbo].[dktDokumenti].[Ojd_Id],[BLAGAJNA].[dbo].[dktDokumenti].[Vlt_Id],
[BLAGAJNA].[dbo].[dktDokumenti].[Npl_Id],[BLAGAJNA].[dbo].[dktDokumenti].[Prt_Id
],[BLAGAJNA].[dbo].[dktDokumenti].[Vpd_Id],[BLAGAJNA].[dbo].[dktDokumenti].[Blg_
Id],[BLAGAJNA].[dbo].[dktDokumenti].[PoslovnaGodina],[BLAGAJNA].[dbo].[dktDokume
nti].[Broj],[BLAGAJNA].[dbo].[dktDokumenti].[Datum],[BLAGAJNA].[dbo].[dktDokumen
ti].[DatumDospijeca],[BLAGAJNA].[dbo].[dktDokumenti].[VrijediDo],[BLAGAJNA].[dbo
].[dktDokumenti].[VanjskiDokument],[BLAGAJNA].[dbo].[dktDokumenti].[Napomena],[B
LAGAJNA].[dbo].[dktDokumenti].[Timestamp],[BLAGAJNA].[dbo].[dktDokumenti].[Koris
nikUnosa],[BLAGAJNA].[dbo].[dktDokumenti].[VrijemeUnosa],[BLAGAJNA].[dbo].[dktDo
kumenti].[KorisnikPromjene],[BLAGAJNA].[dbo].[dktDokumenti].[VrijemePromjene]
FROM [BLAGAJNA].[dbo].[dktDokumenti] WHERE (
[BLAGAJNA].[dbo].[dktDokumenti].[Id] = @Id1)', N'@Id1 decimal(15,1)', @Id1 =
NULL
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
...and so you can see that the scope identity() function returns null...