Logging data changes done by user

You can't get the user login details from the sql transaction unless every individual uses a different sql login to the server. By the sound of your question, you don't use individual logins (This is a good thing btw, but not very helpful)

Step 1: You have to log a 'modified by', date and type in your data. You can easily add such a column to an existing table:
SQL: ALTER TABLE Statement[^]
I also include a "created by" in my data, but that would be overkill for you.

Step 2: Create your triggers based on Update and Delete (if applicable).
You don't need an insert trigger. Instead log the "deleted" side of any update trigger. This way your 'log' table will contain the previous values and your actual table has the 'what is was changed to' values.
The same is true for the delete statements. This will log the last value before it was removed from the table.

Step 3 ????
Step 4 Profit (Just kidding :P)


So your data will go through this life-cycle

Insert:
Table                             Log table
[values1],modified-by, date1      null

Update 1
Table                             Log table
[values2],modified-by, date2      [values1],modified-by, date1, 'update'

Update 2 
Table                             Log table
[values3],modified-by, date3      [values1],modified-by, date1, 'update'
                                  [values2],modified-by, date2, 'update'

Delete
Table                             Log table
null                              [values1],modified-by, date1, 'update'
                                  [values2],modified-by, date2, 'update'
                                  [values3],modified-by, date3, 'update'
                                  [values3],deleted-by, datenow, 'delete'

As you can see there are 2 entries for Values3. This is because we must log the original modification that we would normally find in the main Table and we must log the delete action.

I hope that's clear
Let me know if you need any further clarification.
Andy ^_^

---