[MSSQL] 프로시저 이력 관리 DB 생성 및 트리거(Trigger) 셋팅

프로젝트 진행을 하거나 유지보수 중 다수의 개발자들이 있을 경우

개발자들이 프로시저를 임의로 몰래 수정하여 문제가 생기는 경우가 있다.

 

 

"누가 프로시저 수정한 거 같은데 확인할 수 있나요?"

 

프로시저 히스토리를 남기기 위해 위에서 언급한 트리거를 통해 작업을 하려 한다

 

트리거를 사용하는 것은 시스템에 오버헤드를 유발하기도 하지만

상황에 따라 필요한 정보를 남겨야 하는 경우 매우 유용하므로 적용해보기로..!!

---

•  트리거를 사용하여 프로시저에 변화가 감지되었을 때 특정 테이블에 저장하는 쿼리

-- 프로시저 트리거 생성
CREATE TRIGGER [ProcedureHis]
    ON ALL SERVER 
    -- 프로시저 생성, 수정, 삭제
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EventData XML
SET @EventData = EVENTDATA();
 
    --프로시저 이력 테이블을 따로 만들어 이력 확인 가능
    INSERT ProcedureHis
(
 EventType
, EventDDL
, EventXML
, DatabaseName
, SchemaName
, ObjectName
, HostName
, LoginName
)
    SELECT @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)')
, @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)')
, @EventData
, @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]',  'NVARCHAR(255)')
, @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)')
, @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)')
, HOST_NAME()
, SUSER_SNAME();
END

 

결과

EventType	EventDDL	EventXML	DatabaseName	SchemaName	ObjectName	HostName	LoginName
SP 생성, 수정, 삭제	SP DDL 문	XML 형식	데이터베이스이름	스키마	SP 이름	사용자 이름	SSMS 로그인한 계정

 

 

참고 : 트리거란?

 

[MSSQL] 트리거(Trigger) 란?