Unanswered

SQL Server - When a record is deleted?

(1) Share

Report

Posted on by Brian-M

437 Super User 2024 Season 1

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?

Categories:

Building flows

I have the same question (0)

All responses (13)

Answers (1)

Heartholme 1,278 Super User 2024 Season 1 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Brian-M 437 Super User 2024 Season 1 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Heartholme 1,278 Super User 2024 Season 1 on at

Like (1)

Report

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

Was this reply helpful? Yes No
craig_01 153 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
Brian-M 437 Super User 2024 Season 1 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
craig_01 153 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
Brian-M 437 Super User 2024 Season 1 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
craig_01 153 on at

Like (0)

Report

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

Thanks

Was this reply helpful? Yes No
Brian-M 437 Super User 2024 Season 1 on at

Like (0)

Report

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

Int(Outputs('stringvalue'))

Was this reply helpful? Yes No
Verified answer

abm abm 32,865 Most Valuable Professional on at

Like (1)

Report

Hi @Brian-M

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

https://youtu.be/OG_PH1LlnlY

Thanks

Was this reply helpful? Yes No