After Update Trigger

i have added a trigger to an existing table for auditing. is an "After Update" trigger as i am only interested in keeping the old record, not the inserted record.

When i try and do the update of a record on the database, get

"An optimistic concurrency violation was detected while attempting update or delete of table"

When i do an insert into the table - works fine

If i remove the trigger, update works again - and same if i disable trigger

have used SQL Profiler and got the update SQL that is generated by Lightspeed and run it in SQL Management Studio - and it updates fine - both the inital table and the update table

is there something i need to be specifying in the Lightspeed designer for that table ??

(i did delete it and re-import it to ensure that latest version of the table was in the designer)

am using Lightspeed 2.2

Does the entity you are working with have a LockVersion property?

yes, it does

Thanks, that helped narrow this one down. Currently because (presumably) you are making additional inserts/updates as part of the trigger, the number of affected rows being returned through from ADO.NET call trips the Optomistic Concurrency check because of the LockVersion property being present.

We will need to have a review on this one, so I will get back to you hopefully with an update on us being able to update the logic here to better support these scenarios.

Its worth mentioning that in the absence of a LockVersion property, update triggers will have no impact on this.

Jeremy

Hello. I'm in this boat as well. Is there a solution for this?

No this is not something we can support Im afraid.

Jeremy

This is actually a pretty major issue for us... Is there anything we can do to work around it; other than remove LockVersion or Implement our own Concurrency checks?

Implementing your own approach for concurrency control is the only alternative approach for this.

One feature that will help with this is our query filters. If you look at implementing this through a query filter the appropriate clause will be automatically applied to your queries which removes having to remember to include a clause yourself in every query. Have a read through http://www.mindscapehq.com/blog/index.php/2012/03/26/ninja-entity-filtering-in-lightspeed/ for details on how this works.

Jeremy

What scenario am I overlooking where the LockVersion check would fail and rowsAffected would be returned with a value greater than 0?

C:\Program Files (x86)\Mindscape\LightSpeed\SourceCode\LightSpeedSource\Src\Framework\Data\SqlApplyChangesExecutionStrategy.cs

if (!commandBuilder.IsCascade && commandBuilder.IsVersioned && (rowsAffected < 1))

Below is the EntityFramework Concurrency check:

    private void ValidateRowsAffected(long rowsAffected, UpdateCommand source)
    {
        // 0 rows affected indicates a concurrency failure; negative values suggest rowcount is off; 
        // positive values suggest at least one row was affected (we generally expect exactly one, 
        // but triggers/view logic/logging may change this value)
        if (0 == rowsAffected)
        {
            var stateEntries = DetermineStateEntriesFromSource(source);
            var message = Strings.Update_ConcurrencyError(rowsAffected);
            throw new OptimisticConcurrencyException(message, null, stateEntries.Cast<ObjectStateEntry>().Distinct());
        }
    }

Hmm after some investigation I think you are right. We do have some tests that fail with that change relating to the non-relational based providers but I can make some other changes to those providers to cover this. Ill look at getting this updated for the next nightly build.

Thanks!
Jeremy

Great news! Thanks again for the great support and amazing product.

Is this still the case - that it is not possible to have OptimisticConcurrency and use AFTER UPDATE trigger in Lightspeed

(am using version 5.0.2587)

This was fixed in 2013 and works as expected.

Jason

do you have more details - or where I cold find some ?
(my coding not correct as I am getting the concurrency error when I add in an AFTER UPDATE TRIGGER on table with OptimisticC turned on)

thx

I'm not sure what version the fix was introduced in, but we are using 5.0.3001; I just checked the source code and the fix is there, perhaps Jeremy could confirm if the fix is present in your version?