[SOLVED] Instead Of Insert Trigger, Refetch, Adapter

Posts   
 
    
Devildog74
User
Posts: 719
Joined: 04-Feb-2004
# Posted on: 25-Apr-2004 05:06:31   

I have written an Instead Of Insert trigger that does some date validation. I have also written a save routine in my business facade, that takes a new entity object and saves it to the database.

When I overload the data access adapter to Refetch the data, I receive the following exception:

{SD.LLBLGen.Pro.ORMSupportClasses.ORMConcurrencyException}
    [SD.LLBLGen.Pro.ORMSupportClasses.ORMConcurrencyException]: {SD.LLBLGen.Pro.ORMSupportClasses.ORMConcurrencyException}
    HelpLink: Nothing
    InnerException: Nothing
    Message: "During a recursive save action an entity's save action failed. The entity which failed is enclosed."
    Source: "SD.LLBLGen.Pro.ORMSupportClasses.NET11"
    StackTrace: "   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave)
   at EDS.DNN2.Hotels.Facade.Lists.SaveData(EntityBase2 Entity, Boolean RefetchAfterSave) in C:\Documents and Settings\cbertolasio.EDEVS\My Documents\Visual Studio Projects\HotelManagement\EDS.DNN2.Hotels.Facade\Lists.vb:line 49
   at EDS.DNN2.UI.HotelAdmin.EditSeasons.Update_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\DnnFramework2\DotNetNuke20003\DesktopModules\EDS.DNN2.UI.HotelAdmin\EditSeasons.ascx.vb:line 124"
    TargetSite: {System.Reflection.RuntimeMethodInfo}

The data is still saved in the database, but the ORM doesnt know about it, and I suppose that is why this exception is being thrown.

If I set the refetch option to false, then all is well. If I understand the adapter model, if I need to get the new identity value into the entity after an insert operation, one method would be to use the refetch overload of the data access adapter.

Since this exception is being thrown when refetch = true, should I just issue another fetch operation, and not use the overload?

Also is this exception normal and by design, or would you consider this to be a bug?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 28-Apr-2004 10:41:41   

Save fails for various reasons, and it is also considered a 'fail' if the refetch action fails (which is the case in your situation). This means that when the refetch fails, the save fails, and when the save fails, the transaction is rolled back.

Frans Bouma | Lead developer LLBLGen Pro
Devildog74
User
Posts: 719
Joined: 04-Feb-2004
# Posted on: 28-Apr-2004 12:30:55   

Well, the thing is that the save should not be failing.

If the TSQL captured from SQL Profiler is run in Query Analyzer, the sp_execsql command works just fine.

InsteadOf triggers work slightly different from normal For Insert triggers. Thats probably the root of the issue.

I just find it curious as to why the refetch would be failing.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 28-Apr-2004 12:50:52   

Does the row which is inserted have an identity key ? If so it might be that the value assigned isn't propagated back. If that's the case, I still find it weird that the SCOPE_IDENTITY() select doesn't propagate the value back.

However what exactly did you override? SaveEntity() ? If so, what exactly did you try to 'inject' into the SaveEntity() logic? Perhaps the OnSaveEntity() methods are more appropriate for your code (or even better: an IEntityValidator implementation)

Also, could you please check what the output parameter is for the identity column in the query passed to OnSaveEntityComplete() ? (override the method in the derived class from DataAccessAdapter). and if the PK has now a value, so the fetch routine knows what to fetch.

Frans Bouma | Lead developer LLBLGen Pro
SaX
User
Posts: 42
Joined: 29-Apr-2004
# Posted on: 07-May-2004 16:41:05   

I also tried to use save method and instead of insert triggers and it doesn't work. One possible sollution would be not to query (refech after insert = true) by scope_identity (which returns null) but by @@identity...

So can you please solve this somehow ???

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 07-May-2004 17:30:00   

SaX wrote:

I also tried to use save method and instead of insert triggers and it doesn't work. One possible sollution would be not to query (refech after insert = true) by scope_identity (which returns null) but by @@identity...

@@identity is unreliable. That's why SCOPE_IDENTITY is used.

Frans Bouma | Lead developer LLBLGen Pro
SaX
User
Posts: 42
Joined: 29-Apr-2004
# Posted on: 07-May-2004 18:30:37   

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...

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 07-May-2004 22:09:45   

Instead of triggers are not supported, as the code can't know what's going on. Aren't instead of triggers executed in the same connection scope? (i.e.: the scope_identity() will return the right value)

Frans Bouma | Lead developer LLBLGen Pro
ivanc
User
Posts: 36
Joined: 29-Apr-2004
# Posted on: 08-May-2004 11:10:31   

It seems that the instead of triggers arn't in the same scope as the select or insert... confused After triggers are in the same scope, so if the inserted values passed the constaint checks you can use the after triggers to the same result, if not, it seems that you have to use stored procedures (but I'm still new to the SQL Server, so there may be some obscure solution). @@Identity seems to work OK with the Instead of triggers since they are executed before the actual row is inserted in the database. MS should really look into it anyway.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 08-May-2004 12:45:35   

@@IDENTITY is bad, as it can give wrong results with multiple threads doing the same thing in a high load situation.

Frans Bouma | Lead developer LLBLGen Pro
SaX
User
Posts: 42
Joined: 29-Apr-2004
# Posted on: 10-May-2004 16:09:59   

We have a lot of instead of insert triggers and it would be a very big problem for us if we wouldn't be able to use them. Would it be possible to choose (through option for each entity in generator or something) which sql statement (scope_identity(), @@identity or ident_current()) to use for a given entity?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 10-May-2004 16:16:33   

Yes, in the EntityFieldFactory.cs/vb class, you can replace 'SCOPE_IDENTITY()' with @@IDENTITY or with ident_current if you like.

Frans Bouma | Lead developer LLBLGen Pro
Anonymous
User
Posts: 0
Joined: 11-Nov-2006
# Posted on: 28-Oct-2004 22:39:25   

@@IDENTITY is bad, as it can give wrong results with multiple threads doing the same thing in a high load situation.

What if you had it within a transaction ? That shouldnt cause any problems !