sql - Trigger insert old values- values that was updated

Question

I need to create trigger in SQL Server 2008 that gone insert all values from one row in which some value was changed into Log table!

For example if i have table Employees that have column id, name , password, and i update this table and insert new value for column name, than i need to insert values that was in table Employees after update in table Log.

How I can do this? Thanks!

Answer 1

In your trigger, you have two pseudo-tables available, Inserted and Deleted, which contain those values.

In the case of an UPDATE, the Deleted table will contain the old values, while the Inserted table contains the new values.

So if you want to log the ID, OldValue, NewValue in your trigger, you'd need to write something like:

CREATE TRIGGER trgEmployeeUpdate
ON dbo.Employees AFTER UPDATE
AS 
   INSERT INTO dbo.LogTable(ID, OldValue, NewValue)
      SELECT i.ID, d.Name, i.Name
      FROM Inserted i
      INNER JOIN Deleted d ON i.ID = d.ID

Basically, you join the Inserted and Deleted pseudo-tables, grab the ID (which is the same, I presume, in both cases), the old value from the Deleted table, the new value from the Inserted table, and you store everything in the LogTable