[sql] Trigger insert old values- values that was updated

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!

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

    createTRIGGER [dbo].[Table] ON [dbo].[table] 
FOR UPDATE
AS
    declare @empid int;
    declare @empname varchar(100);
    declare @empsal decimal(10,2);
    declare @audit_action varchar(100);
    declare @old_v varchar(100)

    select @empid=i.Col_Name1 from inserted i;  
    select @empname=i.Col_Name2  from inserted i;   
    select @empsal=i.Col_Name2 from inserted i;
    select @old_v=d.Col_Name from deleted d

    if update(Col_Name1)
        set @audit_action='Updated Record -- After Update Trigger.';
    if update(Col_Name2)
        set @audit_action='Updated Record -- After Update Trigger.';

    insert into Employee_Test_Audit1(Col_name1,Col_name2,Col_name3,Col_name4,Col_name5,Col_name6(Old_values)) 
    values(@empid,@empname,@empsal,@audit_action,getdate(),@old_v);

    PRINT '----AFTER UPDATE Trigger fired-----.'

ALTER trigger ETU on Employee FOR UPDATE AS insert into Log (EmployeeId, LogDate, OldName) select EmployeeId, getdate(), name from deleted go

In SQL Server 2008 you can use Change Data Capture for this. Details of how to set it up on a table are here http://msdn.microsoft.com/en-us/library/cc627369.aspx