How to control version of records with referenced records (foreign keys)

Question

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 25-Aug-19 17:30pm

Naikniket

Updated 27-Aug-19 6:29am

Add a Solution

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.

2 solutions

Solution 2

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.

Posted 31-Jan-20 20:59pm

Bertha Wwallace

Updated 31-Jan-20 21:15pm

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2019-08-27T06:30:00

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.