Error when writing to SQL db on Azure

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

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

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.

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:

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):

Hi team Retool,

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

Thanks :blush:

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

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:

Thanks for the additional info @bradlymathews!

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