Answer.

Log/audit tables are regular tables that store data change events in production tables: who, when, and what was changed. This is relevant for the financial sector, e-commerce, and government agencies.

Implementation approaches:

1. Explicit logging in the audit via the application or procedure.
2. Using triggers to track any changes.
3. In new DBMS, system mechanisms like Change Data Capture (CDC) or Temporal Tables.

Typical structure:

• audit_id (PK), TableName, OperationType (I/U/D), RecordID, OldValue, NewValue, ChangedBy, ChangeDT

Code example (trigger for auditing UPDATE)

CREATE TABLE ProductAudit (
  AuditID int IDENTITY(1,1) PRIMARY KEY,
  ProductID int,
  OldPrice money,
  NewPrice money,
  ChangedBy sysname,
  ChangeDT datetime,
  OperationType char(1)
);
GO
CREATE TRIGGER trg_ProductAudit
ON Products
AFTER UPDATE
AS
BEGIN
  INSERT INTO ProductAudit(ProductID, OldPrice, NewPrice, ChangedBy, ChangeDT, OperationType)
    SELECT d.ProductID, d.Price, i.Price, SYSTEM_USER, GETDATE(), 'U'
    FROM inserted i JOIN deleted d ON i.ProductID = d.ProductID
    WHERE i.Price <> d.Price;
END

Pitfalls:

• Large volume of audit tables can slow down queries.
• Proper partitioning/deletion of old data is necessary.
• Must monitor locks and deduplication of changes.

Trick question.

Q: Is a record inserted into the log table automatically rolled back when the main transaction is rolled back?

A: If the trigger is part of the transaction, then yes, the insert into the log is rolled back along with the main operation. However, when logging at the application level/separate transaction, consistency may not be guaranteed.

Examples of real errors due to lack of knowledge of the nuances of the topic.

---

Story

In a lending institution, a cleaning mechanism for the audit table was not implemented. The volume exceeded hundreds of millions of rows, leading to long backups, lack of space, and degradation of the entire DB performance.

---

Story

A developer performed audits through a separate connection outside of the transaction. As a result, records of events that did not exist were created (the application logic transaction was rolled back, but the log entry was not).

---

Story

When designing the change log, all fields were given the NVARCHAR(MAX) type for universality. This was unnecessary and overloaded storage and indexing — data could not be efficiently analyzed.