April 19, 2012 at 3:11 am
Hello,
I have a task on hand for which I do not have any clue to do it. Let me explain the scenario. We have employee records table, in which we store all the employee info along with monetary benefits. Once an employee gets hike the existing salary is updated with the new one. How to maintain a track of these changes. Now when the user picks up an employee and checks his salary for a particular month, we should be able to pick the salary in that month and not the current one.
Even if we use triggers, I am not getting an idea how to retrieve data basing on the selected month.
Any sort of lead/help is greatly appreciated.
Thanks in anticipation.
Srinivas
April 19, 2012 at 5:07 am
Which version of SQL Server you are using? SQL Server 2008 and above have Change Tracking feature which you can use.
Alternatively, you need to maintain another table where you can log old value before updating.
These links may help:
Log changes made to all fields in a table to another table (SQL Server 2005)
How to organize updated row logging
One other thing comes to my mind is to modify Employee Salary table as below:
EmpID
Salary
SalaryDate
Add these columns in either a new table or modify your existing. Whenever salary is hiked, store new salary and date when hiked in these columns.
This way you can easily query salary of back dates.
======================================
Blog: www.irohitable.com
April 19, 2012 at 7:40 am
Don't use CDC. CDC is best for auditing, not for actually storing historical data. You could do a trigger that simply writes all information to a history table. If this is an application, write it into the stored procedure that updates the table and use CDC to audit manual changes of the data.
Jared
CE - Microsoft
April 20, 2012 at 3:38 am
I have to do this often on with the type of application I author.
My standard method is to create a history table which is populated via the INSERT and UPDATE stored procedures of the table holding the current data.
The history table contains a datetime column defaulted to GETDATE, a foreign key to current data, a foreign key to the user who actioned the change and the change itself.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 20, 2012 at 4:17 am
These are problems that are probably best addressed during the initial design of a database.
If you find yourself doing a lot of this then it can be an indicator that the database has not been designed to suit requirements.
That being said, the suggestions previously made can be used as a "quick fix" as I guess requirements do change even after a database has gone into production.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply