legacy database - updating columns

Question

Hi, I have a database in which tables have 4 common columns - Createdby,createdon,modifiedby,modifiedon. Now, the purpose of these columns is track who modified the record.Is there an alternative to this design? Also, how to update these columns - Should I use triggers? But, for "by" columns I need the userid.(we are using linq-to-sql)

thanks

Answer 1

Assuming that you still want to keep track of who changed and created each row and when, you can either have what you have now or have a separate table just to keep track of the changes, using triggers to write to that table every time that a change has happened on one of the other 4 tables.

Yes, usually triggers are used to update those kinds of columns. The userid shouldn't be a problem when you save the trigger. You may be using Linq but that should not matter because the trigger will be in SQL Server anyway.