{"action":"created","issue":{"url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/322","repository_url":"https://api.github.com/repos/fsprojects/SQLProvider","labels_url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/322/labels{/name}","comments_url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/322/comments","events_url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/322/events","html_url":"https://github.com/fsprojects/SQLProvider/issues/322","id":173169482,"number":322,"title":"SqlServer not support OUTPUT with INSERT and triggers","user":{"login":"fpellet","id":1950032,"avatar_url":"https://avatars.githubusercontent.com/u/1950032?v=3","gravatar_id":"","url":"https://api.github.com/users/fpellet","html_url":"https://github.com/fpellet","followers_url":"https://api.github.com/users/fpellet/followers","following_url":"https://api.github.com/users/fpellet/following{/other_user}","gists_url":"https://api.github.com/users/fpellet/gists{/gist_id}","starred_url":"https://api.github.com/users/fpellet/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/fpellet/subscriptions","organizations_url":"https://api.github.com/users/fpellet/orgs","repos_url":"https://api.github.com/users/fpellet/repos","events_url":"https://api.github.com/users/fpellet/events{/privacy}","received_events_url":"https://api.github.com/users/fpellet/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":3,"created_at":"2016-08-25T10:37:14Z","updated_at":"2016-08-25T13:52:42Z","closed_at":null,"body":"Hi,\r\n\r\n### Description\r\n\r\nOn SqlServer, I cannot insert if my table has trigger.\r\nSqlServer don't allow OUTPUT in INSERT request if the table has trigger\r\n\r\n### Repro steps\r\n\r\nCreate table with trigger : \r\n```\r\nCREATE TABLE [dbo].[TableWithTrigger](\r\n\t[Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_TableWithTrigger_Id]  DEFAULT (newid()),\r\n\t[Text1] [nvarchar](50) NULL,\r\n\t[Text2] [nvarchar](50) NULL,\r\n CONSTRAINT [PK_TableWithTrigger] PRIMARY KEY CLUSTERED \r\n(\r\n\t[Id] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\n) ON [PRIMARY]\r\n\r\n```\r\n\r\nTry to insert\r\n```\r\ntype Sql = SqlDataProvider<Common.DatabaseProviderTypes.MSSQLSERVER, connStr>\r\n\r\nlet ctx = Sql.GetDataContext()\r\n\r\nlet row = ctx.Dbo.TableWithTrigger.Create()\r\nrow.Id <- Guid.NewGuid()\r\nrow.Text1 <- \"Essai\"\r\n\r\nctx.SubmitUpdates()\r\n```\r\n\r\n### Expected behavior\r\n\r\nInsert new row\r\n\r\n### Actual behavior\r\n\r\nI have sql error :\r\n\r\n```\r\nSystem.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.\r\n   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\r\n   at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteScalar()\r\n   at <StartupCode$FSharp-Data-SqlProvider>.$Providers.MsSqlServer.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates@716.Invoke(SqlEntity e)\r\n   at Microsoft.FSharp.Collections.SeqModule.Iterate[T](FSharpFunc`2 action, IEnumerable`1 source)\r\n   at FSharp.Data.Sql.Providers.MSSqlServerProvider.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates(IDbConnection con, ConcurrentDictionary`2 entities)\r\n   at <StartupCode$FSharp-Data-SqlProvider>.$SqlRuntime.DataContext.f@1-28(SqlDataContext __, IDbConnection con, Unit unitVar0)\r\n   at FSharp.Data.Sql.Runtime.SqlDataContext.FSharp-Data-Sql-Common-ISqlDataContext-SubmitPendingChanges()\r\n```\r\n\r\n### Known workarounds\r\n\r\nA create a fork and remove line 302-307 of Providers.MsSqlServer.fs\r\nI propose to add config key to disable this behavior\r\n\r\n### Related information \r\n\r\n* .Net 4.6.2\r\n* Version 1.0.28\r\n"},"comment":{"url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/comments/242394068","html_url":"https://github.com/fsprojects/SQLProvider/issues/322#issuecomment-242394068","issue_url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/322","id":242394068,"user":{"login":"Thorium","id":229355,"avatar_url":"https://avatars.githubusercontent.com/u/229355?v=3","gravatar_id":"","url":"https://api.github.com/users/Thorium","html_url":"https://github.com/Thorium","followers_url":"https://api.github.com/users/Thorium/followers","following_url":"https://api.github.com/users/Thorium/following{/other_user}","gists_url":"https://api.github.com/users/Thorium/gists{/gist_id}","starred_url":"https://api.github.com/users/Thorium/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Thorium/subscriptions","organizations_url":"https://api.github.com/users/Thorium/orgs","repos_url":"https://api.github.com/users/Thorium/repos","events_url":"https://api.github.com/users/Thorium/events{/privacy}","received_events_url":"https://api.github.com/users/Thorium/received_events","type":"User","site_admin":false},"created_at":"2016-08-25T13:52:42Z","updated_at":"2016-08-25T13:52:42Z","body":"Ok the actual trigger code was missing from the issue. Here is one:\r\n\r\n```\r\nCREATE TRIGGER reminder1  \r\nON [dbo].[TableWithTrigger]\r\nAFTER INSERT, UPDATE   \r\nAS RAISERROR ('Notify Customer Relations', 16, 10);  \r\nGO  \r\n```\r\n\r\nAnd this is one way to found tables with triggers (that could be excluded from pkLookup dictionary):\r\n\r\n```\r\nSELECT OBJECT_NAME(parent_id) as TablesHavingTriggers FROM SYS.TRIGGERS\r\n```\r\n"}}
{"actor":{"display_login":"Thorium"}}