Procedure History 남기는 방법

SQL Server에서 프로시저를 생성하거나 수정할 때 발생하는 이벤트를 감지하고 해당 정보를 기록하는 트리거를 생성하는 방법은 다음과 같습니다. SQL Server에서는 DDL 이벤트를 감지하는 DDL 트리거를 사용하여 이러한 작업을 수행할 수 있습니다.

먼저, 프로시저 변경 내역을 기록할 테이블을 생성해야 합니다. 그런 다음, DDL 트리거를 생성하여 프로시저의 생성 또는 변경 이벤트를 감지하고 해당 정보를 기록할 수 있습니다.

1. History 테이블 생성

CREATE TABLE ProcedureHistory (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    EventDate DATETIME DEFAULT GETDATE(),
    EventType NVARCHAR(50),
    SchemaName NVARCHAR(128),
    ProcedureName NVARCHAR(128),
    TSQLCommand NVARCHAR(MAX),
    ExecutedBy NVARCHAR(128)
);

이 테이블은 각 이벤트의 ID, 이벤트가 발생한 날짜, 이벤트 유형, 스키마 이름, 프로시저 이름, T-SQL 명령어 및 실행자를 기록합니다.

2. DDL 트리거 생성

DDL 트리거는 데이터 정의 언어(DDL) 이벤트를 감지하여 트리거가 설정된 이벤트가 발생할 때 지정된 작업을 수행합니다. 프로시저의 생성 또는 수정 이벤트를 기록하는 트리거를 생성하는 방법은 다음과 같습니다.

CREATE TRIGGER trg_ProcedureChangeHistory
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML;
    SET @EventData = EVENTDATA();

    DECLARE @EventType NVARCHAR(50);
    DECLARE @SchemaName NVARCHAR(128);
    DECLARE @ProcedureName NVARCHAR(128);
    DECLARE @TSQLCommand NVARCHAR(MAX);
    DECLARE @ExecutedBy NVARCHAR(128);

    SET @EventType = @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(50)');
    SET @SchemaName = @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)');
    SET @ProcedureName = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)');
    SET @TSQLCommand = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)');
    SET @ExecutedBy = SUSER_SNAME();

    INSERT INTO ProcedureHistory (EventDate, EventType, SchemaName, ProcedureName, TSQLCommand, ExecutedBy)
    VALUES (GETDATE(), @EventType, @SchemaName, @ProcedureName, @TSQLCommand, @ExecutedBy);
END;

설명

• EVENTDATA(): 트리거가 실행될 때 이벤트에 대한 XML 데이터를 반환합니다.
• @EventData.value: XML 데이터를 분석하여 필요한 값을 추출합니다.
• SUSER_SNAME(): 현재 로그인한 사용자의 이름을 반환합니다.
• FOR CREATE_PROCEDURE, ALTER_PROCEDURE: 프로시저가 생성되거나 수정될 때 트리거가 실행되도록 설정합니다.

반응형

3. 트리거 테스트

이제 트리거를 테스트해 볼 수 있습니다. 새로운 프로시저를 생성하거나 기존 프로시저를 수정하여 트리거가 작동하는지 확인합니다.

-- 새 프로시저 생성
CREATE PROCEDURE TestProcedure
AS
BEGIN
    SELECT 'Hello, World!';
END;

-- 기존 프로시저 수정
ALTER PROCEDURE TestProcedure
AS
BEGIN
    SELECT 'Hello, SQL Server!';
END;

-- 히스토리 테이블 확인
SELECT * FROM ProcedureHistory;

이 테스트를 통해 ProcedureHistory 테이블에 생성 및 수정된 프로시저의 정보가 기록되는지 확인할 수 있습니다.

이렇게 하면 SQL Server에서 프로시저가 생성되거나 수정될 때 해당 정보를 자동으로 기록하는 시스템을 구현할 수 있습니다.