SQL Database Update Error

[AdamVW]

Good day!

I have two tables in SQL, tbl 1- "RetailDataWks" has stock data that has certain statuses calculated, e.g. "Overstocked" column determines whether the item is overstocked on the shelf. Once a week the stock data is updated in the SQL database, via script and not through Appsheet (So no trigger event for BOT). What I then want to do is set up a scheduled event in appsheet that triggers a webhook in the background, that filters the "RetailDataWks" and add all the overstocked items in the "RetailDataWks" table and adds it to another "SMTasks" table.

Before I switched to SQL I had the following working:

1. Created a data action that updates a column on the "RetailDataWks" table. This column is filtered by the scheduled event and just updates to a uniqueid() each time, as I only want appsheet to register the change to the table.

2. Created a scheduled event that runs on a weekly basis, filtering "RetailDataWks" to just one row

3. Created a webhook that filters "RetailDataWks" and updates "SMTasks"

4. Created a bot that triggers on the scheduled event, updates the "RetailDataWks" table using the DataAction and then calls the webhook.

This worked fine whilst using googlesheets as background, but since moving to SQL I get this error and cannot seem to fix it 😞

Error: 'Set Column Values' Data action ''Set Column Values' Data action 'Change Counter'' failed with exception Can't update table 'RetailDataWks' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. ."

So, questions:

1. Am I approaching the problem correctly? Is there another way to schedule a webhook?

2. If the process is right, how do I fix the error?

Any help would be greatly apprecited!

[Marc_Dillon · Accepted solution]

---

@AdamVW wrote:

Can't update table 'RetailDataWks' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

---

That's a SQL error generated from your database, Appsheet is just forwarding it along for you to see. Google search that error for help with it. My quick search indicates that you may have set up a sql trigger on that table in an incorrect way.

---

@AdamVW wrote:

Am I approaching the problem correctly? Is there another way to schedule a webhook?

---

I mean, if it works, then technically it's "correct", but I would have set it up a bit differently. For starters I don't understand the point of the data change action in your #1? And you can just run a table-less scheduled event, running the event on a single record seems a bit convoluted. 

Also, why even get Appsheet automation involved here? You could just do the crud operations directly in the sql database I think, with its own event scheduler. Unless there are some Appsheet VCs in play?

[AdamVW]

Hi Marc, thank you for the reply, and yes you are correct; I realized I overcomplicated the process completely, I am now just doing with Stored Procedure in SQL. Thank you for helping!