Click here to Skip to main content
16,017,651 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am developing an application and in which i have multiple tables,

Table Name : User_master
|Id (PK,AI) | Name | Email | Phone | Address

Table Name: User_auth
|Id (PK,AI) | user_id(FK_userMaster) | UserName | password | Active_Status

 Table Name: Userbanking_details
 |Id (PK,AI) | user_id(FK_userMaster) | Bank Name | account Name | IFSC

Now, what i want is to save all the updates done in records should not be updated directly instead it should control the version that means i want to track the log of all previous updates user has done.

which means if user updates the address, then also previous address record history should be stored into the table.

How can i achieve this?


What I have tried:

I have tried it by adding fields version_name,version_latest,updated_version_of field and insert new record when update like

|Id (PK,AI) | Name | Email | Phone | Address |version_name |version_latest| updated_version_of
    1       | ABC  |ABC@gm.com|741852|LA     |1            |0             |1
    2       | ABC  |ABC@gm.com|852741|NY     |2            |1             |1   

Now the problem comes here is the user table is in FK with other two listed tables so when updating the record their relationship will be lost because of new ID.

I want to preserve the old data shown as old and new updated records will be in effect only with new transactions.
Posted
Updated 27-Aug-19 6:29am
Comments
Santosh kumar Pithani 26-Aug-19 9:04am    
Hello, i hope this problem will be overcome by using "on update cascade" foreign key reference and also write one stored procedure for updating old value to other column.

If you're using SQL Server 2016 or later, then use a system-versioned temporal table:
Temporal Tables - SQL Server | Microsoft Docs[^]

Otherwise, create a separate "history" table for each tracked table. Eg:
User_master:
============================================
| Id (PK) | Name | Email | Phone | Address |
============================================

User_master_history:
============================================================================
| Id (PK,FK) | Version (PK) | Name | Email | Phone | Address | DateChanged |
============================================================================
The history table has a compound primary key on the ID and version number, and a foreign key from the ID to the main table.

NB: If any record is deleted from the main table, the history of that record will also be deleted. If that is not what you want, then don't create a foreign key between the history table and the main table, and ensure that IDs from deleted records are never reused in the main table.
 
Share this answer
 
If you don't have SQL Server 2016 but do have triggers, you could use a trigger to track any changes in your table. In the trigger, you could save the relevant history information in a history table.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900