답변.

변경 로그 테이블(log/audit tables)은 프로덕션 테이블에서 데이터 변경 이벤트를 저장하는 일반 테이블입니다. 누가, 언제, 무엇을 변경했는지를 기록합니다. 이는 금융 부문, 전자 상거래, 정부 기관에 해당합니다.

구현 접근 방식:

애플리케이션이나 프로시저를 통한 명시적 로그 기록.
모든 변화를 추적하기 위한 트리거 사용.
새로운 DBMS에서는 Change Data Capture (CDC) 또는 Temporal Tables와 같은 시스템 메커니즘 사용.

일반적인 구성:

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

코드 예제 (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

함정:

대량의 감사 테이블은 조회 성능을 저하시킬 수 있습니다.
적절한 파티셔닝/오래된 데이터 삭제가 필요합니다.
잠금 및 변경 사항의 중복 제거를 주의해야 합니다.

함정 질문.

Q: 기본 트랜잭션이 롤백될 때 로그 테이블에서 추가된 레코드도 자동으로 롤백됩니까?

A: 트리거가 트랜잭션의 일부라면, 네, 로그에의 삽입은 기본 작업과 함께 롤백됩니다. 그러나 애플리케이션 수준/별도의 트랜잭션에서 로깅할 경우 일관성이 없을 수 있습니다.

주제의 미세한 차이로 인한 실제 오류 예시.

이야기

한 신용 기관에서 감사 테이블 정리 메커니즘이 구현되지 않았습니다. 데이터 양이 수억 행을 초과하여 백업 시간이 길어지고, 공간 부족 및 전체 DB 성능 저하로 이어졌습니다.

이야기

개발자가 트랜잭션 밖의 별도 연결을 통해 감사 작업을 수행했습니다. 결과적으로 존재하지 않는 이벤트에 대한 레코드가 생성되었습니다 (응용 논리의 트랜잭션이 롤백되었으나 로그의 레코드는 남아 있었습니다).

이야기

변화 로그를 설계할 때 모든 필드에 NVARCHAR(MAX) 타입을 적용하여 유연성을 도모했습니다. 그러나 이는 필요하지 않았고, 저장 및 인덱싱에 과부하를 주어 데이터를 효율적으로 분석할 수 없었습니다.