SQL 트리거: 필수 가이드
트리거는 SQL Server 데이터베이스 시스템의 핵심 요소 중 하나입니다. 이는 특정 데이터베이스 작업에 반응하는 특수한 유형의 함수 호출로 간주될 수 있습니다.
이 가이드는 SQL 트리거에 대한 상세한 정보를 제공하며, 여러분의 실무에 큰 도움이 될 것입니다. 이제 시작해 보겠습니다!
SQL 트리거란?
"트리거"라는 용어는 데이터베이스 내용이 변경될 때마다 서버가 자동으로 쿼리를 실행하도록 지시하는 명령어를 나타냅니다.
트리거는 메모리 공간에 저장되는 특별한 SQL 쿼리 그룹입니다. 데이터베이스 이벤트 발생 시 즉시 호출되는 특정 함수 호출 형태입니다. 모든 트리거는 연결된 테이블을 가지고 있습니다.
예를 들어, 특정 테이블에 새로운 열이 추가되거나 특정 데이터가 수정될 때 트리거가 자동으로 실행될 수 있습니다.
Microsoft 개발자 네트워크에 따르면, 트리거는 저장 프로시저의 특정 범주입니다. 트리거문에서 트리거가 실행되어야 하는 시점을 정의한 후, 트리거가 활성화되면 수행할 작업을 지정합니다.
구문:
CREATE TRIGGER 트리거_이름 BEFORE/AFTER INSERT/UPDATE/DELETE ON 테이블_이름 FOR EACH ROW SET 작업 [트리거_본문];
각 매개변수에 대한 설명:
- CREATE TRIGGER 트리거_이름 – 새로운 트리거를 생성하거나 기존 트리거의 이름을 변경합니다.
- BEFORE/AFTER – 트리거 실행 시점(특정 이벤트 전 또는 후)을 정의합니다.
- INSERT/UPDATE/DELETE – 테이블에서 수행하려는 작업의 종류를 나타냅니다.
- ON 테이블_이름 – 트리거가 적용될 테이블의 이름을 지정합니다.
- FOR EACH ROW – 각 행이 변경될 때마다 트리거가 실행되는 행 수준 트리거임을 나타냅니다.
- trigger_body – 트리거가 활성화되었을 때 실행할 구체적인 작업을 정의합니다.
트리거는 메모리에 저장된 쿼리를 재사용할 수 있도록 고유한 식별자를 가진 저장된 함수입니다. 이제 SQL에서 트리거가 왜 필요한지 알아보겠습니다.
트리거는 특정 이벤트가 발생할 때 코드를 실행해야 하는 경우에 주로 사용됩니다. 즉, 특정 사건에 대한 응답으로 특정 코드 조각을 지속적으로 실행해야 할 때 트리거가 매우 유용합니다.
다음은 SQL 데이터베이스 작업에서 트리거를 활용함으로써 얻을 수 있는 몇 가지 이점입니다.
참조 무결성은 관계형 데이터베이스 시스템에서 매우 중요한 속성입니다. 이는 데이터베이스에 저장된 데이터가 모든 트랜잭션 및 작업에서 항상 정확해야 함을 의미합니다.
만약 두 개의 테이블이 서로 다른 데이터베이스나 시스템에 존재한다면, 제약 조건만으로는 이 테이블들 사이의 데이터 유효성을 확인할 방법이 없습니다. 이런 경우, 트리거는 데이터 무결성을 보장하는 유일한 대안이 될 수 있습니다.
트리거 인자의 조합
모든 테이블에 대해 총 6가지 유형의 트리거를 정의할 수 있습니다. 이는 SQL 행 수준 트리거에서 사용 가능한 인자들의 조합입니다.
BEFORE INSERT: 지정된 테이블이나 데이터베이스에 새로운 데이터를 삽입하기 전에 작업을 수행합니다.
AFTER INSERT: 데이터베이스에 데이터가 성공적으로 삽입된 직후에 작업을 수행합니다.
BEFORE UPDATE: 데이터베이스에서 데이터 업데이트 작업이 수행되기 전에 작업을 수행합니다.
AFTER UPDATE: 데이터베이스의 특정 테이블에서 업데이트 작업이 완료된 직후에 작업을 수행합니다.
BEFORE DELETE: 데이터베이스나 테이블에서 데이터 삭제 작업이 실행되기 전에 특정 작업을 수행합니다.
AFTER DELETE: 데이터 삭제 트랜잭션이 완료된 후 작업을 실행합니다.
SQL 트리거 유형
SQL 트리거는 특정 이벤트 발생 시 즉시 실행되는 저장된 함수입니다. 이는 이벤트 기반 스케줄링과 유사하게 작동하며, 이벤트 발생 시 트리거 실행을 시작할 수 있습니다.
DML 트리거 – DML은 데이터 조작 언어(Data Manipulation Language)를 의미합니다. DML 트리거는 데이터 변경 작업에 대한 반응으로 코드를 실행하는 데 사용됩니다. 이 트리거는 INSERT, UPDATE, DELETE와 같은 DML 명령이 실행될 때 활성화되며, "테이블 수준 트리거"라고도 불립니다.
DDL 트리거 – DDL은 데이터 정의 언어(Data Definition Language)를 의미합니다. DDL 트리거를 사용하면 테이블 추가 또는 삭제와 같은 데이터베이스 스키마 변경이나, 사용자가 데이터베이스에 접속할 때와 같은 서버 이벤트에 대한 반응으로 코드를 실행할 수 있습니다. 이를 "데이터베이스 수준 트리거"라고도 합니다.
이러한 트리거는 데이터베이스에서 CREATE, ALTER 또는 DROP과 같은 특정 DDL 문이 실행될 때 활성화될 수 있습니다. 또한, 실행되는 활동을 추적하고 관리하는 데에도 활용될 수 있습니다.
로그온 트리거 – 로그온(시작, 로그인, 로그아웃, 종료) 이벤트가 발생할 때마다 로그온 트리거가 자동으로 호출됩니다. 사용자 트랜잭션이 시작되기 전, 사용자 인증 프로세스가 완료된 후에 실행됩니다. 인증에 실패하면 로그온 트리거는 실행되지 않습니다.
이러한 트리거는 서버 연결에 대한 감사 기록을 기록하거나, 특정 로그인에 대한 이벤트 제한을 설정하는 데 사용될 수 있습니다.
CLR 트리거 – CLR은 공용 언어 런타임(Common Language Runtime)을 나타냅니다. CLR 트리거는 .NET 기술을 기반으로 구축된 트리거의 하위 집합입니다. 이러한 트리거는 트리거가 복잡한 계산을 수행해야 하거나, SQL 외부의 엔터티와 상호 작용해야 하는 경우 유용합니다.
DML 및 DDL 트리거는 Visual Basic, C#, F-sharp와 같은 .NET 기술에서 지원하는 CLR 트리거 코드를 사용하여 구성할 수 있습니다.
샘플 SQL Server 트리거
예제를 통해 트리거 개념을 이해해 보겠습니다.
먼저, SQL 문을 사용하여 데이터베이스를 생성합니다.
CREATE DATABASE testdb; use testdb;
여기서는 데이터베이스 이름을 "testdb"로 지정했습니다. 다음 단계는 테이블을 만드는 것입니다.
CREATE TABLE student( name varchar(25), id int(2), maths int(2), physics int(2), biology int(2), social int(2), total int(2) );
학생 정보를 저장할 테이블을 만들었습니다. 다음은 테이블 구조를 보여주는 명령입니다. 여기에서 "student"는 테이블 이름입니다.
DESC student;
아래는 생성된 테이블의 구조입니다.
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(25) | YES | | NULL | | | id | int | YES | | NULL | | | maths | int | YES | | NULL | | | physics | int | YES | | NULL | | | biology | int | YES | | NULL | | | social | int | YES | | NULL | | | total | int | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)
테이블을 생성한 후, 트리거를 설정하는 다음 단계로 넘어갑니다. 이번에는 BEFORE INSERT 인자를 사용해 보겠습니다.
생성한 트리거의 이름은 "marks"입니다. 학생의 성적 정보로 테이블이 수정되는 즉시, 아래 트리거는 학생의 총점을 자동으로 계산합니다.
CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;
이전 데이터를 사용하는 대신 행 데이터를 대체해야 하므로 "total"을 정의할 때 new라는 이름을 사용했습니다. 모든 후속 표현식에는 점 연산자를 사용하여 total 앞에 new 키워드를 접두사로 사용했습니다. 이제 각 행에 데이터를 추가하고 결과를 확인합니다. 처음에는 모든 학생의 총점이 0입니다.
INSERT INTO student VALUES("George",02,99,87,92,91,0);
INSERT INTO student VALUES("James",03,91,81,94,90,0);
INSERT INTO student VALUES("Harry",04,86,70,73,88,0);
INSERT INTO student VALUES("John",05,73,89,78,92,0);
INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);
데이터가 student 테이블에 삽입되면 트리거 문이 자동으로 실행됩니다. 각 학생의 총점은 트리거에 의해 계산됩니다. 이제 SELECT 문을 사용하여 트리거가 제대로 동작하는지 확인합니다.
SELECT * FROM student;
다음은 최종 결과입니다.
mysql> select * from student; +--------+------+-------+---------+---------+--------+-------+ | name | id | maths | physics | biology | social | total | +--------+------+-------+---------+---------+--------+-------+ | George | 2 | 91 | 81 | 94 | 90 | 356 | | James | 3 | 86 | 70 | 73 | 88 | 317 | | Harry | 4 | 73 | 89 | 78 | 92 | 332 | | John | 5 | 94 | 75 | 69 | 79 | 317 | | Lisa | 1 | 99 | 87 | 92 | 91 | 369 | +--------+------+-------+---------+---------+--------+-------+ 5 rows in set (0.00 sec)
위 결과에서 모든 과목의 점수가 각 학생에 대해 자동으로 더해진 것을 볼 수 있습니다. 트리거가 성공적으로 호출된 것을 확인할 수 있습니다.
추가 트리거 작업
트리거를 사용하여 다양한 작업을 수행할 수 있습니다. 일부 작업은 간단하지만, 일부는 다소 복잡할 수 있습니다. 하지만 쿼리문을 살펴보면 쉽게 이해할 수 있습니다. Transact-SQL 문을 사용하면 아래 명령을 사용하여 트리거를 활성화, 비활성화 또는 삭제할 수 있습니다.
특정 트리거의 존재 여부 확인 쿼리
이 명령은 지정된 트리거가 데이터베이스에 존재하는지 확인합니다.
SELECT * FROM [sys].[triggers] WHERE [name] = '트리거_이름'
트리거 표시 쿼리
현재 데이터베이스에서 사용 가능한 모든 트리거는 다음 명령으로 표시됩니다.
SHOW TRIGGERS;
트리거 비활성화 쿼리
아래 명령은 지정된 데이터베이스의 트리거를 비활성화합니다.
DISABLE TRIGGER 트리거_이름 ON DATABASE;
특정 테이블 이름을 지정하여 해당 테이블의 트리거만 비활성화할 수도 있습니다.
DISABLE TRIGGER 트리거_이름 ON 테이블_이름;
트리거 활성화 쿼리
다음 명령은 먼저 활성 데이터베이스의 지정된 테이블에 정의된 특정 트리거를 비활성화한 후, 다시 활성화합니다.
ALTER TABLE 테이블_이름 DISABLE TRIGGER 트리거_이름 ALTER TABLE 테이블_이름 ENABLE TRIGGER 트리거_이름
트리거를 활성화하기 전에 반드시 비활성화해야 합니다.
테이블의 모든 트리거 활성화/비활성화 쿼리
위의 SQL 문에서 특정 트리거 이름 대신 "ALL"을 사용하여 테이블의 모든 트리거를 한 번에 비활성화하거나 활성화할 수 있습니다.
ALTER TABLE 테이블_이름 DISABLE TRIGGER ALL ALTER TABLE 테이블_이름 ENABLE TRIGGER ALL
트리거 삭제 쿼리
트리거를 삭제하거나, 테이블을 삭제하여 트리거를 제거할 수 있습니다. 테이블이 삭제되면 관련된 모든 트리거도 함께 삭제됩니다.
DROP TRIGGER [트리거_이름];
트리거가 삭제될 때마다 관련 데이터가 sys.objects 데이터 테이블에서 제거됩니다.
트리거의 장점
- 트리거를 만드는 것은 간단하며, 트리거 자체에서 저장된 함수나 메서드를 호출할 수 있습니다.
- 사용자는 트리거를 사용하여 간단한 데이터 감사 기능을 구현할 수 있습니다.
- 트리거를 사용하여 제약 조건과 비슷한 작업을 수행할 수 있지만, SQL Server를 사용하여 데이터베이스 시스템의 엔터티 간에 제약 조건을 만드는 것은 불가능합니다.
- 데이터 무결성 제약 조건을 데이터베이스 전반에 걸쳐 구현하는 데 사용할 수 있습니다.
- 새로 입력되거나 변경된 데이터를 행 단위로 확인하는 것 대신, 그룹 유효성 검사가 필요한 경우 트리거가 유용할 수 있습니다.
트리거의 단점
SQL 트리거는 제한 사항으로 인해 일부 상황에서는 최선의 선택이 아닐 수 있습니다.
- 트리거는 정확하게 문서화해야 합니다.
- 트리거는 애플리케이션 구성 요소에 접근할 수 없는 동시 데이터베이스 실행으로 인해 디버깅이 어려울 수 있습니다.
- 트리거를 사용하면 DML 문이 더 복잡해질 수 있습니다.
- 사소한 트리거 문제라도 명령문에서 논리적 오류로 이어질 가능성이 있습니다.
결론
트리거는 Transact-SQL과 SQL에서 매우 유용한 구성 요소이며, Oracle에서도 사용할 수 있습니다. 저장된 메서드를 호출해야 할 때 트리거를 사용하는 것이 중요합니다. 이러한 SQL 트리거를 통해 활동 타임라인을 분석하고 필요한 경우 대응 방법을 결정할 수 있습니다. 데이터를 수집하기 위해 트리거에 연결된 특정 테이블을 확인할 수도 있습니다.
재귀는 트리거에 의해 발생할 수 있습니다. 테이블의 트리거가 상위 테이블에서 명령을 실행할 때마다 트리거의 두 번째 반복이 시작되며, 이를 재귀 트리거라고 합니다. 이는 ID 상관 관계를 해결하는 데 도움이 됩니다.
또한, 트리거는 데이터베이스에서 허용하는 업데이트 패턴을 조절합니다. SQL 제약 조건 키(주로 기본 키와 외래 키)가 없는 경우, 데이터베이스 시스템에서 데이터 무결성 제약 조건을 유지하는 데 매우 유용합니다.
이 글이 SQL 트리거를 이해하는 데 도움이 되었기를 바랍니다.
데이터베이스에 대해 더 깊이 배우고 싶다면 SQL과 NoSQL을 배울 수 있는 다양한 자료들이 있습니다.