Update trigger on target table causes BulkMerge to fail

[geremysremitter]

Description

Upon performing a BulkMerge against an entity that targets a SQL table with an update trigger, an exception is thrown. This is a SQL Exception which appears to be complaining that the output (presumably of the new ids created during insert / update) is not being put into a variable. This would be a nice-to-have feature, that is, to be able perform bulk operations against tables with triggers.

Tks

Exception

Microsoft.Data.SqlClient.SqlException

Exception message:

Microsoft.Data.SqlClient.SqlError: DestinationTable' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Stack trace:

Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, 
Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior 
behavior)   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)   at .(DbCommand , BulkOperation , Int32 )   at .( , DbCommand )   at .Execute(List`1 actions)   at Z.BulkOperations.BulkOperation.Execute()   at Z.Dapper.Plus.DapperPlusAction.Execute()   at Z.Dapper.Plus.DapperPlusActionSet`1..(Object )   at System.Collections.Generic.List`1.ForEach(Action`1 action)   at Z.Dapper.Plus.DapperPlusActionSet`1.CreateDapperAction[T1,T2](String mapperKey, DapperPlusActionKind actionKind, Func`2[] selectors)

Fiddle or Project (Optional)

Any bulkmerge against a table with an UPDATE trigger (we haven't tried with INSERT triggers but assume the same error would occur)

Please advise if you'd like us to send code separately. Thanks

Further technical details

• Dapper version: 2.0.123
• Dapper Plus version: 6.0.0
• Dapper Contrib: 2.0.78
• Database Provider: SQL Server (Azure)

[JonathanMagnan]

Hello @geremysremitter

Are you using a BulkMerge on a View? Our library should automatically discover your table has a trigger and adjust the SQL when a Table is directly used but unfortunately cannot have this logic when a View is used.

You should be able to fix this issue per table with the options.ForceTriggerResolution = true; options:

DapperPlusManager.Entity<MyEntityType>().UseBulkOptions(options =>
{
    options.ForceTriggerResolution = true;
});

Let me know if that solution worked for you.

Best Regards,

Jon

[geremysremitter]

Hi John,

Nope, we are running the bulk operations on tables (in fact this database has no views at present). The exception sited in the original post went away as soon as I dropped our update triggers on all tables. Bear in mind this is all evaluation stuff so it's no issue dropping these for sake of evaluation however we'd ideally like to enable update triggers in the least.

BTW, this exception only occurs on tables where we've setup as follows

DapperPlusManager.Entity<Widget>().Identity(i => i.WidgetId, true); // produces SqlException at BulkMerge

It seems that

DapperPlusManager.Entity<Widget>().Identity(i => i.WidgetId, false); // no exception at BulkMerge

Works fine

Will report back the results of the options suggestion. Thanks !

[geremysremitter]

Follow-up

The options fix suppressed the SqlException and bulkmerges against tables with update triggers is working. Very nice and thanks for the quick response again.

I will close this out - appreciate it !