Skip to main content

Notifications

Community site session details

Community site session details

Session Id : a+j2tRDaJHKSCH+WyNEXXI
Power Automate - Building Flows
Unanswered

SQL: Get row changes by column with change tracking - trigger email on specific value change

Like (0) ShareShare
ReportReport
Posted on 24 Sep 2020 12:52:21 by 8

Hi,

I'm looking for a trigger that i can apply that trigger something based on a specific value.

 

Situation:

I have a table with "PersonId - Guid, Name - String, IsActive - bool"

When the value of IsActive is changed to false, i would like to send an email to someone.

 

I experimented with the Flow template "SQL: when row modified/changed V2"

In my situation this doesn't help, as i don't know which value of my row has changed.

Yes i know about filters, but in case I use a Filter i would still send my email multiple times and repeatedly as long the condition is met.

 

The perfect solution for me would be to combine this statement now with the integrated SQL Column Change tracking.

For column change tracking i need to save somewhere the @Last_synchronization_version in order to build a future query on that.

 

Has someone already experimented in this direction how to achieve that?

 

 

 

 

 

  • bkeano Profile Picture
    136 on 11 Nov 2021 at 14:58:44
    Re: SQL: Get row changes by column with change tracking - trigger email on specific value change
    Spoiler (Highlight to read)

    Hi @rth 

     

    I know this is old, but in case it is useful to someone else looking for a solution, there is another option, but does also involve some set-up in SQL. That is putting an SQL  'update' trigger on your tables column.

    This trigger then fires anytimethat column, and only that column, changes.

     

    Second you then add an action to the trigger which will insert the values of the changed reocrd, so you can identify it, to another new  table.

    This second table has a primay key. You then run your flow of this second table.

     

    So in your example , below would be the trigger, just make sure you create the new table first

    create TRIGGER [dbo].[IsActive_change] ON [dbo].[Original_SQLTable]
    AFTER UPDATE
    AS
    if (update (IsActive))
    BEGIN
    SET nocount ON;
    IF EXISTS (Select * FROM Original_SQLTable)
    BEGIN
    INSERT INTO NewTable (PersonId, Name, IsActive , DateAdded)
    SELECT i.PersonId, i.Name, i.IsActive, GETDATE() from inserted i --inner join inserted i
    END
    END
    GO
    
    ALTER TABLE [dbo].[Original_SQLTable] ENABLE TRIGGER [IsActive_change]
    GO

    I've used this a few times, I hate having to have set-ups in SQL and flow to support an automation, but in this case, it adds extra maintenance, but can't see there is another option, other than using a difernet type of DB like SharePoint DB with version control.

     

    Hope this helsp somebody

     

    Hi   I know this is old, but in case it is useful to someone else looking for a solution, there is another option, but does also involve some set-up in SQL. That is putting an SQL  'update' trigger on your tables column.This trigger then fires anytimethat column, and only that column, changes. Second you then add an action to the trigger which will insert the values of the changed reocrd, so you can identify it, to another new  table.This second table has a primay key. You then run your flow of this second table. So in your example , below would be the trigger, just make sure you create the new table firstcreate TRIGGER [dbo].[IsActive_change] ON [dbo].[Original_SQLTable] AFTER UPDATE AS if (update (IsActive)) BEGIN SET nocount ON; IF EXISTS (Select * FROM Original_SQLTable) BEGIN INSERT INTO NewTable (PersonId, Name, IsActive , DateAdded) SELECT i.PersonId, i.Name, i.IsActive, GETDATE() from inserted i --inner join inserted i END END GO ALTER TABLE [dbo].[Original_SQLTable] ENABLE TRIGGER [IsActive_change] GOI've used this a few times, I hate having to have set-ups in SQL and flow to support an automation, but in this case, it adds extra maintenance, but can't see there is another option, other than using a difernet type of DB like SharePoint DB with version control. Hope this helsp somebody 
  • Amanthaper Profile Picture
    441 on 24 Sep 2020 at 14:31:56
    Re: SQL: Get row changes by column with change tracking - trigger email on specific value change

    Not sure if this is an option for you.

    https://docs.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-change-data-capture-feature-portal

     

  • rth Profile Picture
    8 on 24 Sep 2020 at 14:26:53
    Re: SQL: Get row changes by column with change tracking - trigger email on specific value change

    @ChristianAbata 

    Is there some feature I'm not aware about? - I mean I can't export my whole database to sharepoint...or whats the idea behind?

  • rth Profile Picture
    8 on 24 Sep 2020 at 14:25:48
    Re: SQL: Get row changes by column with change tracking - trigger email on specific value change

    Hi, 

    Unfortunatly CDC is not available in SQl Azure

  • ChristianAbata Profile Picture
    8,947 Most Valuable Professional on 24 Sep 2020 at 14:23:29
    Re: SQL: Get row changes by column with change tracking - trigger email on specific value change

    mmmm @rth  unterstand, I sugest you to use sharepoint and then you can have like a mirror data base to compare values so then you can use a condition to see if the value real changes.

  • Amanthaper Profile Picture
    441 on 24 Sep 2020 at 14:19:41
    Re: SQL: Get row changes by column with change tracking - trigger email on specific value change

    Hi,

    You are on the right track with your approach.

    I have done a similar project by using SQL Server's CDC features. Once you have enabled CDC on SQL server and start monitoring  your table(s) using CDC, the SQL Agent CDC jobs will track all data changes and assign operators which can then be used to get specific change actions and run all sorts of downstream process.

     

    Here is how we are doing this with CDC enabled tables and a "When an item is modified (V2)" flow.

     

    1. CDC has been enabled on our Staging table

     

    PA_UG_CDC1.png

    2. The CDC table has captured some events, the last 2 are update events. You can see the "__operation" set to 3 then 4 in the last 2 columns where the data has changed in a "from" and "to" scenario.

     

    PA_UG_CDC3.png

    PA_UG_CDC4.png

    3. There is another step after this which runs a merge procedure to check for Matched and Unmatched data between the CDC table and the staging table and inserts\updates any changes into a "Flow Source" table. This table is mapped in our flow and any time there are any data changes (or inserts) to it our flow event triggers and performs some actions (emails, data uploads, etc...).

    PA_UG_CDC6.png

     

    CDC capture all change events by using the following operators:

    1 = delete
    2 = insert
    3 = update (captured column values are those before the update operation). This value applies only when the row filter option 'all update old' is specified.
    4 = update (captured column values are those after the update operation)

     

    Here's some documentation https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/cdc-fn-cdc-get-all-changes-capture-instance-transact-sql?view=sql-server-ver15

     

    Hope this helps,

    Aman Thaper

     

  • rth Profile Picture
    8 on 24 Sep 2020 at 13:36:48
    Re: SQL: Get row changes by column with change tracking - trigger email on specific value change

    @ChristianAbata 

    Hi,

    Thanks for your reply. I have already used this, but this doesn't really show me which row was modified, or triggers multiple times.

    So let's assume i want a trigger when the value of field ACTIVE is changed to false.

     

    I have a very simplified sample

    Base::  PersonId: 1   Name: Max:  Active: true

    Change 1:  PersonId: 1   Name: Max:  Active: false  -- > this triggers "when item is modified", which triggers an email.

     

    Change 2: PersonId: 1 Name: MaxNewName Active: false --> this triggers now again "when item is modified", which triggers again an email.

    Result: I get now an email for each and every change on the datarow, but what i want is an email on change on specific columns.

    Problem: "When item is modified" and "Get Row" does not show which column was modified.

     

    I could get the modified columns when combining it somehow with change-tracking CHANGE_TRACKING_IS_COLUMN_IN_MASK()

    But therefore i need to save somewhere something like a @lastSyncrowVersion in order to query >Version

     

    Thomas

     

  • ChristianAbata Profile Picture
    8,947 Most Valuable Professional on 24 Sep 2020 at 13:06:40
    Re: SQL: Get row changes by column with change tracking - trigger email on specific value change

    hi @rth  you can use SQL: when row modified/changed V2 and then Get row

     

    table.PNG

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,743 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,089 Most Valuable Professional

Leaderboard
Loading started