Logging

cdpace

Hello guys, I would like to create a log of what is happening in my database. What i exactly mean is that i need to log every update, insert and delete statement that executes in my database. for example when a delete happens i want to keep the values that have been delete, when an update happens i want to keep the old values and the new ones etc. Another important thing is that i want to achieve this without the use of triggers is possible. How can this be done?

Ashfield

cdpace wrote:

Another important thing is that i want to achieve this without the use of triggers is possible

Can't see an alternative to triggers myself. How else do you effectively capture the insert/update/delete events.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

cdpace

ok got your point can i ask you one more thing what are the column names for the the following tables that are used in triggers Table names: inserted deleted

Blue_Boy

Check this.[^]

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

Ashfield

The column names for inserted and deleted are the same as the table the trigger is for. F|or inserts just inserted is poulated, for deletes just deleted is populated and for an update, guess what, they are both populated. I often select the inserted & deleted as xml and store them in a songle audit record. Hope this helps

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

cdpace

This helps allot thank you very much. Can i ask you one last thing have any links that show how i can retrieve the data as xml? thank you very much

Ashfield

Just take a look at BOL or google, is not difficult.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

i i i

In Synopsis Go to Sql Server Management Studio object Explorer Select Server Right Click on Server goto Properties then Navigate To Securities -----> Enable C2 audit trailing thats all the only disadvantage of this is that it consumes more space on HDD but it is the requirement and now days with the size of HDD it does not matter

Best Of Regards, SOFTDEV Sad like books with torn pages, sad like unfinished stories ...

i i i

Triggers are hell slow don't use it use C2 audit

Best Of Regards, SOFTDEV Sad like books with torn pages, sad like unfinished stories ...

Ashfield

softdev_sup wrote:

Triggers are hell

True, if badly written, but we use them regularly for auditing on tables with upwards of 20 million transactions a month withu problems.

softdev_sup wrote:

use C2 audit

Never used it, does it acually record before and after images, user etc? Also I would be worried about it bringing the system down - from MSDN Important: C2 audit mode saves a large amount of event information to the log file, which can grow quickly. If the data directory in which logs are being saved runs out of space, SQL Server will shut itself down. If auditing is set to start automatically, you must either restart the instance with the -f flag (which bypasses auditing), or free up additional disk space for the audit log.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

i i i

well i tried one software ApexSQLAudit for the auditing sql and it uses triggers and the system performance after using it was so bad almost dead while beofre using this ApexSQLAudit the system was efficient but after this it was very slow and c2 and sql profiler trace did the job i am confused now because that system is running now more then 2 years and is not crashed but by using the triggers we had to remove ApexSQLAudit within one week because of slowness

Best Of Regards, SOFTDEV Sad like books with torn pages, sad like unfinished stories ...

Ashfield

So you used a generic logging system and it slowed your system. No big suprise there. As I said, we use triggers for auditing and have never had performance issues, but we write them ourselves so they are optimised for our system, with the audit tables properly located and indexed etc. You may have had a bad experience with triggers, but do not assume they are bad, its just a case of using them properly - I have no experience of ApexSQLAudit, but I wouldn't expect some generic product to be as performant as one written specifically for your database by someone who knows what they are doing.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP