Ok back from Lunch Smiley

SQL and data; depending on what you need for the system sql triggers that log updates and deletes may be a good thing.

there are several ways to do this but they all center around the idea that a trigger will fire inthe sql server when the table is updated.

even if for example the user gets a sql connection via some other app that does not use your business logic, say they use MS access and mess with the data....

they will not know that the triggers will store the edits....

you can log the date and time of the edits and "undo" the chnages and possibly also log what sql user login was used to find the guilty parties.

by hand this audit trail can be a bee to create, google around and you will find many ways folks have done them.

one I read of but have not yet used is a SQL CLR based package.


with SQL CLR you could create one set of C# routines to do the work and one table to hold the log.

then you could add and remove logging on any table with minimal work.

example of log table:
ID
DateTime
TableRowID
TableName
ColumnName
OldValue
LoginUserID

so you take a table and select all the rows with that table name ordered by datetime desc and you can re-create any edits. or prior version of the data.
trade offs.. ok this writes a bunch of rows but only saves the columns you want to track, and works even if you alter the table at some point (well works as good as it can)

some folks create a dupe table and just dunmp the whole row....
but then you have 2 x the tables as your database and a different trigger for every table.