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

Question

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

There are many options, you can use a trigger, you can use a stored procedure, you can do it in code. — Dale K, Aug 14 '20 at 10:42

score 1 · Answer 1 · answered Aug 14 '20 at 10:41

You have described what a trigger does.

create trigger trig_member_insert on members after update
as
begin
    insert into notifications ( . . . )
        select . . ., i.points as new_points u.points as old_points   -- what you want to insert
        from inserted i join
             updated u
             on i.member_id = u.member_id
        where u.points <> i.points
end;

Storing something called "points" as a string seems like a very poor choice. It sounds like a number.

score 1 · Accepted Answer · answered Aug 14 '20 at 11:03

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 :

Thank you but can you please elaborate a little why triggers are bad? What advantage does output clause have over triggers? — Kaan Taze, Aug 14 '20 at 16:35
there's plenty of blog about triggers in SQLServer, here's some reading. https://www.red-gate.com/simple-talk/sql/t-sql-programming/triggers-threat-menace/ https://stackoverflow.com/questions/460316/are-database-triggers-evil at one of my client, they were using an ERP that used trigger for their audit module and was poorly design that it was able to deadlock itself. but sometimes, you can't do without, especially if you're are not legally allowed to modify the SP for a commercial product. — MLeblanc, Aug 14 '20 at 17:10

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

2 Answers2