All geek questions in one place

How to get all changes from a SQL Server database using change tracking in a single query (or avoid chat with the database)?

I know that you can get changes to a single table in SQL Server 2008 using change tracking. However, I could not find an acceptable way to request all the changes in the entire database in one request.

Is it possible?

What are your thoughts on writing dynamic SQL and using exec for this? Is there any example of this that you can point me to?

Are there any other alternatives for monitoring certain column / row data changes in SQL Server 2008 that require less effort than writing and maintaining triggers? Our database has more than 1,500 tables, and this is not an option.

thanks

+4
source share
1 answer

When I started using CDC, this URL helped me get started: http://www.mssqltips.com/sqlservertip/1819/using-change-tracking-in-sql-server-2008/

The disadvantage of CDC is that you cannot see who changed the data, the data just changed and what the previous data was. CDC is available only in Enterprise, Developer, and Evaltion editions.

In cases where I have to check certain tables to modify the data, you can also use the audit functions available since SQL Server 2008. Auditing can be enabled for the database, and you can see which command was executed by whom. The disadvantage is that you can only see the executed command, and not what data was present before the command was executed.

This website will help you decide which functionality is best for your situation. It describes the various methods and their pros and cons: http://solutioncenter.apexsql.com/tag/methods-for-auditing-sql-server/

I always avoid using triggers because they slow down processes for inserting, updating, and deleting records.

Hope this helps you a little

+2
source

Source: https://habr.com/ru/post/1398622/

More articles:


All Articles