如果DML语句包含不带INTO子句的OUTPUT子句，则DML语句不能具有任何已启用的触发器 [英] DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause

问题描述

我一直在拖网StackOverflow和其他站点来解决我的错误，因此没有成功，因此有时间发布帖子，看看是否有人可以告诉我我要去哪里.

I have been unsuccessfully trawling StackOverflow and other sites to solve my error so time to post and see if someone can tell me where I'm going wrong.

我的系统具有一个简单形式的PowerApp.保存表单后，字段将写入table1.然后，我想在table1上创建一个触发器，以便将每个新记录的一个字段插入到table2中.

My system has a PowerApp with a simple form. On saving the form, the fields are written to table1. I then want to create a trigger on table1 so that one field from each new record is inserted into table2.

尽管我的代码中没有输出子句，但出现以下错误:

Despite there being no output clause in my code, I am getting the following error:

请求的操作无效.服务器响应:Microsoft SQL:如果DML语句的目标表'table1'包含不带INTO子句的OUTPUT子句，则该语句不能具有任何启用的触发器.内部异常:Microsoft SQL:如果DML语句的目标表'table1'包含不带INTO子句的OUTPUT子句，则该语句不能具有任何已启用的触发器.

The requested operation is invalid. Server Response: Microsoft SQL: The target table 'table1' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. inner exception: Microsoft SQL: The target table 'table1' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

将我的代码剥离回以下代码仍会引发相同的错误:

Stripping my code back to the following still throws the same error:

ALTER TRIGGER [dbo].[FormatID] 
   ON  [dbo].[table1]
   AFTER INSERT
AS 
BEGIN

    INSERT INTO [dbo].[table2](origID) 
    VALUES (1)

END

任何建议都请开除，我感觉好像缺少了一些明显的东西...

Any suggestions please fire away, I feel as though I am missing something obvious...

推荐答案

您的应用程序正在输出dml语句的插入/删除值作为结果集.当表(dml的目标)上有触发器时，这是不可能的

your application is OUTPUT-ing inserted/deleted values of a dml statement as a resultset. This is not possible when there are triggers on the table (target of the dml)

create table dbo.table1(id int);
create table dbo.table2(origID int);
go

CREATE OR ALTER TRIGGER [dbo].[FormatID] 
   ON  [dbo].[table1]
   AFTER INSERT
AS 
BEGIN

    INSERT INTO [dbo].[table2](origID) 
    VALUES (1)

END;


--succeeds
insert into dbo.table1(id) 
values (1);
go

--fails
insert into dbo.table1(id) 
output inserted.id --output cannot be a resultset(in the void) because there is a trigger
values(1);

--succeeds
declare @outputtable table (id int);
insert into dbo.table1(id) 
output inserted.id into @outputtable(id)--output into a table
values(1);

这篇关于如果DML语句包含不带INTO子句的OUTPUT子句，则DML语句不能具有任何已启用的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！