SQL Server trigger multiple update rows

Question

I am using a trigger on my table for update.

When I was updating 1 row, it works. But when I update 20 rows at once, the log only show 1 history update.

I want to show all updated rows. How to make it?

This my simple sql:

create trigger Triger_Update_Product
on product 
for update
as begin
    declare @first char(10)
    declare @after char(10)

    select @first = name from deleted

    select @after = name from inserted

    insert into historyproduk
        select @first, @after, getdate()
end

msdn.microsoft.com/en-in/library/ms190752.aspx

sandeep rawat
– sandeep rawat

2017-02-14 05:51:56 +00:00
Commented Feb 14, 2017 at 5:51 — sandeep rawat
– sandeep rawat, Commented Feb 14, 2017 at 5:51

marc_s · Accepted Answer · 2017-02-14 05:57:24Z

You need to write the trigger in a set-based fashion and be aware that inserted and deleted will contain multiple rows - so these lines of code are really bad:

select @first = name from deleted
select @after = name from inserted

These select one arbitrary row from the update set - and they ignore all other rows. Don't do this!!

Try this instead:

create trigger Trigger_Update_Product
on product 
for update
as begin
    insert into historyproduk
        select d.name, i.name, getdate()
        from deleted d
        inner join inserted i on d.PrimaryKey = i.PrimaryKey
end

You need to join the Inserted and Deleted pseudo tables on the primary key and then grab the Name from the old (deleted) and new (Inserted) and insert those - along with the date/time stamp - into the history table in a single INSERT ... SELECT .... statement to handle multiple rows being updated

Collectives™ on Stack Overflow

SQL Server trigger multiple update rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related