Capture previous value of the columns

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.

Answer 1

User-1360095595 posted

Using a trigger on the table is one option. 

Answer 2

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!