SqlServer not support OUTPUT with INSERT and triggers

Bắt đầu với Providers.MsSqlServer.fs, đặc biệt là các dòng 302-307, và lần theo đường dẫn ProcessUpdates được thể hiện trong stack trace. Tái hiện insert đối với một bảng SQL Server có trigger được bật, sau đó xác định provider nên xử lý mệnh đề OUTPUT như thế nào và tùy chọn cấu hình được đề xuất nên ảnh hưởng đến nó ra sao; hoàn thành có nghĩa là SubmitUpdates thành công với schema này mà không làm hỏng các insert thông thường.

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