SqlServer not support OUTPUT with INSERT and triggers

Inizia da Providers.MsSqlServer.fs, in particolare dalle righe 302-307, e segui il percorso ProcessUpdates mostrato nello stack trace. Riproduci l'insert su una tabella SQL Server con un trigger abilitato, quindi determina come il provider dovrebbe gestire la clausola OUTPUT e quale effetto dovrebbe avere su di essa l'opzione di configurazione proposta; il lavoro è terminato quando SubmitUpdates ha esito positivo per questo schema senza compromettere gli insert normali.

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