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

[rth]

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?

[ChristianAbata]

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

[rth]

@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

[Amanthaper]

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

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.

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

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

Hope this helps,

Aman Thaper

[ChristianAbata]

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.

[rth]

Hi, 

Unfortunatly CDC is not available in SQl Azure

[rth]

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

[Amanthaper]

Not sure if this is an option for you.

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

[bkeano]

Spoiler

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