Tracking data changes is a cornerstone of Record Keeping and Data Integrity. In the highly regulated world of banking and financial institutions, the ability to accurately track and reconstruct changes to data is not merely a best practice; it is a fundamental regulatory requirement.

Financial institutions handle customer information and transactional data where even a minor, undocumented alteration can have significant fiscal and legal implications. This article explores data change tracking and examines practical implementation in Oracle and SQL Server databases.

What is Data Change Tracking?

When it comes to auditing, recordkeeping, and data integrity, there are three measures customers find confusing:

• Query Auditing aims to record all access requests to view data. This type of auditing aims to combat data theft by inspecting and/or controlling who accesses what. That is something we can help you with but is not in the scope of this article.
• Data Modification Auditing aims to prevent unauthorized changes by inspecting and/or controlling who changes data. That includes inserting rows, deleting data, or modifying records. That is also something we can help you with but is not in the scope of this article.
• Data Change Tracking aims to track the lineage of each piece of data, allowing you to undo changes. That is the subject of this article, and let us explain it a little more.

So, how does data change tracking differ from modification auditing?

Let us assume we have a balance column, and someone issues a SQL that changes it. For example:

balance = balance + 1000

In auditing, we record the SQL, who executed it, and when. In data change tracking, we record the previous and new values of the balance (for example, 10,000 and 11,000). Those values are not in the SQL, as the SQL only asked to add 1,000 to the previous value.

If the above SQL modified 10 rows, auditing will record the SQL and the fact that it modified 10 rows. However, auditing will not list which rows changed. Data change tracking will record each change, listing the 10 previous and 10 new values.

Similarly, if someone deleted several rows, auditing will record the SQL and the number of rows deleted. Change tracking will record the values in each deleted row.

Data change tracking is a strong requirement common in highly regulated environments such as banks and financial institutions. It means we will know the values before and after every change. That is critical in detailed recordkeeping.

The Regulations

Tracking data changes is the cornerstone of good recordkeeping. It means we know the lineage of each piece of data and can revert if someone makes an unauthorized change. It is, therefore, required by all compliance regulations for banks and other financial institutions.

In the United States, FINRA (Financial Industry Regulatory Authority) and FFIEC (Federal Financial Institutions Examination Council) publish guidance for banks and financial institutions.

For example, FINRA Rule 4511 and its underlying SEC Rule 17a-4 deal with electronic recordkeeping and require that the audit trail includes “all modifications to and deletions of the record or any part thereof” (17a-4(f)(2)(i)(A)(1)).

Similar guidance exist from the ECB (European Central Bank), BaFin (Bundesanstalt für Finanzdienstleistungsaufsicht) in Germany, ACPR (Autorité de contrôle prudentiel et de résolution) in France, and CNMV (Comisión Nacional del Mercado de Valores) in Spain.

In Latin America, regulators also have strict requirements from CNBV (Comisión Nacional Bancaria y de Valores) in Mexico, SFC (Superintendencia Financiera de Colombia) in Colombia, SBS (Superintendencia de Banca, Seguros y Administradoras Privadas de Fondos de Pensiones) in Peru, and many more.

BCBS 239 (Basel Committee on Banking Supervision – Principles for Effective Risk Data Aggregation and Risk Reporting) is a global banking guidance that influences most regulators worldwide. Most interpretations tend to agree that it also requires this type of detailed recordkeeping.

Implementations

Tracking changes in the database is possible through different mechanisms. While each database is slightly different and reviewed below, the general principles and pros and cons are similar.

Triggers are pieces of code that execute when data changes. One of the simplest methods for tracking changes in a database is with a trigger that records the changes in an audit table. That is something any DBA can implement on any database.

Triggers, however, suffer from a major flaw: a high impact on performance. The additional recording into the audit table is part of the transaction and will significantly slow it down. Typical impact is at least 100%, making everything take twice as long.

Light Triggers are a variation of the classic trigger implementation. Instead of an insert, they execute a dummy SQL that includes the data that changed. Using a dummy SQL significantly reduces the performance overhead. Core Audit Full Capture can see these internal SQLs in triggers, extract the data, and record it on the Core Audit Server.

One of the benefits of light triggers is that you can correlate data changes to the rest of the audit data, including the exact session that made them.

Light triggers will work on any database as long as you have the Core Audit Full Capture technology to harvest the data. For example, information from SQL Server light triggers stored in Core Audit may look like this:

Time	2025/05/16 15:38:19
Session	714000000000002308 (link to a Core Audit session)
Username	sa
Session info	OSQL-32 local
Owner	test
Table	emp
Operation	UPDATE
New Values	id=1 first=’John’ last=’Doe’ salary=11000
Old Values	id=1 first=’James’ last=’Doe’ salary=10000

Redo Logs or Transaction Logs, are different names for the same thing. These are logs that exist in all databases and record every change to the data. They are a critical part of the database, enabling crash recovery and point-in-time recovery, ensuring data integrity.

Since these logs always exist and record every change, you can mine them to extract the changes you need to track. The primary benefit of mining these logs is that it is not part of the SQL and does not slow down database transactions.

The downside is that mining these logs increases resource consumption on the server and, more importantly, is limited to the information stored in the logs. For example, some databases record the session information while others only record the change itself without any reference to who made it. Below is specific information for each database type, including drawbacks that are database-dependent.

Oracle Redo Logs

You can control the information in the Oracle redo logs through the Oracle Supplemental Logging configuration. If you aim to mine changes from the logs, you should, at the very least, execute this SQL:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Without it, ‘update’ sometimes shows as a ‘delete’ and ‘insert’. You can enable additional logging to record more information about primary keys, other columns, and more.

Oracle Logminer lets you query the online and archived redo logs to extract information from the Oracle redo logs. 

Core Audit can use Logminer to query the redo logs and store the information on the Core Audit Server. For example, Logminer information stored in Core Audit may look like this:

Time	2025/05/15 12:22:55
Session	68
Serial#	3787
Username	HRAPP
Session info	login_username=HRAPP client_info= OS_username=bluecore Machine_name=WORKGROUP\BLUECORE2 OS_terminal=BLUECORE2 OS_process_id=5976:5220 OS_program_name=sqlplus.exe
Owner	HRAPP
Table	SALARIES
Operation	UPDATE
Redo SQL	update “HRAPP”.”SALARIES” set “SALARY” = ‘11000’ where “SALARY” = ‘10000’ and ROWID = ‘AABnEEAAEAAAAJUAAE’;
Undo SQL	update “HRAPP”.”SALARIES” set “SALARY” = ‘10000’ where “SALARY” = ‘11000’ and ROWID = ‘AABnEEAAEAAAAJUAAE’;
New Values	Salary=11000
Old Values	Salary=10000
Field

SQL Server Transaction Logs

SQL Server transaction logs do not contain session information, so it is impossible to link changes to the users that performed them.

Core Audit can leverage the SQL Server replication engine to extract information from the transaction logs. This is a high-performance near real-time extraction, but it takes over the replication engine so you cannot use SQL Server replication in conjunction with the Core Audit transaction log extraction.

For example, information from SQL Server transaction logs stored in Core Audit may look like this:

Time	2025/05/16 13:42:33
Owner	test
Table	emp
Operation	update
Redo SQL	update [dbo].[emp] set [salary] = 11000 where [id] = 1

SQL Server also has built-in mechanisms to extract information from the transaction logs. They are also limited to the same information available in the logs. Change Tracking is the old mechanism, and Change Data Capture (CDC) is the newer one. CDC is better but, still, requires some administration and regular maintenance, and the output is not trivial to understand.

Summary

Tracking data changes is vital for good recordkeeping and essential in banking and financial systems.

This requirement has multiple implementation options, and Core Audit supports all of them. While the best option may depend on your environment, many customers find the limited downsides of light triggers and the high quality of the information compelling.