Insert record into transaction table for each column value change of my user table in SQL

Question

I have a user table with data

User_Name
User_Address
User_Gender

and so on..

Now my transaction table contains fields like:

Trans_Id
Trans_User_Field
Trans_Previuos_Data
Trans_Add_Date

Now in my ASP.net application when a user update their address or name or any other field on the page I have to compare that with USer table and insert a record for each updated field/Column into transaction table with previous data.

Here Trans_User_field gives you which field updated(User_Name, User_Address, User_Gender)

Please tell me what is the best way to do it. Do it on the SQL side or application side.

THanks

Wouldn't you just grab the current value from the User table, create a new record in the Transaction table, put the value in of the change, the date, and the field that the user has changed? I guess I don't understand what you're having trouble with. — mason, Jun 04 '13 at 19:59
I have like 200 fields on my page, i dont know how to identify which field user have changed.. — user1882705, Jun 04 '13 at 20:01
How are you presenting your data to the user? Is it a GridView? — mason, Jun 04 '13 at 20:03
What version of SQL server? You may have the change data capture feature available http://msdn.microsoft.com/en-us/library/cc645937.aspx — StingyJack, Jun 04 '13 at 20:20

score 1 · Answer 1 · answered Jun 04 '13 at 20:13

Though I may get dinged for this, because people vehemently hate triggers, I'm going to suggest one here. You could build one like this:

CREATE TRIGGER update_user ON table FOR UPDATE
AS

DECLARE @update_mask AS INT
SELECT @update_mask = COLUMNS_UPDATED()

IF ( @update_mask & 1 = 1 ) -- this means the first column was modified
IF ( @update_mask & 2 = 2 ) -- this means the second column was modified
IF ( @update_mask & 4 = 4 ) -- this means the third column was modified
IF ( @update_mask & 8 = 8 ) -- this means the fourth column was modified

and I think you get the idea. From there you can grab the updated value from the updated row and INSERT into your other table. See, using the COLUMNS_UPDATED method gives you some real flexibility. You could determine easily if a set of columns was modified by adding their bit values together and just looking for that. So let's say I wanted to know if the address and gender were both changed -for whatever reason -I could do this:

IF ( @update_mask & 6 = 6 ) -- both the second the third fields were modified

I think this wont work in my situation...I have nearly 200 columns in my table... — user1882705, Jun 04 '13 at 20:44

score 1 · Answer 2 · edited May 23 '17 at 11:57

1

How about trying an alternate approach. Create a Trans_User table will all the fields in User table and Trans_Date. Then create insert/update/delete triggers on User table to populate Trans_User table with the previous data. Take a look at this question or this Code Project article.

edited May 23 '17 at 11:57

Community

1
1

answered Jun 04 '13 at 20:14

Satish

2,710
5
30
43

mason · Answer 3 · 2013-06-04T20:33:46.747

Assuming you're using ASP.NET Web Forms.

In your .aspx page

 TextBox1: <asp:TextBox runat="server" id="TextBox1" /><br />
 TextBox2: <asp:TextBox runat="server" id="TextBox2" /><br />
 <asp:Button runat="server" id="Button1" OnClick="Button1_Click" Text="Submit" />

In your .aspx.cs page

 protected void Button1_Click(object sender, EventArgs e)
 {
      string original_text=GetOriginalTextOfTextBox1();
      if(TextBox1.Text==original_text)
      {
           //the text didn't change
      }
      else
      {
           //the text changed. need to update the transaction table and the user table.
      }
      string originaltext2=GetOriginalTextOfTextBox2();
      if(TextBox2.Text==originaltext2)
      {
           //the text didn't change
      }
      else
      {
           //the text changed. need to update the transaction table and the user table.
      }

 }
 protected string GetOriginalTextOfTextBox1()
 {
     //code here that gets the original value of TextBox1 from the User table.
 }
 protected string GetOriginalTextOfTextBox2()
 {
      //code here that gets the original value of TextBox2 from the User table.
 }

}

You'll probably want to combine all this using collections (List) and strongly typed objects once you have the concept down. This will minimize the number of calls to the database and simplify your code.

--Edit-- If you want to store all the history of a single update using a single record in the Transcation table, you need to modify the Transaction table to support all the fields at once. Note that this may not be as space efficient, depending on whether you expect users to update many fields per transaction or just one.

 Trans_Id
 User_Name
 User_Gender
 User_Address
 Trans_Add_Date

In this c# code how would i separate each field to know that this field is updated into transaction table. Because I am inserting only that one field/column in to the transaction table — user1882705, Jun 04 '13 at 20:13
Update the transaction table with multiple records, one for each field that was updated. — mason, Jun 04 '13 at 20:15
I dont want to insert multiple records for one filed update in the user page — user1882705, Jun 04 '13 at 20:21
Then add enough columns in your Transaction table to support all of the columns from the User table without needing to use multiple records per update. — mason, Jun 04 '13 at 20:27

Insert record into transaction table for each column value change of my user table in SQL

3 Answers3