MSSQL Trigger 조회 및 내용 검색

MSSQL에는 생성된 Trigger 이름 및 내용을 database에 저장하고 있으므로 Query 또는 명령어로 생성된 Trigger에 대한 모든 정보를 조회할 수 있습니다.

 

Trigger(트리거) 조회, 내용검색 설명.

• 생성한 Trigger의 내용을 확인할 수 있도록 MSSQL은 Trigger 조회 기능을 지원합니다.
• SQL Server Management Studio (SSMS)를 이용해서도 내용 확인이 가능합니다.
• Trigger 명을 알고 있다면 sp_helptext 명령어로 내용검색이 가능합니다. 가장 자주 사용하는 명령어입니다.
• Trigger의 object_id 알고 있다면 OBJECT_DEFINITION을 이용하여 문자열 내용을 검색할 수 있습니다.
• 시스템 뷰에서 직접 검색할 수도 있는데 보통은 syscomments의 TEXT COLUMN을 이용하면 가능합니다.
• Trigger는 시스템 뷰에서 지원하는 sys.sql_modules는 SQL 언어로 정의된 모든 module을 제공하므로 Trigger 내용도 검색할 수 있습니다.

Trigger(트리거) 조회, 내용검색 그림 설명입니다. 

Trigger 조회,내용검색 그림 설명입니다.

 

Trigger(트리거) 조회, 내용검색 방법

sp_helptext 명령어 사용법.

• sp_helptext는 가장 많이 사용하는 명령어 업니다.
• 한 개의 Trigger 내용검색을 위해서 sp_helptext 명령어를 사용합니다.
• 소스 코드입니다.

 -- Create Sample Table 
DROP TABLE IF EXISTS AurumGuide_Text_Search; 
CREATE TABLE AurumGuide_Text_Search (
    AurumId           INT NOT NULL,
    AurumNm           VARCHAR(255) NOT NULL,
    AurumAge          INT  NULL,
    AurumAddress      VARCHAR(500)  NULL
);
go
DROP TABLE IF EXISTS AurumGuide_Text_Search_Log; 
CREATE TABLE AurumGuide_Text_Search_Log (
    AurumId           INT NOT NULL,
	AurumNm           VARCHAR(255)  NULL,
    AurumEvent        VARCHAR(255)  NULL,
    AurumDateTime          datetime  NULL     
);
go  
CREATE OR ALTER TRIGGER dbo.TR_AurumGuide_Text_Search
ON dbo.AurumGuide_Text_Search
AFTER INSERT
AS
BEGIN
INSERT INTO dbo.AurumGuide_Text_Search_Log
		(AurumId,AurumNm, AurumEvent, AurumDateTime)
SELECT AurumId,AurumNm,'INSERT', GETDATE() FROM inserted;
END;

--  1. sp_helptext 명령어.
EXEC sp_helptext 'TR_AurumGuide_Text_Search';

syscomments의 TEXT 칼럼 사용.

• syscomments는 시스템 View이며, text 칼럼을 통해서 Trigger 내용을 검색할 수 있습니다.
• sys.objects의 column의 'type'의 조건을 'TR'만 검색할 수 있습니다. 
• 소스 코드입니다.

-- 2. syscomments의  TEXT 컬럼으로 Trigger 문자열 검색.
SELECT type, name, text
 FROM syscomments S1
 JOIN sys.objects S2 
   ON S1.id = S2.object_id
WHERE S2.type IN ('TR')
 AND S1.text LIKE '%TR_AurumGuide_Text_Search%';

 

sys.objects을 이용해서 Trigger 내용검색.

• sys.objects는 Trigger, 프로시저, 함수 거의 모든 내용검색이 가능합니다.
• 내용을 검색할 때는 OBJECT_DEFINITION() 함수를 사용합니다.
• 소스 코드입니다.

-- 3. sys.objects을 이용해서 함수 내용검색
SELECT OBJECT_NAME(object_id)  FUNC_NAME
      ,OBJECT_DEFINITION(object_id) FUNC_TEXT
 FROM sys.objects
 WHERE  OBJECT_NAME(object_id) = 'TR_AurumGuide_Text_Search'

 

sys.sql_modules을 사용하여 Trigger 내용검색.

• sys.sql_modules의 definition column에 Trigger 내용을 저장하고 있습니다.
• Trigger, 프로시저, 함수 등 모든 내용검색을 할 때 주로 사용합니다.
• 소스 코드입니다.

-- 4. sys.sql_modules을 사용하여 프로시저 내용 검색.
SELECT OBJECT_NAME(OBJECT_ID) AS  Trigger_NM
      ,definition             AS  Trigger_SORCE
      ,*
FROM sys.sql_modules  
WHERE object_id = (OBJECT_ID(N'dbo.TR_AurumGuide_Text_Search'));