I need to have a modification log table, which will carry the table name, column name, modified user name, old value (old data) and the updated timestamp for all updation that happens in the database. How can I achieve this?
Your triggers should reference virtual tables used by SQL Server during the transaction. The two tables are named "inserted" and "deleted", and have a structure identical to that of your production table. "inserted" contains all the records being inserted into your table. "deleted" contains all the records being deleted from your table. When an update occurs, the records is inserted and deleted, and thus will appear in both virtual tables giving you a "before and after" view of the transaction.
In you trigger, you can select from or join the virtual tables just like any other table or view.
If it's not practically useful, then it's practically useless.