SqlServer not support OUTPUT with INSERT and triggers

Providers.MsSqlServer.fs、特に302-307行目から始め、スタックトレースに示されているProcessUpdatesのパスをたどってください。トリガーが有効になっているSQL Serverテーブルに対するinsertを再現し、その後、providerがOUTPUT句をどのように扱うべきか、また提案された設定オプションがそれにどのような影響を与えるべきかを判断してください。このスキーマでSubmitUpdatesが成功し、通常のinsertを壊さなければ完了です。

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