UpdatedAt Table Trigger problem SQL Server

[gianox]

Describe the bug

I have a table named AspectClasses. When I enable the trigger:

CREATE OR ALTER TRIGGER [dbo].[AspectClasses_UpdatedAt]
ON [dbo].[AspectClasses]
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE
[dbo].[AspectClasses]
SET
[UpdatedAt] = GETUTCDATE()
WHERE
[Id] IN (SELECT [Id] FROM INSERTED);
END

I get the following error when using the repository method "await someRepository.ReplaceAsync(someAspectClass)"

The target table 'AspectClasses' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

To Reproduce

Steps to reproduce the behavior:

I fetch an object using var someAspectClass = await someRepository.ReadAsync(someAspectClassId);

Change a property on someAspectClass

call the await someRepository.ReplaceAsync(someAspectClass);

Expected behavior

Modified someAspectClass is saved with an updated UpdatedAt property

What platforms?

• Server:
  • Version of dotnet being used to compile? .NET 8.0
  • Library versions? Latest
  • What database are you using? SQL Server
  • Where are you running the server? Azure

Additional context

Add any other context about the problem here.

I tried working around the issue by doing this in my DbContext:

public override async Task SaveChangesAsync(CancellationToken cancellationToken = default)
{
await SetUpdatedAt();
return await base.SaveChangesAsync(cancellationToken);
}

private async Task SetUpdatedAt()
{
var entries = ChangeTracker.Entries().Where(e => (e.State == EntityState.Added || e.State == EntityState.Modified));

foreach (var entry in entries)
{
    var entity = (ExtEntityData)entry.Entity;

    if (entry.State == EntityState.Modified)
    {
        entity.UpdatedAt = DateTimeOffset.UtcNow;
    }
}

}

The task is finishes without issue, but the entry in the database does not get an updated UpdatedAt value.

[gianox]

Adding this to the modelBuilder seems to be a workaround: modelBuilder.Entity().ToTable(t => t.HasTrigger("AspectClasses_UpdatedAt"));

[richard-einfinity]

Hi @gianox the HasTrigger configuration is required since EFCore 7 so not really a problem with the DataSync library but rather a requirement of the underlying EF core framework.

Further information here https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/breaking-changes?tabs=v7#sqlserver-tables-with-triggers

@adrianhall would it not be better to let the repository deal with the timestamp through the UpdatedAtRepositoryAttribute rather than having to maintain the triggers?

[adrianhall]

The EFCore repository has enough flexibility to cope with just about any condition.

The trigger allows you to use the database properly without needing to go through the repository. If the datasync library is guaranteed to be the only thing modifying the database, then you can go either way. If, however, you are modifying the table outside of the repository - in the same app, through SQL, or wherever (and this is common), then you need to use a trigger.

[adrianhall]

TODO:

• Update docs for the server to indicate the HasTrigger is required (across the board) when using EF Core and a trigger.