Hello

I have to implement a functionality for audit ( insert/update/delete ) log of the each table.
There are two option
1) Through SQL Server 2008 Feature : I heard that in the SQL Server 2008 we have CDC(Change Data Capture) features which can be enable on each table and track each data changes. It insert new row into a special system table which we need audit log.
2) Create a un normalized table for each table and handle the audit (insert/update/delete) log using the trigger.

Can anybody help to me to choose which options is best and easy to manage in the future?

Thanks,
Craig
Database Development Company San Diego

CDC. It takes only a few minutes to enable it and the impact in most cases should be minimal (of course you should make sure you test it). Triggers introduce lots of complexity and frequently harm performance and make support and development more difficult.

but, how we can get data if we use CDC in the asp.net

Craig
Database Development Company San Diego

Triggers are not only appropriate for this type of business logic, in my opinion they are the best solution and I have been implementing archive tables using them for years.

Here is a link to a script you can run which will itself generate a script for adding archive tables and supporting triggers to an existing database:
http://dl.dropbox.com/u/2507186/Scripts/AutoArchive.sql

I have not played around yet with 2008's auditing features, but my initial impression is that it would not give me the fine-tune control that I get by implementing my own scripts.

Same way you access any other data. With a query. Usually in a stored procedure. You access CDC data with a SELECT statement.

You would use triggers rather than CDC? Any particular reason other than familiarity? One of the disadvantages of a trigger for audit is that the audit all happens in the transaction, which means your server works twice as hard. CDC reads the log and it does so asynchronously so the overhead is almost negligible.

If you only have Standard Edition then you don't have CDC so that's a reason to use triggers (or to upgrade).

i think cdc is cool. will i jump in on version 1.0? probably not.

I do have a question about this. am i wrong in remembering triggers use the row versioning thingie in the tempdb in 2008? does that ultimately read from the logs? same with CDC? I forget.

Again I think CDC is cool because of it's ease of use. i have not done any performance bench marking yet, but I strongly suspect a read is a read and a write is a write and unless CDC uses fewer reads, I am doubtful how much the perf will improve. cpu improvement maybe.

the other thing that might bother me about CDC is tieing your auditing to a specific platform.

hello

Thank you for the information.

Can you give some useful guiled link where i can find information about the steps how to get CDC data?

Also if you have any statistic about the performance then it will be great.

One of my application required this feature but he is worring about the performance and scalability.

Thanks
Craig

Database Development Company San Diego

Familiarity and control, but I will definitely look into CDC.

Can I have a hit too?

Easy as pie

How do I track data changes in a database

I won't...just as I won't EVER employee CASCADE

And it's only in Enterprise Edition?

Know the cost difference?

You're such a Luddite, Brett.

dude, why do people hate cascade delete?

when you use it properly, it is physically more efficient and saves from having to write all those DELETE statements in the right order.

of course you should never use it where it does not make sense.

If ALL of these BS features were as good as a loom, I wouldn't have a problem...and, well, they mostly smoke and mirrors

Luddite - Wikipedia, the free encyclopedia

Why don't people like Cascade?

Because front end developers don't know what they are doing, and neither do the users

OOOOOPPPPSSS what HAPPEND to my data

Better have History tables to get the sheet back

OK, NOW you need to back THAT tidbit up with some facts