Error when writing to SQL db on Azure

App Building
,
1

Hi team,

New to Retool and doing some app testing. I've got a test SQL database setup on Azure, and all good so far from a read of data perspective. However, after setting up a form for quick insert of a record into a table in that database, I'm getting the following error:

statusCode:422
error:"Unprocessable Entity"
message:"The target table of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause."
data:null

Any ideas, as to how this can be resolved?

Cheers

1 Like
2

Hi there! Thank you for for your question!

Would you be able to share screenshots of the SQL query you are making?

Does a very simple select * statement work? (to test your resource setup is working)

Cheers,

Isaac

3

Hi Isaac,

The SQL SELECT query itself works great. No issues reading data from the database, and seeing it in a table. It's the adding a record - or editing a record I've also found - where I get that error.

4

Could you try running the insert query in SQL raw mode to see if that works? Curious to see your configuration for the query as well, that'll also help us provide appropriate syntax if need be :slightly_smiling_face:

5

Hi,

Good news! A straight SQL INSERT statement works - when trying it through a form, that's when the error occurs.

Successful SQL INSERT statement:

INSERT INTO dbo.STOCK_ITEMS
(SUP_CODE, SUP_ID, IS_ACTIVE, COST_PRICE, RRP, UOM_QTY,EOQ,MOQ,ACLDescription,STOCK_GROUP_1,STOCK_GROUP_2)
VALUES
('DH SQL RAW TEST3', 164, 'Y',1,1.99,1,0,0, 'DH AZURE DYSON SQL RAW TEST' ,121,19)

When trying it through a form - I get the error (key value pairs):

image
image1973×1126 163 KB

6

Hi team Retool,

Any ideas on that last update? Insert working on SQL raw mode, but not via form submit?

Thanks :blush:

7

Hey @dhretool! Sorry about the late reply here. I haven't been able to reproduce this on my end, using generated forms for input queries seems to be running fine. If you'd like us to take a look at your app ourselves for additional debugging you can try writing in to us directly. Otherwise, I might recommend sticking with the RAW SQL query and passing in the values from your form dynamically using {{}} (as you would with the key-value pairs).

8

I added a trigger to a table and my insert query started firing this error.

Indeed going from GUI to SQL mode fixes the problem. But hopefully something can be fixed to address this.

Here is a comprehensive SO thread on the subject:

https://stackoverflow.com/questions/13198476/cannot-use-update-with-output-clause-when-a-trigger-is-on-the-table

9

Thanks for the additional info @bradlymathews!

Will keep trying to repro and let yall know here when there's a fix.

10

Any update on this one? Still getting the error when using key-value pairs for inserting/updating (and there is a trigger on the table).

11

Hey @mth! No updates yet, but I've just bumped the ticket with the dev team. Thanks for resurfacing this!

1 Like
12

Hi,
I have the same problem. I cannot update ms sql records via forms. Problem is with output inserted.* in sql query.

David

13

I'm also having this exact problem.

The target table of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Would love for it to get checked / resolved. (removing any triggers does not change anything)

Not sure if it is relevant, but it did work with the automatically generated forms / table setup. But I recently switched the tables to the newer table component. I also made other changes so not sure if that is what started the error.

14

I have the same issue here, using SQL server on Azure. The table has a trigger defined and I am trying to use the GUI mode to update an existing record. SQL works fine for the same query. I tried switching to GUI mode after a recommendation that it would allow for notification of query failure if the query didn't update any rows.

image
image1268×864 43.1 KB

15

+1 I still have this issue

16

Thanks @PadenM, I've bumped the internal ticket. We will update here as we get any additional information to share.

17

+ 1 to this issue. MS SQL Server GUI SQL Inserts do not work if there is a trigger on the table.