SqlServer not support OUTPUT with INSERT and triggers

Commencez par Providers.MsSqlServer.fs, en particulier les lignes 302-307, et suivez le chemin ProcessUpdates indiqué dans la stack trace. Reproduisez l'insert sur une table SQL Server avec un trigger activé, puis déterminez comment le provider doit gérer la clause OUTPUT et quel effet l'option de configuration proposée doit avoir sur celle-ci ; le travail est terminé lorsque SubmitUpdates réussit pour ce schéma sans perturber les inserts normaux.

Hi,

Description

On SqlServer, I cannot insert if my table has trigger.
SqlServer don't allow OUTPUT in INSERT request if the table has trigger

Repro steps

Create table with trigger :

CREATE TABLE [dbo].[TableWithTrigger](
    [Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_TableWithTrigger_Id]  DEFAULT (newid()),
    [Text1] [nvarchar](50) NULL,
    [Text2] [nvarchar](50) NULL,
 CONSTRAINT [PK_TableWithTrigger] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Try to insert

type Sql = SqlDataProvider<Common.DatabaseProviderTypes.MSSQLSERVER, connStr>

let ctx = Sql.GetDataContext()

let row = ctx.Dbo.TableWithTrigger.Create()
row.Id <- Guid.NewGuid()
row.Text1 <- "Essai"

ctx.SubmitUpdates()

Expected behavior

Insert new row

Actual behavior

I have sql error :

System.Data.SqlClient.SqlException: The target table 'dbo.TableWithTrigger' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
   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.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at <StartupCode$FSharp-Data-SqlProvider>.$Providers.MsSqlServer.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates@716.Invoke(SqlEntity e)
   at Microsoft.FSharp.Collections.SeqModule.Iterate[T](FSharpFunc`2 action, IEnumerable`1 source)
   at FSharp.Data.Sql.Providers.MSSqlServerProvider.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates(IDbConnection con, ConcurrentDictionary`2 entities)
   at <StartupCode$FSharp-Data-SqlProvider>.$SqlRuntime.DataContext.f@1-28(SqlDataContext __, IDbConnection con, Unit unitVar0)
   at FSharp.Data.Sql.Runtime.SqlDataContext.FSharp-Data-Sql-Common-ISqlDataContext-SubmitPendingChanges()

Known workarounds

A create a fork and remove line 302-307 of Providers.MsSqlServer.fs
I propose to add config key to disable this behavior

Related information

• .Net 4.6.2
• Version 1.0.28