SQL Server - When a record is deleted?

[Brian-M]

I created a flow that creates a calendar event every time a new row is added to a sql table.  The calendar event ID is recorded in the SQL record upon creation.  I'm looking to create another flow that would then delete that calendar event when the corresponding sql record is deleted. However, there is no trigger for "When a record/row is deleted" in SQL Server.  Is there a workaround that would achieve the same objective?

[Heartholme]

Hi @Brian-M, 

Like you say, there isn't a trigger for when a record is deleted in an SQL Table. A work around here is to have a stored procedure which stores deleted rows in a temp table for example (https://stackoverflow.com/questions/25000968/how-to-get-list-of-deleted-records-in-sql). Select a recurring trigger of let's say 10 minutes and the next action should be to run the stored procedure. Every time you delete an event you finish your flow with putting in that event in another SQLTable/SPList/Excelworkbook etc. For each run of your flow, match up the SQL deleted rows with the calenderIDs in your Outlook deleted table, if there's any discreprancies, move forward and delete those records. This is a possible work around, but not the nicest solution. Is there a specific reason for why you're using a SQL server for this job? I would reccommend using a SP list or a custom dataverse table for this purpose, since you can easily run a trigger when a row/item is deleted: 

Hopefully this will help you on your way 🙂

{ If my post answered your question, please Accept it as a solution ✔ }
{ This will in turn help others find solutions to similar questions }

Best Regards
Heartholme

[Brian-M]

Thanks, I am working with a client and am restricted to their environment.  They have an app that stores data in a local sql server so unfortunately I'm limited in how I can handle this.

[Heartholme]

@Brian-M If the client have any SQL resources, I would cooprate with them to have a seperate temp. table with the deleted events, which you can call via a strored procedure. That would make it possible to do the synchronization between SQL and Outlook. Easiest way to connect to a SQL server is probably via a gateway installed on the server: 
https://docs.microsoft.com/en-us/power-automate/gateway-manage.

This solution requires a bit of work, so maybe it's not appropiate for the client to actually go for it. 

Let me know if you have any further questions. 

Best of luck. 

[craig_01]

@Brian-M Did you get a resolution to delete records.. I have a similar setup, im using a flow to pull sql data with holiday information into a sharepoint list (which is viewed as a calendar view).. However if a holiday entry is deleted from SQL, i need to keep the data updated on the sharepoint list. @Heartholme Any help of further guidance would be apprecaited also.

Regards

Craig

[Brian-M]

My situation ended up being a little different. The client and I determined that no records will be deleted in SQL, only added or changed. For your situation, maybe a flow that periodically checks, on a scheduled basis, the items in the sharepoint list against rows in SQL.  If the sharepoint record doesn't exist in SQL, delete it from Sharepoint.  Maybe have it auto-run once or twice a day? Or more frequent if needed.

Hopefully you're pulling some kind of unique record ID from SQL into sharepoint and you can just search for that in SQL.  If not, you'll have to try to match some of the data fields to each other, enough to say, "these are the same records", and either "do nothing" (if the corresponding SQL record exists) or delete the SP record if there is no corresponding SQL record.  Hope that helps.

[craig_01]

@Brian-M I have a unique SQL id for each record that is also fed into a column within the SharePoint list. At present i use the ID within an existing flow, and based on a condition either create a new record, or update an existing one... I will create a new flow for the deletion, can you give some guidance on the best way to build this flow with regards each element..

Thanks

[Brian-M]

The basics of the flow are:

1. Trigger = Schedule/Recurrence (every 10 mins or what ever you want)

2. Get Items (Sharepoint)

3. Get Rows (SQL)

4. Condition: If SP ID column = SQL ID, Do nothing (Yes), Else (No) Delete Item (Sharepoint)

You will likely need to convert your sharepoint column from a string to an integer to do the comparison though.

[craig_01]

@Brian-M Thanks for the reply... Can you provide a few points on converting the SP Column from a string to an Integer..

Thanks

[Brian-M]

Create a Compose action and use Int() function to convert string to an integer.  Example:

Int(Outputs('stringvalue'))

[abm · Accepted solution]

Hi @Brian-M 

I have made a tutorial video regarding this. Please have a look.

https://youtu.be/OG_PH1LlnlY

Thanks

[craig_01]

Hi @abm very informative video, i hope to test this over the weekend.. My SQL table is a Sage200 table, i cant see that creating the trigger within this would create any issued. Natively its still just a SQL database used by Sage.

Could the trigger be created on a view? As i have a view that only shows Live no Completed orders, or how would i ensure i am looking at the ID of "Live" orders.

Thanks.

[Brian-M]

Thank you @abm, very helpful video.

[abm]

Under the SQL trigger procedure code you could add an extra check to make sure its the right record  or not. Add an If statement and execute the flow http url.

Thanks