While updating table1, how do I INSERT to table2 for every change in table 1?

I have a MEMBER table and NOTIFICATION table. On client side, I list all of the records in MEMBER table and there is a points column and this is shown as text input. So after I change the values for some members, I can click save button and this will update the records in my MEMBER table that’s all right,

But the thing I want to accomplish is for every record whose points value has changed I want to INSERT a record in my notifications table.

I couldn’t think of anything, how can I approach to this problem?

For notifications I made 3 tables by following the article in here

Answer

Use the output clause instead of trigger, they are bad.

You need the condition “where data_old <> data_new” case if you updated a column with the same value, SQL Server marked it as changed, even if the value hasn’t changed

create table #example (id int identity(1,1) not null, data nvarchar(max));
insert into #example (data) values ('value 1'),('value 2'), ('value 3');

create table #audit  (id int, data_old nvarchar(max), data_new nvarchar(max), [When] datetime not null default (getdate()));

insert into #audit (id, data_old, data_new) 
select id, data_old, data_new
from (
    update #example 
    set data = 'value changed' 
    output inserted.id, deleted.data as data_old, inserted.data as data_new
    where id = 2
)changed (id, data_old, data_new)
where data_old <> data_new
select * from #audit

will result with this in #audit :