Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add ability for customers to filter out rows changed for trigger #852

Open
Charles-Gagnon opened this issue May 4, 2023 · 0 comments
Open
Labels
enhancement New feature or request P2 trigger
Milestone

Comments

@Charles-Gagnon
Copy link
Contributor

From #840

I've written an Azure function using the SQL trigger to process records that have been inserted into a table. When I am finished with the processing, I need to update the record that was processed to indicate it was successfully processed. Currently I am using a DbContext and calling ExecuteSqlInterpolated to update the underlying record from within the function, using the ID of the record that was processed via the triggered function. However, this results in another change to the DB that causes the trigger to fire again. When the function runs, I can filter the changes list to only include inserts, like this: changes.Any(m => m.Operation == SqlChangeOperation.Insert) That way I skip processing for those, but it is an unnecessary call to the function nonetheless. Is there a way to ignore updates (or inserts or delete for that matter) that are made inside the function, either by not tracking them, or using CHANGE_TRACKING_CONTEXT in the UPDATE statement and ignoring that context in the queries in this library's class SqlTableChangeMonitor ? This would be similar to what's described here https://www.mssqltips.com/sqlservertip/6521/sync-sql-server-change-tracking-tables-without-changing-data/

The general idea here is to give customers a way to easily filter out rows from triggering the function. A couple ways we could do that :

  1. Have a hardcoded SYS_CHANGE_CONTEXT that we ignore when fetching changes. Customers could then use this in their own code to set the context when making changes they want to be ignored
  2. Allow customers to pass in their own values of SYS_CHANGE_CONTEXT to ignore

In addition to this, we should probably also make output bindings able to have a context set when making the upsertions so that people can easily chain together trigger & output bindings without running into the same problem above.

@Charles-Gagnon Charles-Gagnon added trigger enhancement New feature or request labels May 4, 2023
@Charles-Gagnon Charles-Gagnon added this to the Post GA milestone Jun 13, 2023
@MaddyDev MaddyDev assigned MaddyDev and unassigned MaddyDev Jul 10, 2023
@MaddyDev MaddyDev added the P2 label Feb 21, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request P2 trigger
Projects
None yet
Development

No branches or pull requests

2 participants