SqlServer not support OUTPUT with INSERT and triggers

[fpellet]

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

[Thorium]

Maybe we could read the trigger from DB and if found, we could just go to the else-branch of the pattern match.

But the ID is used to get the reference to the entity: then if we don't get the id, you can't update or delete the item. Is that ok for you to not be able to do those things?

[fpellet]

For me, it's fine, because it's not the database that generate id
But I think his strange behavior in other cases.
That is why I proposed a config key

[Thorium]

Not related to the actual matter, but I'm just curious,
why do you ever want a trigger like that as you could easily just say:

CREATE TABLE [dbo].[TableWithTrigger](
    [Id] [uniqueidentifier] NOT NULL DEFAULT (newsequentialid()),
    [Text1] [nvarchar](50) NULL,

[Thorium]

Ok the actual trigger code was missing from the issue. Here is one:

CREATE TRIGGER reminder1  
ON [dbo].[TableWithTrigger]
AFTER INSERT, UPDATE   
AS RAISERROR ('Notify Customer Relations', 16, 10);  
GO  

And this is one way to found tables with triggers (that could be excluded from pkLookup dictionary):

SELECT OBJECT_NAME(parent_id) as TablesHavingTriggers FROM SYS.TRIGGERS