SaveEntity to table with "Instead of insert" trigger fails due to "select SCOPE_IDENTITY()"

Posts   
 
    
hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 02-May-2015 22:01:50   

Hi.

We recently upgraded from v4.0 to v4.2 Final, April 10th, 2015. We are using Adapter pattern.

We noticed that SaveEntity passing false for refetchAfterSave, now selects SCOPE_IDENTITY(). This causes a problem with tables having "instead of insert" trigger. We are using such triggers to do some data validation. We are doing the same validation in the UI so we have disabled these triggers for now.

There is an example below where @p10 is populated with SCOPE_IDENTITY().

Will this new behaviour stay like this?

adapter.SaveEntity(userFutureDepartmentChange, false, true);
{ Query: 
INSERT INTO [ServiceManager].[dbo].[UserFutureDepartmentChange] ([AddedByUserId], [AddedDate], [ChangeDate], [FromDepartmentEnquiryId], [FromDepartmentId], [IsManager], [TargetUserId], [ToDepartmentEnquiryId], [ToDepartmentId]) 
VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9) ;SELECT @p10=SCOPE_IDENTITY()
Parameter: @p1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3bc5fa09-d06a-4e16-8a37-23ca2431dcd7. Parameter: @p2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2015-04-28T08:07:56.4757493Z. Parameter: @p3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2015-04-30T22:00:00.0000000Z. Parameter: @p4 : Int64. Length: 0. Precision: 19. Scale: 0. Direction: Input. Value: 186920. Parameter: @p5 : Int64. Length: 0. Precision: 19. Scale: 0. Direction: Input. Value: 1429. Parameter: @p6 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: True. Parameter: @p7 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 00f38758-43a3-4f3e-aadf-d83acb2e7015. Parameter: @p8 : Int64. Length: 0. Precision: 19. Scale: 0. Direction: Input. Value: 186920. Parameter: @p9 : Int64. Length: 0. Precision: 19. Scale: 0. Direction: Input. Value: 60. Parameter: @p10 : Int64. Length: 0. Precision: 19. Scale: 0. Direction: Output. Value: <undefined value>.}
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 03-May-2015 09:38:18   

In the entity editor, select '@@IDENTITY' instead of 'SCOPE_IDENTITY' as sequence for the PK field for the entity. Regenerate the code and the query should use @@IDENTITY instead of SCOPE_IDENTITY and the right value should be returned to the entity simple_smile

Frans Bouma | Lead developer LLBLGen Pro