For insert and update there is a simple method: add a timestamp filed, and a trigger to update it. And you don't need to (should not) interfere with it from code. But this is rarely enough.
If you really need that for deletion too, you have do implement audit log functionality. Here is a good solution:
http://www.sqlservercentral.com/Forums/Topic1307316-392-1.aspx[
^].
If you need to log the user also, that can make it more complicated depending on your application's authentication scenario.
Here is a good comparison of auditing methods you can deal with:
http://www.slideshare.net/sqlserver.co.il/a-comparative-analysis-of-auditing-solutions-in-sql-server[
^]
Submit an article or tip