show changes from and changes to in a history table

Question

Post reply

show changes from and changes to in a history table

idaaureajimenez

Valued Member

Points: 74
More actions
June 28, 2014 at 12:03 am

#396977

hi! I have a table history of Employee data.
id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Megha P SSCommitted Points: 1976 More actions · Answer 1

I think, you need to have audit table which would be same as original table with

two more fields version and action .action will store if it was insert,update or delete.

create after insert ,update,delete trigger on original table.So from that audit table you can get which field is changed and updated value as well.

There can be other better approach for this.

idaaureajimenez Valued Member Points: 74 More actions · Answer 2

I thought of that too, but the instruction does not let me create a new table for that.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 3

Is that table encrypted by any chance?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Xedni SSCertifiable Points: 6951 More actions · Answer 4

Just curious, why are you unable to create an additional support table? I'm unable to think of a way you could have full DML history on demand without having some supporting objects to audit changes.

Executive Junior Cowboy Developer, Esq.[/url]