MSSQL 트리거 실행하게 한 쿼리 조회하는 방법 문의드립니다.

Question

안녕하세요.MSSQL로 쿼리 실행시 트리거가 실행되는데 트리거를 실행하게 한 쿼리 이력을 남기려고 합니다. 그래서 트리거 내부에 쿼리 이력을 남기게끔 하였는데 이것저것 아무리 해봐도 CREATE TRIGGER 문만 가져옵니다. 예를 들어 update testTable set rmk &#61; 'TEST' where rmk is null 이 쿼리를 실행하였을 때 쿼리 이력에 update testTable set rmk &#61; 'TEST' where rmk is null 쿼리문을 가져와야 되는데 CREATE TRIGGER trigger_test................... 을 가져옵니다. 아래 쿼리를 실행했을 경우에도 update 문은 찾아볼 수가 없습니다. 조언 부탁드립니다.... 감사합니다.select db_name(st.dbid) DBNanme, object_schema_name(objectid, st.dbid) SchemaName, object_name(objectid, st.dbid) SPName, qs.total_elapsed_time, creation_time, last_execution_time, textfrom sys.dm_exec_query_stats qscross apply sys.dm_exec_sql_text(qs.plan_handle) stjoin sys.dm_exec_cached_plans cpon qs.plan_handle &#61; cp.plan_handlewhere CONVERT(VARCHAR(10), last_execution_time, 121) &#61; CONVERT(varchar(10), getdate(), 121) -- 조건 : 수행일자 (1일전) AND db_name(st.dbid) &#61; 'TESTDB'order by last_execution_time desc;

더미 · Answer

네, 말씀하신 문제는 SQL Server 트리거 내에서 실행된 쿼리를 직접 얻어오기 어렵기 때문에 발생합니다. 트리거는 특정 이벤트(INSERT, UPDATE, DELETE) 발생 시 자동으로 실행되는 특수한 저장 프로시저로, 실행된 쿼리 정보를 직접 접근할 수 있는 시스템 함수나 변수가 제공되지 않습니다.하지만, 몇 가지 방법을 통해 트리거를 실행한 쿼리 이력을 간접적으로 확인할 수 있습니다.1. SQL Server Profiler 또는 Extended Events 활용:SQL Server Profiler (SQL Server 2016 이하 버전): 특정 이벤트(예: 트리거 실행)를 추적하고 실행된 SQL 문을 포함한 상세 정보를 기록할 수 있습니다.Extended Events (SQL Server 2012 이상 버전): SQL Server Profiler의 후속 기술로, 더 가볍고 유연하게 이벤트를 추적할 수 있습니다. 트리거 실행 이벤트를 추적하고 관련 정보를 수집할 수 있습니다.2. 트리거 내에서 CONTEXT_INFO() 함수 활용:CONTEXT_INFO() 함수: 현재 세션의 컨텍스트 정보를 설정하고 가져올 수 있는 함수입니다.구현 방식:트리거 실행 전에 애플리케이션에서 CONTEXT_INFO() 함수를 사용하여 실행된 쿼리 정보를 설정합니다.트리거 내부에서 CONTEXT_INFO() 함수를 사용하여 설정된 쿼리 정보를 가져와 이력 테이블에 저장합니다.예시:SQL-- 애플리케이션에서 쿼리 실행 전 DECLARE @query NVARCHAR(MAX) = N'UPDATE Employees SET salary = salary * 1.1 WHERE department_id = 10'; SET CONTEXT_INFO @query; -- 트리거 내부 INSERT INTO QueryHistory (query_text, execution_time) VALUES (CAST(CONTEXT_INFO() AS NVARCHAR(MAX)), GETDATE()); 코드를 사용할 때는 주의가 필요합니다.content_copy3. 로그 테이블 생성 및 트리거 수정:로그 테이블 생성: 쿼리 이력을 저장할 테이블을 생성합니다.트리거 수정: 트리거 내부에서 INSERT 문을 사용하여 실행된 쿼리 정보를 로그 테이블에 저장합니다.주의 사항: 트리거 내에서 로그 테이블에 데이터를 삽입하는 것은 성능에 영향을 줄 수 있으므로, 대량의 데이터 변경 작업에는 적합하지 않을 수 있습니다.예시:SQLCREATE TABLE QueryHistory ( id INT IDENTITY(1,1) PRIMARY KEY, query_text NVARCHAR(MAX), execution_time DATETIME ); CREATE TRIGGER trg_Employees_Update ON Employees AFTER UPDATE AS BEGIN INSERT INTO QueryHistory (query_text, execution_time) SELECT 'UPDATE Employees...', GETDATE(); END; 코드를 사용할 때는 주의가 필요합니다.content_copy결론:SQL Server 트리거에서 직접 실행된 쿼리를 얻어오는 것은 불가능하지만, 위에서 제시된 방법들을 활용하여 간접적으로 쿼리 이력을 확인할 수 있습니다. 각 방법의 장단점을 고려하여 상황에 맞는 방법을 선택하시기 바랍니다.

winnever · Answer

트리거 내부에 아래 내용 적용해서 해결했습니다. INSERT #INPUTBUFFER EXEC ('DBCC INPUTBUFFER( ' + @@SPID + ')') SELECT @sqlText = LTRIM(RTRIM(CONVERT(NVARCHAR(MAX),TSQLCommand))) FROM #INPUTBUFFER

MSSQL 트리거 실행하게 한 쿼리 조회하는 방법 문의드립니다.

2개의 답변

Q&A 목록