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

User · Answer

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

User · Answer

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

User · Answer

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-----

User · Answer

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

User · Answer

Here s an example update trigger   create table Employees  id int identity  Name varchar 50   Password varchar 50   create table Log  id int identity  EmployeeId int  LogDate datetime       OldName varchar 50   go create trigger Employees Trigger Update on Employees after update as insert into Log  EmployeeId  LogDate  OldName   select id  getdate    name from deleted go insert into Employees  Name  Password  values   Zaphoid    6   insert into Employees  Name  Password  values   Beeblebox    7   update Employees set Name    Ford  where id   1 select   from Log   This will print   id   EmployeeId   LogDate                   OldName 1    1            2010-07-05 20 11 54 127   Zaphoid

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

Examples related to sql

Examples related to tsql

Examples related to sql-server-2008