Hi guys.

On a previous project, the requirement was just to store who updated a
row, and when. So, we have a:

created_date, created_user_id, modified_date and modified_user_id

However, phase 2 of this project - they want to know which fields in
the row were edited by which user! So, basically, per field auditing.
This is something I have never done. There's also two ways a field can
be updated. It can be updated by a user, or via an automated process,
at which point, a user should be stopped from editing it (client side
enforcement). So, I was thinking that we could make a nullable field
for the updated_user.. and if it's null, it was automated, and
therefore locked...

But.. this has to cover all the columns! It would be very messy to
have, for example, a 'description' field, and then a
'description_modified_date', and 'description_modified_user_id'....

I was thinking maybe a separate table that covered all tables and
columns that we need to log, and then a trigger to update these? But
am not sure if this is a good move, or the most effective way of
handling this requirement.

When we display the screen, it would be a hefty procedure to populate
the screen! We'd not only have to get the data, but then get all the
audit data - so that we can show the developer if the user can edit
the data or not.

Headache! Unless there is a way that this is done previously?

Craig what versrion of SQL Server are you using?


"Craig Lister" <cdotlister (AT) gmail (DOT) com> wrote

Craig Lister (cdotlister (AT) gmail (DOT) com) writes:
If you are on SQL 2008 and have Enterprise Edition you could consider
using SQL Server Audit for the task.

Else a solution which is available with SQL 2005 and with any edition
is to use XML. Here is a quick example from a database that I have:

INSERT auditlog (tablename, keyvalue1,
action, beforeimage, afterimage)
SELECT 'accounts', coalesce(i.accno, d.accno),
CASE WHEN i.accno IS NULL THEN 'D'
WHEN d.accno IS NULL THEN 'I'
ELSE 'U'
END,
(SELECT * FROM deleted d2
WHERE d2.accno = i.accno FOR XML RAW, TYPE),
(SELECT * FROM inserted i2
WHERE i2.accno = i.accno FOR XML RAW, TYPE)
FROM inserted i
FULL JOIN deleted d ON i.accno = d.accno

As you can tell from the appearance of inserted/deleted, this is part
of a trigger. In this example, accno is the primary key of the table.

That is, in this table we store a copy of the row as it looked before and
after the update.

Currently we don't use this table for anything but manual investigations,
but an idea is to write a generic GUI which permits the user to browse
changes. The GUI would present the table names and the field names,
but only show the differences. It would may be not be the best user
experience, but it would be a lot easier to implement.

(In the example above, you don't see username, the time for the operation
etc. There are column for this in the table, but as they have default
values, you don't see them in the SELECT statement.)


This is certainly an extra challenge, and it does not sound like a
requirement that is particularly cheap to implement. You could
use the audit table to find out if the most recent change was
made by automatic user, but in the model above it's not easy to dig
out this information.

You should probably track this status separately from auditing. Maybe
a table keyed by tablename, keyvalue and column name. You add a row
when a column gets locked. But I cannot say that I like this terribly
much. Having metadata as data in the user tables is OK for auditing,
but when you use it for enforcing business rules, it's less appealing.
But the alternative is to have a "is_locked" column for every column
which is unpleasant as well.

Usually, these kind of ugly requirments tends to evaporate. If you
tell the people who decides that this requirement will cost an arm
and a leg to implment they may reconsider. Maybe locking is only needed
on row level?


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi Craig,

if i understand you problem right, may be the OUTPUT clause will help you
tracking all that individual values.

USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID smallint, Name varchar(50),
ModifiedDate datetime);

INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate INTO
@MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO

Above Example (from Help *s*) will show you how you can use it. In that
example only inserted rows and columns (!) are tracked in the temp-table.
You can use any other valid technic instead of a temp-table - but do so is
the most flexibal one. The OUTPUT-Clause will work on an UPDATE and DELETE
also.

And via SQL-Functions like SUSER_SNAME and/or SUSER_SID you can track
additional information about the current logged in user.

Try out and let me know if that helps

Regards
Jan Waiz

"Craig Lister" <cdotlister (AT) gmail (DOT) com> schrieb im Newsbeitrag
news:ad51480b-80ab-4859-8290-81d80aeffd19 (AT) x18g2000pro (DOT) googlegroups.com...

On Jul 5, 2:46*pm, "Uri Dimant" <u... (AT) iscar (DOT) co.il> wrote:
Microsoft SQL Server 2005 - 9.00.4226.00 (Intel X86)

Just reading the other replies.... thanks guys.

Hi Craig,

did you find a solution?

regards

"Craig Lister" <cdotlister (AT) gmail (DOT) com> schrieb im Newsbeitrag
news:ad51480b-80ab-4859-8290-81d80aeffd19 (AT) x18g2000pro (DOT) googlegroups.com...

I haven't yet found anything reusable and simple to impliment. I think
it's going to require a major rework of what we're doing at the
moment.
I'm leaning towards triggers.. and then having a 'ghost' table,
matching the table that's being edited... and it will basically write
a new row to the ghost table, everytime a change is made.

So, lets say we have a table named Applications. I think we'll add an
'Application_change_history' table. Both tables have a
'Last_Update_User field, and a Last_Update_Date field. So, when the
user clicks Save (or what ever), it generates an UPDATE into the
Applications table... and then does a 'SELECT FROM Applications INTO
Application_change_history.

Then, to find out who made a change to a particular field... we can
then compare 'versions' in the Application_change_history table... and
look for what fields have changed. We can then somehow find the name
of the user who changed those fields.

Issue with this is that this history table will grow a lot! For every
update, there will be a new row. Also, it won't be very easy to query.
That is, a business rule we have is that if a field was altered by an
external source, then users can no longer edit it. it must be read-
only. So, when we build the screen, it will be pretty inefficient for
the database to work out who last changed which fields...

So, a bit stuck right now...

Craig Lister (cdotlister (AT) gmail (DOT) com) writes:
Yes, but the data may be archivable?

Depends for what. For a generic comparison of data, that could be done.

But this is a killer requirement, and whatever the solution it will be
expensive. Personally, I lean towards that should keep this disctinct
from the auditing. Maybe even as heavyduty like a xxxisfrozen column for
every column to which this rules apply.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx