Capture previous value of the columns[Answered] RSS

• • Reply
  vignesht5

  Member

  38 Points

  250 Posts

  Capture previous value of the columns

  May 29, 2014 06:14 AM|vignesht5|LINK

  Hi,

  I have a table called Application which has information about applications.

   AppID      AppName     AppGroup     AppLocation     TeamSize    GrpSize 
    1         Nestle        Food           UK             6            12
    2         RedBull       Drink          USA            5            15 
    3          Ford        Vehicle         USA            9            25   

  Now when I update any rows in this Application table I want to capture the values to another table called AuditTable. Also I need to capture the previous values of TeamSize and GroupSize to that table too.

  For ex: When I update the first row then the below Audit Table values should be populted as shown below.

  Audit Table:

  But Audit table doesn't have OldTeamSize and OldGroupSize columns.

  AppID   AppName   AppGroup  AppLocation OldTeamSize NewTeamSize OldGrpSize NewGrpSize
    1      Nestle    Food       UK            6            3          12          8

  Really appreciate any suggestions or thoughts.

• • Reply
  MetalAsp.Net

  All-Star

  101931 Points

  20703 Posts

  Re: Capture previous value of the columns

  May 29, 2014 08:38 AM|MetalAsp.Net|LINK

  Using a trigger on the table is one option. 

  C# <--> VB.Net  |  jQuery Widgets
• • Reply
  Sam - MSFT

  Star

  10596 Points

  1379 Posts

  Re: Capture previous value of the columns

  May 30, 2014 07:04 AM|Sam - MSFT|LINK

  Hi,

  vignesht5

  Now when I update any rows in this Application table I want to capture the values to another table called AuditTable. Also I need to capture the previous values of TeamSize and GroupSize to that table too.

  For ex: When I update the first row then the below Audit Table values should be populted as shown below.

  Audit Table:

  But Audit table doesn't have OldTeamSize and OldGroupSize columns.

  Use UPDATE TRIGGER on the table.

  When you use trigger there are two MAGIC tables Inserted and Deleted that can be used.

  Generally Magic Tables are invisible tables, we can only see them with the help of Trigger's in SQL Server.

  Use with Triggers

  If you have implemented a trigger for a table then:

  1. Whenever you Insert a record on that table, that record will be shown in the INSERTED Magic Table.
  2. Whenever you Delete the record on that table, that record will be shown in the DELETED Magic Table Only. 
  3. Whenever you Update the record on that table, that existing record will be shown in the DELETED Magic Table and Updated new data will be shown in the INSERTED Magic Table.

  For more reference:

  Magic Tables in SQL Server 2012

  So, create the Update Trigger on the table. While an attempt is made to update the data in the table. The values that are deleted will be present in the Deleted Magic table. You may retrieve the value from there and insert it into any other table and store it.

  For Trigger Syntax refer to the following article:

  CREATE TRIGGER (Transact-SQL)

  The Trigger will look somewhat as below:

  The query below needs to be tweaked and perfected.

  create Trigger update_Application 
  ON Application
  For UPDATE
  As
  BEGIN
  declare @AppID int
  declare @AppName varchar(20)
  declare @AppGroup varchar(20)
  declare @AppLocation varchar(10)
  declare @OldTeamSize int
  declare @OldGrpSize int
  declare @NewTeamSize int
  declare @NewGrpSize int
  
  select @AppID=AppID,
  @AppName=AppName,
  @AppGroup=AppGroup,
  @AppLocation=AppLocation,
  @OldTeamSize=TeamSize,
  @OldGrpSize=GrpSize from DELETED
  
  select @NewTeamSize=TeamSize,
  @NewGrpSize=GrpSize from INSERTED
  Insert into Audit values(@AppID,@AppName,@AppLocation,@OldTeamSize,@NewTeamSize,@OldGrpSize,@NewGrpSize)
  END

   

  Hope it helps!

  Best Regards!