Abort or modify incoming value on BeforeUpdate?

[Trevortni]

Is there any way, or can a way be added, to abort the update if a condition is not met, or to alter the incoming value before the update happens?

I am trying to implement a trigger for a RowVersion column, where I only allow the update to happen if the RowVersion on the new row matches the RowVersion on the old row, and to increment it if it is allowed, and I need this functionality in order for it to work.

[Trevortni]

Looking at the internal code, it looks like three things would need to be added to make this work:

1. Else support could be added by creating public IList<ITriggerAction> ElseActions in TriggerBuilders.Base.Trigger, and supporting code in On[Event].On[Action]TriggerElse and [Provider].MySqlTriggerVisitor
2. AlterUpdate support could be added by creating TriggerAlterUpdateAction with a single LambdaExpression in the constructor; this would be narrowed to Expression<Func<TTriggerEntity, TTriggerEntity, TTriggerEntity>> alterValues in On[Event]Trigger[Action]Action in TriggerBuilders and the appropriate provider code
3. Abort support could be added by creating TriggerAbortAction with a LambdaExpression that takes new and old values for the updated row and returns an error message to be thrown by the db provider (assuming other providers work similarly to MySql, which is what I am working in)

Listing this is more for my own notes, as I'm sure you probably already know what you would do, but if you would like me to take a stab at implementing it myself, I might be able to find time in the next couple weekends.

[Trevortni]

So you're trying to implement row versioning in a database that doesn't support it natively?

Unless there's a way to turn it on that I haven't been able to figure out yet, yes.

[kjkrum]

I deleted my original comment when I noticed your mention of MySQL. I'm not in front of a computer with VS on it right now, but I'll try to help with snippets from one of my own private repos.

I have a project where I use a trigger to cancel an update if a boolean column is true. Note that it's an "after" trigger.

var trigger = new OnUpdateTrigger<IInactivatable>(TriggerTime.After);
trigger.Action(action => action
    .Condition((before, after) => before.Inactive && after.Inactive)
    .Throw(ErrorNumbers.RowInactive, $"Cannot update inactive {type.ClrType.Name}."));

Throw is my own extension. I don't know how specific it is to SQL Server. There might be a better way to abort the transaction in MySQL, but the principle should be the same.

public static void Throw<T>
(
    this OnUpdateTriggerActions<T> actions,
    int errorNumber,
    string message,
    byte state = 1
) where T : class
{
    actions.ExecuteRawSql($"THROW {errorNumber}, '{message}', {state}");
}

[Trevortni]

I have a project where I use a trigger to cancel an update if a boolean column is true. Note that it's an "after" trigger.

Is there a reason it's an "after" trigger instead of a "before" trigger? Wondering if there's some kind of gotcha I'm not seeing there, since my naive reasoning would be that stopping it makes more sense before it goes in.

Throw is my own extension. I don't know how specific it is to SQL Server. There might be a better way to abort the transaction in MySQL, but the principle should be the same.

MySql essentially does the same thing - I think the name of the command is "SIGNAL"? Not exactly intuitive to me, but it basically boils down to throwing an error.

[kjkrum]

@Trevortni In my case it's an after trigger because I want to compare the before an after values, and only veto the update if the column is true in both. A before trigger might work. I don't remember if all the same context information is available to it.