December 20, 2005 at 7:37 am
Hello,
I wanted a query regarding archiving the data from the log files. Really wha t I wanted here is as follows:
When we change the data through the application, we can have the log to see who has changed what data. But when the data is modified through the SQL Server Enterprise Manager or Query Analyzer we can not have the log. But I think this will be recorded in the log files. But how can I archive this data. Means can I write some queries or stored procedures to get the modifications done in the database?
December 20, 2005 at 7:40 am
You can take one of two approaches.
One, you can put triggers on key tables where you want to monitor data changes. This is the most intrusive, but the cheapest, option.
Two, you can invest in log reading software like Apex SQL Log, Lumigent's Log Explorer, or Log PI. This will potentially require you to make some configuration changes to your logging configuration for your databases.
K. Brian Kelley
@kbriankelley
December 20, 2005 at 8:05 am
Hello,
Thanks for the reply. The idea of using the triggers may suite our purpose. But my audit log is designed in such a way that, it tracks even the data that has been inserted, deleted or updated. I need to insert both the old value and the new value. In this scenario, can I write the triggers to trace this and insert the data in the other table.
December 20, 2005 at 8:24 am
Yes. In the case of an INSERT, just look to use the inserted tablespace. In the case of a DELETE, use deleted. When you're firing a trigger on an UPDATE, the deleted tablespace contains the values of the rows which are changing as they appear before the UPDATE statement is executed. The inserted tablespace contains the rows changing as they appear after the UPDATE statement is executed. If you need to deal with text, ntext, or binary columns, look at using INSTEAD of triggers.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply