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

[databrecht]

Bug Description

RepoDB in combination with SQL server seems to suffer from the same problems that Prisma has run into.
prisma/prisma#4535

It seems that RepoDB uses OUTPUT in their query generator which stops their users from using triggers. Apparently EF Core works around this by using a different approach in their query generator according to this SO post: https://stackoverflow.com/questions/13198476/cannot-use-update-with-output-clause-when-a-trigger-is-on-the-table

Exception Message:

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


Statement(s) could not be prepared.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader()
   at System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
   at RepoDb.DbConnectionExtension.MergeAllAsyncInternalBase[TEntity](IDbConnection connection, String tableName, IEnumerable`1 entities, IEnumerable`1 qualifiers, Int32 batchSize, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder, CancellationToken cancellationToken)
   at RepoDb.DbConnectionExtension.MergeAllAsyncInternal[TEntity](IDbConnection connection, String tableName, IEnumerable`1 entities, IEnumerable`1 qualifiers, Int32 batchSize, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder, CancellationToken cancellationToken)

Schema and Model:

Probably not relevant in this case, any schema model that has a trigger on it (in our case a trigger to update an updated_at datettime) will trigger this if a Merge or MergeAll is used (judging from the source code), we were using a MergeAll when we encountered this.

Library Version:

Example: RepoDb v1.12.10 and RepoDb.SqlServer v1.1.5

Workaround

For people who bump into this and are looking for a solution, it seems we can use the bulk operations instead. Judging from the source code it also uses OUTPUT but only when isReturnIdentity is enabled (which is, according to the docs, disabled by default).

if (isReturnIdentity && identityField != null)
            {
                builder
                    .OrderBy()
                    .WriteText("[__RepoDb_OrderColumn]")
                    .Ascending();
            }

https://repodb.net/operation/bulkmerge

[dendism]

EDIT: ok i just read that SO post and seems, it's not so easy to solve it in a right way

Just stumbled on this issue too and had to use BulkMerge :)
Though this problem can also be solved by using table type variable and insert the values there and just select it at the end.
Snippet:

// Iterate the indexes
for (var index = 0; index < batchSize; index++)
{
    // MERGE T USING S
    builder
        .WriteText(keyColumn is null ? "" : $"DECLARE @MergeResult_{index} AS table (Result sql_variant, __RepoDb_OrderColumn int);")
        .Merge()
    ...
    // Set the output
    if (keyColumn != null)
    {
        // original piece of code
        // builder
        //     .WriteText(string.Concat("OUTPUT INSERTED.", keyColumn.Name.AsField(DbSetting)))
        //         .As("[Result],")
        //     .WriteText($"{DbSetting.ParameterPrefix}__RepoDb_OrderColumn_{index}")
        //         .As("[__RepoDb_OrderColumn]");
        
        builder
            .WriteText(string.Concat("OUTPUT INSERTED.", keyColumn.Name.AsField(DbSetting)))
            .WriteText(",")
            .WriteText($"{DbSetting.ParameterPrefix}__RepoDb_OrderColumn_{index}")
            .Space()
            .WriteText($"INTO @MergeResult_{index}");
    }

    // End the builder
    builder.End();

    if (keyColumn is not null)
        builder.WriteText($"SELECT * FROM @MergeResult_{index};");

I was just quick testing it, if it works. I have no idea how this will impact performance. I also couldn't find a way how to determine database type of the identity/primary key column, so i used sql_variant letting it to ADO.NET to handle it.