7

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.

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:

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...

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
PeaceAndJam
  • 91
  • 2
  • 8
  • The error is telling you the problem here; you can't get around that. You'll be better off telling us what you're *actually* trying to achieve. – Thom A Jan 09 '20 at 10:53
  • Sounds like the code behind your form is just using `output` to return a result set instead of `output ... into` to store it in a @table variable, #temp or ##temp table, or persisted table. It's basically the same problem and solution as [Cannot use UPDATE with OUTPUT clause when a trigger is on the table](https://stackoverflow.com/questions/13198476/cannot-use-update-with-output-clause-when-a-trigger-is-on-the-table) – AlwaysLearning Jan 09 '20 at 11:02
  • Hey @AlwaysLearning thanks for responding. Are you saying that despite the fact I do not use OUTPUT or INSERTED etc, I need to accommodate for that result set and just store it somewhere? I looked that that link before but as with most examples, the person is utilising the INSERTED or DELETED whereas I am not. Maybe I am being too literal with my coding and understanding of it... – PeaceAndJam Jan 09 '20 at 13:36
  • 2
    Use a stored procedure in power apps rather than the insert row component. As that appends the OUTPUT clause to the INSERT as discussed here https://powerusers.microsoft.com/t5/Connecting-To-Data/Insert-into-SQL-table-with-active-triggers-implemented/td-p/43549 – Martin Smith Jan 09 '20 at 14:34
  • @MartinSmith Thank you so much, this has saved me such a headache. – PeaceAndJam Jan 09 '20 at 14:48

1 Answers1

6

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);
lptr
  • 1
  • 2
  • 6
  • 16
  • Hi @lptr thanks for your response, I've run through your example on my machine and have an additional question :) Okay so my confusion here is why the first "insert into" succeeds despite not accommodating the output result set like the third one from your example? – PeaceAndJam Jan 09 '20 at 13:33
  • @PeaceAndJam: if table1 does NOT have a trigger then all 3 statements succeed. If table1 DOES have a trigger then the second statement fails (because when the target table has triggers, output must be channeled INTO a container/table) – lptr Jan 09 '20 at 14:00
  • ...and the very first statement does NOT use/have an OUTPUT so, it always succeeds. – lptr Jan 09 '20 at 14:28
  • Hey @lptr thanks for your patience :) This is why I am having trouble understanding my issue: my trigger does not use the output clause so I do not understand why the error is thrown when inserting into table1 via the PowerApp. When I run through the example you provided, it works but its all in SSMS... I wonder if it's something funky going on with the insert coming from a PowerApps form? – PeaceAndJam Jan 09 '20 at 14:46
  • I think Martin Smith in the comments above has confirmed it is a PowerApps limitation. Thanks for your time @lptr! Much appreciated. – PeaceAndJam Jan 09 '20 at 14:48