MSSQL – 트리거 목록을 확인하고 사용하지 않는 트리거를 관리해보자

트리거는 특정 이벤트 발생 시 자동으로 정의된 동작을 수행하도록 해준다.
예를 들어, 테이블에 새로운 데이터가 추가될 때마다 자동으로 로그를 남기거나 다른 테이블을 업데이트할 수 있다.
그런데 문득 내가 어떤 테이블에 트리거를 걸어두었고, 데이터베이스 내에 트리거는 총 몇 개 인지 기억이 나지 않아 확인해야 할 때가 있다.
또한, 특수한 상황에서 데이터베이스를 마이그레이션 할 때, 해당 DB 서버에서는 트리거가 필요하지 않을 수도 있다.
이번 글에서는 MS-SQL 초보자도 쉽게 트리거 목록을 확인하고 관리하는 방법을 설명하려 한다.

트리거 목록 확인하기🔍

MSSQL에서 트리거 목록을 확인하는 방법은 여러 가지가 있다.

시스템 뷰 ‘sys.triggers’ 활용하기

sys.triggers 는 데이터베이스 내 트리거에 대한 정보를 제공하는 시스템 카탈로그 뷰이다.
이 시스템 뷰를 이용하면 모든 트리거의 정보를 확인할 수 있다.

SELECT * FROM sys.triggers

주요 컬럼만 빠르게 알아보자.

• name : 트리거 이름
• object_id : 트리거의 객체 ID
• parent_id : 트리거가 속한 테이블의 ID
• type_desc : 객체의 유형 설명 (DML 또는 DDL 트리거 등)

특정 테이블의 트리거 조회하기

특정 테이블의 트리거를 조회하고자 한다면,
parent_id 를 해당 테이블의 object_id 와 비교하면 된다.
OBJECT_ID() 함수는 테이블의 이름을 이용하여 해당 개체 ID 번호를 가져오는 기능을 한다.

SELECT name, object_id, parent_id, type_desc
FROM sys.triggers
WHERE parent_id = OBJECT_ID('테이블명');

트리거가 존재하는 테이블 이름과 함께 조회하기

트리거가 어디에 속하는지 테이블의 이름과 함께 조회하려면, sys.tables 시스템 뷰를 조인하면 된다.
sys.tables 는 데이터베이스 내에 있는 사용자 정의 테이블에 대한 정보를 제공하는 시스템 카탈로그 뷰이다.

SELECT 
    trg.name AS TriggerName,
    tbl.name AS TableName
FROM 
    sys.triggers trg
JOIN 
    sys.tables tbl ON trg.parent_id = tbl.object_id;

• TriggerName : 트리거 이름
• TableName : 트리거가 존재하는 테이블의 이름
• trg.parent_id = tbl.object_id : 트리거의 parent_id 는 해당 트리거가 연결된 테이블의 object_id 를 가리킨다.

트리거 목록과 트리거 내용(정의 코드) 확인하기

트리거의 내용을 확인하려면 sys.sql_modules 시스템 뷰를 조인하면 트리거의 SQL 스크립트를 볼 수 있다.
sys.sql_modules 는 저장된 프로시저, 트리거, 뷰, 함수 등의 SQL 정의 객체에 대한 정보를 제공하는 시스템 카탈로그 뷰다.
이 뷰를 통해 해당 객체들의 SQL 스크립트 또는 정의 내용을 조회할 수 있다.

SELECT
  t.name AS TriggerName,
  m.definition AS TriggerDefinition
FROM sys.triggers t
JOIN sys.sql_modules m ON t.object_id = m.object_id
-- 특정 트리거의 내용만 확인하려면 WHERE절에 트리거 이름 검색
-- WHERE t.name = '트리거명';

• TriggerName : 트리거 이름
• TriggerDifinition : 트리거 정의 (SQL 코드)

사용하지 않는 트리거 관리하기

MSSQL에서 트리거를 삭제하거나 비활성화(사용 안 함)하는 방법은 각각 다르다.
우선 트리거를 삭제하는 방법을 알아보자.

트리거 삭제하기

트리거를 완전히 삭제하려면 DROP TRIGGER 명령을 사용하면 된다.
이 명령어를 사용하면 트리거가 삭제되므로 주의하자.

DROP TRIGGER [스키마명.]트리거명;

/* 예시 */
DROP TRIGGER dbo.trg_test;

• 스키마명 : 트리거가 속한 스키마의 이름, 스키마를 명시하지 않으면 기본 스키마가 사용된다.
• 트리거명 : 삭제할 트리거의 이름

트리거 활성/비활성화하기

트리거를 완전히 삭제하지 않고 일시적으로 중지(비활성화)하는 방법도 있다.
DISABLE TRIGGER 명령을 사용하면 트리거가 비활성화 상태가 되는데,
비활성화된 트리거는 테이블에 적용된 DML 작업 시 실행되지는 않지만 여전히 트리거 자체는 존재한다.
즉, 트리거를 지우지 않고 작동을 안하도록 할 수 있다.
반대로 활성화하는 명령은 ENABLE TRIGGER 이다.

DISABLE TRIGGER [스키마명.]트리거명 ON [테이블명] 또는 [DATABASE] 또는 [ALL SERVER];

/* [예시] */
-- 모든 트리거 비활성화하기
DISABLE TRIGGER ALL ON ALL SERVER;

/* [예시]
      트리거 이름 : trg_test
      트리거가 존재하는 테이블 이름 : t1
*/
-- 특정 트리거 비활성화하기
DISABLE TRIGGER trg_test ON t1;

/* [예시] */
-- 비활성화된 특정 트리거 다시 활성화하기
ENABLE TRIGGER trg_test ON t1;

• 스키마명 : 트리거가 속한 스키마 이름
• 트리거명 : 활성화 또는 비활성화할 트리거 이름
• 테이블명 : 트리거가 속한 테이블 이름
• DATABASE : 현재 데이터베이스의 모든 트리거를 비활성화 할 경우 사용
• ALL SERVER : 서버 수준에서 모든 DDL 트리거를 비활성화 할 경우 사용

트리거 비활성화 사용 예시

특정 테이블에 속한 트리거 비활성화

DISABLE TRIGGER trg_after_insert ON Employees;

위 쿼리는 Employees 테이블에 속한 trg_after_insert 트리거를 비활성화한다.

모든 트리거 비활성화 (테이블 단위)

DISABLE TRIGGER ALL ON Orders;

Orders 테이블에 정의된 모든 트리거를 비활성화한다.

데이터베이스 내 모든 트리거 비활성화

DISABLE TRIGGER ALL ON DATABASE;

현재 데이터베이스 내의 모든 트리거를 비활성화한다.

서버 전체의 DDL 트리거 비활성화

DISABLE TRIGGER ALL ON ALL SERVER;

서버 전체에서 발생하는 DDL 작업에 반응하는 모든 트리거를 비활성화한다.

요약

• 트리거를 삭제하려면? ➡️ DROP TRIGGER
• 트리거를 비활성화하려면? ➡️ DISABLE TRIGGER
• 비활성화된 트리거를 다시 활성화하려면? ➡️ ENABLE TRIGGER

✅ 함께 보면 좋은 글

• MSSQL – 트리거의 개념, 사용 방법(Insert, Update, Delete, Inserted, Deleted)