hi,
i am working in asp.net ,recentely i got one task,i need to create db which has few tables,one table name is "master_tables" which has table_id and table_name column(table name is all the tables names which is in db).
so,suppose there is 1 table name employee which has emp_id, name adress + two more columns name loghistory_id and table_row_id..(tablerowid value come from master_tables).. and another table is log_history_table..(which has loghistoryid,tablerowid,rowid,operation(insert,delete,update etc),user_name,comments) if i insert record through asp.net appllication,after inserting, the record should insert in both the tables means (in inserted table + log historytable)...in log history table i want..tablerowid from which table the record inserted,rowid,which rowid is inserted ,and which user inseted or deleted...and in inseted table historyrowid and table row id columns should also be update after inserting....i searched alot but i dint get any good result..will you plzzz tell me the proper logic..to solve this task..
its very important for me ....
Loading
mohit mahajanPosted Mar 16, 2012, 9:37 AM
SenthilkumarPosted Mar 16, 2012, 7:49 AM
You can see some examples of audit table...
http://www.mindsdoor.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html
http://www.mssqltips.com/sqlservertip/1468/designing-tables-for-audit-data-in-sql-server/
http://weblogs.asp.net/jgalloway/archive/2008/01/27/adding-simple-trigger-based-auditing-to-your-sql-server-database.aspx
mohit mahajanPosted Mar 16, 2012, 3:18 AM
will plz send one samle code for this,coz i m tring this at front end only..but how i will get the table_id in log history table.as i mention table rowid will come for master_tables in which all tables inserted,if i get the table row id from front end thn i can easily knw from which table the row id inserted and updated....and in lod history table i dont want any data change history,only thing i want who updated,deleted or inserted,which row id updated and from which table it is updated..
please solve me sample code for this,coz m not geeting any proper way to solve this..
SenthilkumarPosted Mar 15, 2012, 8:26 PM
I think you can do it in the front end.
The history table should be like this.
SID | table_name | column_name | TypeofOperation | PreviousValue | UpdatedValue | Lastupdated | LastUpdatedBy
1 employee EmployeeID INSERT NULL 1 03/03/2012 02:34:56 senthil
.
.
. Do for the all columns in the employee table
.
.
10 employee EmployeeName UPDATE SENTHIL SKUMAR 04/03/2012 11:23:24 senthil
Thats how you need to maintain the history table commonly.
Front end, you need to identify the operation. If it is insert then you need to only get the table schema and perform insert operation to the history table. If it is update then before update you have to get the value for the updating row and compare with the updating column value and if there is any different then only you have to update into history table.