MSSQL 사용자 감사 트리거로 데이터 변경 내역 완벽 기록하기

🔗 감사 트리거란 무엇인가?

감사 트리거(Audit Trigger)는 데이터베이스에서 발생하는 특정 작업을 자동으로 감지하고, 그 내역을 별도의 테이블에 기록하는 기능입니다.
MSSQL에서는 AFTER INSERT, AFTER UPDATE, AFTER DELETE와 같은 트리거를 사용해 데이터 변경 이력을 추적할 수 있습니다.
즉, 사용자가 데이터를 추가, 수정, 삭제하는 순간 해당 트리거가 실행되어 필요한 정보를 로그 테이블에 저장하는 방식입니다.

이러한 감사 트리거는 단순한 변경 기록뿐만 아니라, 보안 및 감사 목적으로도 널리 사용됩니다.
예를 들어, 누군가 중요한 고객 정보를 삭제했을 경우, 트리거를 통해 삭제 시각, 사용자 ID, 변경된 데이터 내용 등을 정확히 확인할 수 있습니다.
이는 데이터 무결성을 유지하고, 법적 규제나 내부 감사 요구사항을 충족하는 데 중요한 역할을 합니다.

📌 감사 트리거의 주요 기능

• 🛠️데이터 변경 시점(INSERT, UPDATE, DELETE) 자동 감지
• ⚙️변경된 데이터의 이전 값과 새로운 값 저장
• 🔒변경을 수행한 사용자 및 변경 시각 기록
• 📜감사 및 규제 준수를 위한 로그 데이터 유지

📌 감사 트리거의 필요성

감사 트리거는 단순히 로그를 남기는 것을 넘어, 시스템의 투명성과 신뢰성을 높이는 핵심 요소입니다.
특히 금융, 의료, 공공기관과 같이 민감한 데이터를 다루는 분야에서는 법적으로 데이터 변경 이력을 보관해야 할 의무가 있습니다.
이때 트리거를 활용하면 사용자의 실수나 악의적 행위로 인한 데이터 손실을 방지하고, 문제가 발생했을 때 원인을 신속하게 파악할 수 있습니다.

🛠️ AFTER 트리거의 동작 원리

MSSQL에서 AFTER 트리거는 특정 작업(INSERT, UPDATE, DELETE)이 완료된 직후에 실행되는 특수한 프로시저입니다.
이 말은 곧, 데이터 변경이 데이터베이스에 반영된 뒤에야 트리거 로직이 동작한다는 뜻입니다.
따라서 AFTER 트리거는 변경된 데이터를 확실히 확인하고, 이를 바탕으로 로그를 기록하거나 후속 작업을 수행할 수 있습니다.

트리거가 동작할 때, MSSQL은 자동으로 INSERTED와 DELETED라는 두 개의 가상 테이블을 제공합니다.
이 두 테이블에는 변경 전·후 데이터가 담겨 있어, 이를 활용해 어떤 값이 어떻게 변경되었는지 추적할 수 있습니다.
예를 들어 UPDATE 작업 시 DELETED에는 기존 데이터가, INSERTED에는 변경된 데이터가 저장됩니다.

📌 AFTER 트리거 동작 흐름

1. 사용자가 INSERT, UPDATE 또는 DELETE 명령 실행
2. 명령이 성공적으로 완료되어 데이터가 변경됨
3. MSSQL이 INSERTED / DELETED 가상 테이블 생성
4. AFTER 트리거가 실행되며 변경 내역을 가상 테이블에서 조회
5. 변경 정보가 로그 테이블 또는 다른 처리 로직으로 전달됨

📌 예시 코드

CREATE TRIGGER trg_Audit_AfterUpdate
ON dbo.Customer
AFTER UPDATE
AS
BEGIN
    INSERT INTO dbo.CustomerLog (CustomerID, OldName, NewName, ChangedBy, ChangeDate)
    SELECT 
        d.CustomerID, 
        d.Name, 
        i.Name, 
        SYSTEM_USER, 
        GETDATE()
    FROM deleted d
    INNER JOIN inserted i ON d.CustomerID = i.CustomerID;
END

⚙️ 로그 테이블 설계 방법

감사 트리거를 통해 기록된 데이터가 보관되는 곳이 바로 로그 테이블입니다.
로그 테이블은 단순한 백업 저장소가 아니라, 변경 내역을 분석하고 문제를 추적하는 핵심 자료가 되므로 구조 설계가 매우 중요합니다.
잘 설계된 로그 테이블은 데이터베이스 성능에 최소한의 부담을 주면서도, 필요한 정보를 빠르게 검색할 수 있게 합니다.

로그 테이블 설계 시에는 다음과 같은 요소들을 고려해야 합니다.
우선 어떤 항목을 기록할 것인지 명확히 정의해야 하며, 일반적으로 변경된 행의 키 값, 변경 전 데이터, 변경 후 데이터, 변경 사용자, 변경 시각 등을 포함합니다.

📌 로그 테이블 필수 컬럼

📌 로그 테이블 설계 시 주의사항

또한, 로그 테이블은 읽기 작업이 많을 수 있으므로 적절한 인덱스를 설정하는 것이 중요합니다.
특히 ChangeDate, ActionType, PrimaryKeyValue 컬럼에 인덱스를 걸면 검색 속도가 향상됩니다.
단, 인덱스가 너무 많으면 쓰기 성능이 저하될 수 있으므로 균형 있게 설계해야 합니다.

🔌 INSERT, UPDATE, DELETE 트리거 작성 예제

감사 트리거를 실무에 적용하려면, 각 작업 유형별로 적절한 AFTER 트리거를 작성해야 합니다.
MSSQL에서는 INSERT, UPDATE, DELETE 각각에 대해 별도의 트리거를 만들 수도 있고, 하나의 트리거 안에서 모든 작업을 처리하도록 구성할 수도 있습니다.
아래 예제는 고객(Customer) 테이블의 데이터 변경 내역을 CustomerLog라는 로그 테이블에 저장하는 코드입니다.

📌 통합 감사 트리거 예제

CREATE TRIGGER trg_Audit_Customer
ON dbo.Customer
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- INSERT 작업 기록
    INSERT INTO dbo.CustomerLog (ActionType, PrimaryKeyValue, OldValue, NewValue, ChangedBy, ChangeDate)
    SELECT 
        'INSERT',
        i.CustomerID,
        NULL,
        i.Name,
        SYSTEM_USER,
        GETDATE()
    FROM inserted i
    LEFT JOIN deleted d ON i.CustomerID = d.CustomerID
    WHERE d.CustomerID IS NULL;

    -- UPDATE 작업 기록
    INSERT INTO dbo.CustomerLog (ActionType, PrimaryKeyValue, OldValue, NewValue, ChangedBy, ChangeDate)
    SELECT 
        'UPDATE',
        i.CustomerID,
        d.Name,
        i.Name,
        SYSTEM_USER,
        GETDATE()
    FROM inserted i
    INNER JOIN deleted d ON i.CustomerID = d.CustomerID;

    -- DELETE 작업 기록
    INSERT INTO dbo.CustomerLog (ActionType, PrimaryKeyValue, OldValue, NewValue, ChangedBy, ChangeDate)
    SELECT 
        'DELETE',
        d.CustomerID,
        d.Name,
        NULL,
        SYSTEM_USER,
        GETDATE()
    FROM deleted d
    LEFT JOIN inserted i ON i.CustomerID = d.CustomerID
    WHERE i.CustomerID IS NULL;
END

📌 테스트 방법

• 🛠️Customer 테이블에 새 레코드 INSERT
• ⚙️기존 고객 이름 UPDATE
• 🗑️레코드 DELETE 후 로그 테이블에 기록 여부 확인

💡 로그 관리와 성능 최적화 팁

감사 트리거를 통해 쌓이는 로그 데이터는 시간이 지날수록 방대해집니다.
관리 방안을 마련하지 않으면 데이터베이스 성능이 저하되고, 스토리지 사용량이 급격히 늘어날 수 있습니다.
따라서 주기적인 관리와 최적화 전략이 필수적입니다.

📌 로그 데이터 관리 방법

• 🗂️보관 주기를 설정하고 일정 기간이 지난 로그는 별도 아카이브 테이블로 이전
• 🧹불필요한 테스트 로그 및 중복 데이터 정리
• 📦대량 데이터는 분할 테이블(partitioned table)을 활용해 관리
• 🔒로그 테이블 접근 권한 제한으로 보안 강화

📌 성능 최적화 팁

감사 트리거는 모든 데이터 변경 시 실행되므로, 잘못 설계하면 성능에 부담이 될 수 있습니다.
다음은 성능 저하를 방지하는 팁입니다.

1. 트리거에서 불필요한 SELECT, JOIN 연산 최소화
2. 대량 UPDATE/DELETE 시 트리거 일시 비활성화 고려
3. 로그 테이블에 필요한 최소한의 컬럼만 저장
4. 자주 조회하는 컬럼에 적절한 인덱스 추가

📌 유지보수 전략

정기적으로 로그 테이블 상태를 점검하고, 필요 시 파티션을 재구성하거나 인덱스를 재구성하는 것이 좋습니다.
또한, 로그 데이터를 기반으로 리포트를 생성하면 단순 감사 기능을 넘어 데이터 활용 가치가 높아집니다.

❓ 자주 묻는 질문 (FAQ)