asp.net mvc - How To create Audit table in sql server ? i need to create each audit table or can manage single table all audit -

i using mvc5. created entity framework. have totally 15 tables . want recent activity in project . have plan use audit table . need create audit each table or single table can mange ? fields come on inside audit table ?

i have tp_users table

id  int unchecked username    nvarchar(50) useremail   nvarchar(50)     displayname nvarchar(50)     password    nvarchar(50)     roleid      int isactive    bit  clientid    int  

now want create audit table tp_users table .whare fields come inside audit table ? how use audit table entity framework ?

we use entity below:

public class audit {     [required]     [stringlength(6)]     public string action { get; set; }     public string changes { get; set; }     public string pk { get; set; }     [required]     public datetime revisionstamp { get; set; }     [required]     [stringlength(50)]     public string tablename { get; set; }     [required]     [stringlength(50)]     public string username { get; set; } } 

we use auditing of our tables. action property says type of change has occurred 'update', 'insert' or 'delete'. column values before , after change saved in changes column json below:

[{"fieldname":"id","valuebefore":"2","valueafter":"2"},{"fieldname":"settingtypeindex","valuebefore":"fiscalyear","valueafter":"fiscalyear"},{"fieldname":"value","valuebefore":"2015","valueafter":"2016"},{"fieldname":"year","valuebefore":"0","valueafter":"0"},{"fieldname":"dateinserted","valuebefore":"2016-04-11 8:45:08 am","valueafter":"2016-04-11 8:45:08 am"},{"fieldname":"datemodified","valuebefore":"2016-07-28 9:45:46 am","valueafter":"2016-07-28 9:47:17 am"},{"fieldname":"creatoruserid","valuebefore":"b44792db-1fbd-44d2-9f7d-cf05c6a2f922","valueafter":"b44792db-1fbd-44d2-9f7d-cf05c6a2f922"},{"fieldname":"updateruserid","valuebefore":"b44792db-1fbd-44d2-9f7d-cf05c6a2f922","valueafter":"b44792db-1fbd-44d2-9f7d-cf05c6a2f922"}]

so way can have rollback functionality (if ever needed). primary key of changed record saved in pk property. date of change saved in revisionstamp propery , table name , username saved in tablename , username properties respectively. of course entity mapped table in database , data persisted in table.

if using entityframework can override dbcontext.savechanges() method , changetracker.entries().where(e=>e.state != entitystate.unchanged) items , create audit data using items.