locked
Capture previous value of the columns RRS feed

  • Question

  • User-1888080761 posted

    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.

    Thursday, May 29, 2014 6:14 AM

Answers

  • User1140095199 posted

    Hi,

    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!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 30, 2014 7:04 AM

All replies

  • User-1360095595 posted

    Using a trigger on the table is one option. 

    Thursday, May 29, 2014 8:38 AM
  • User1140095199 posted

    Hi,

    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!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 30, 2014 7:04 AM