Sr. ID FName LName Mark
1 P1 amar ranjan 100
2 P2 sameer kumar 200
3 P1 amar ranjan 200
Here I updated the first row for Mark
column.
On each update to a single row I am creating the new row (see row 3) with updated value. Is it possible to get the column name which have been updated in the table?
3条答案
按热度按时间zqdjd7g91#
I used Mahmoud Gamal's answer and SQL Fiddle demo as a starting point.
What you need is possible, but if you have access to the update processes I would recommand to manage the updates in any a stored procedure which saved the changes directly in a table.
If you need a trigger, something like this can work, but as you see, it has it's own drawbacks: two temporary table because of the scope and a cursor.
It basicly generates a check for each column each time and inserts the new and old values for a column into the history table.
(a change on the ID column to Primary Key and Identity is recommanded too)
I also made an SQL FIDDLE demo
ep6jt1vc2#
Since you are using SQL Server 2008, you can use the
OUTPUT
clause to get the updated data. Something like:However if you need to insert ito another table
History
the data that was you can create aAFTER UPDATE
TRIGGER
to update the history table by the updated data, somthing like:SQL Fiddle Demo
l0oc07j23#
I would say, create another table called history and store what is updated & when.
Making duplicate entries in one table is BAD design. ALso I am not sure how you are handling duplicate entries?
Lets say you want to print data for
P1
how you are handling that?Edit 1
In one of my website, client wanted to know who added/ edited/ delete what and when. So I created new table called HISTORY and once add/ edit/ delete is done, I am making entry of that in that table. Creating many rows for one user is BAD design.