트리거는 SQL Server의 데이터베이스 엔터티입니다. 기술적으로 이들은 특정 데이터베이스 작업에 응답하는 특정 클래스의 함수 호출입니다.
이 필수 가이드는 직업에서 매우 유용할 수 있는 SQL 트리거에 대한 심층 정보를 제공합니다. 시작하자!
목차
SQL 트리거란 무엇입니까?
“트리거”라는 단어는 데이터베이스의 내용이 변경될 때마다 서버가 자동으로 쿼리를 실행한다는 명령문을 나타냅니다.
트리거는 메모리 공간에 저장되는 특별히 명명된 SQL 쿼리 그룹입니다. 데이터베이스 이벤트가 발생할 때마다 즉시 호출되는 특정 유형의 함수 호출입니다. 모든 트리거에는 할당된 테이블이 있습니다.
예를 들어 특정 테이블에 새 열이 추가되거나 특정 레코드가 변경될 때마다 트리거가 활성화될 수 있습니다.
Microsoft 개발자 네트워크에 따르면 트리거는 저장 프로시저의 특정 클래스입니다. 트리거 문에서 처음에 트리거를 실행해야 하는 시기를 정의한 다음 트리거가 활성화된 후 수행해야 하는 작업을 제공합니다.
통사론:
CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tableName FOR EACH ROW SET operation [trigger_body];
각 매개변수의 설명
- CREATE TRIGGER trigger_name – 트리거를 구성하거나 기존 트리거의 이름을 변경하는 데 사용됩니다.
- BEFORE/AFTER – 이 쿼리는 트리거의 실행 시간(특정 이벤트 전후)을 정의하는 데 사용됩니다.
- INSERT/UPDATE/DELETE – 테이블에 대해 수행하려는 작업을 설명합니다.
- ON tableName – 여기에서 트리거를 설정할 테이블 이름을 정의합니다.
- FOR EACH ROW – 이 문은 행 트리거와 관련이 있습니다. 즉, 행이 변경될 때마다 트리거가 실행됩니다.
- trigger_body – 트리거가 활성화될 때 수행할 작업을 지정합니다.
트리거는 이미 실행되어 메모리에 안전하게 저장된 쿼리를 재사용할 수 있도록 하는 고유한 ID를 가진 저장된 함수입니다. 이제 SQL에 왜 필요한지 이해해 보겠습니다.
트리거는 이벤트가 발생할 때마다 코드 배포를 규제하는 데 주로 사용됩니다. 다시 말해, 특정 이벤트에 대한 응답으로 특정 코드 조각을 지속적으로 실행해야 하는 경우 트리거를 사용하는 것이 이상적인 선택입니다.
다음은 SQL 데이터베이스 작업에서 트리거를 활용하는 몇 가지 이점입니다.
참조 무결성은 관계형 데이터베이스 시스템의 핵심 핵심 속성입니다. 이는 데이터베이스 시스템에 보관된 데이터가 각 트랜잭션 및 각 작업에 대해 항상 정확해야 함을 의미합니다.
두 개의 테이블이 별도의 데이터베이스 또는 시스템에 있는 경우 제약 조건 값을 사용하여 해당 테이블 내에서 데이터 유효성을 확인할 수 있는 방법이 없습니다. 이러한 상황에서 트리거는 실행을 위한 유일한 옵션입니다.
트리거 인수의 조합
모든 테이블에 대해 6가지 다른 트리거 유형을 지정할 수 있습니다. SQL 행 수준 트리거에 포함된 트리거 인수의 조합입니다.
BEFORE INSERT: 이 트리거는 지정된 테이블이나 데이터베이스에서 INSERT 작업을 수행하기 전에 행에 대한 작업을 실행합니다.
AFTER INSERT: 데이터베이스 INSERT 활동 직후에 행에 대한 작업을 실행합니다.
BEFORE UPDATE: 이 트리거를 사용하면 데이터베이스에서 UPDATE 작업이 수행되기 전에 행에 대한 기능이 수행됩니다.
AFTER UPDATE: 데이터베이스 또는 특정 테이블 UPDATE 활동 직후에 행에 대한 작업을 실행합니다.
BEFORE DELETE: 데이터베이스나 테이블이 DELETE 작업을 받기 전에도 행에 대해 특정 작업을 실행합니다.
AFTER DELETE: 이 트리거는 모든 DELETE 트랜잭션 다음에 오는 행에서 작업을 실행합니다.
SQL 트리거 유형
SQL 트리거는 특정 이벤트가 발생할 때 즉시 실행되는 저장 함수입니다. 이벤트 기반 스케줄링과 유사합니다. 후속 상황에서 트리거 실행을 시작할 수 있습니다.
DML 트리거 – DML은 데이터 조작 언어를 나타냅니다. 데이터 수정에 대한 반응으로 코드 실행은 DML 트리거를 사용하여 가능합니다. 이 트리거는 INSERT, UPDATE 및 DELETE와 같은 DML 명령이 실행될 때 활성화됩니다. 이를 “테이블 레벨 트리거”라고도 합니다.
DDL 트리거 – DDL은 데이터 정의 언어를 나타냅니다. DDL 트리거를 사용하면 테이블 추가 또는 삭제와 같은 데이터베이스 스키마 변경 또는 사용자가 체크인할 때와 같은 서버 이벤트에 대한 반응으로 코드를 실행할 수 있습니다. 이를 “데이터베이스 수준 트리거”라고 합니다.
이러한 트리거는 활성 데이터베이스에서 CREATE, ALTER 또는 DROP과 같은 특정 DDL 문이 실행될 때 활성화될 수 있습니다. 또한 실행되는 활동을 주시하고 관리하는 데 사용할 수 있습니다.
로그온 트리거 – 로그온(시작, 로그인, 로그아웃, 종료) 이벤트가 발생할 때마다 로그온 트리거가 즉시 호출됩니다. 사용자 트랜잭션이 시작되기 전에도 사용자 인증 프로세스를 거쳐야만 수행됩니다. 인증이 실패하면 LOGON 트리거가 트리거되지 않습니다.
이러한 트리거는 서버 연결에 대한 다른 감사 및 ID 관리 기능 중에서 로그인 기록을 기록하거나 특정 로그인에 대한 이벤트 제한을 설정하는 데 사용할 수 있습니다.
CLR 트리거 – CLR은 공용 언어 런타임을 나타냅니다. CLR 트리거는 실제로 .NET 기술 내에서 주로 CLR을 기반으로 구축된 고유한 트리거 하위 집합입니다. 이러한 트리거는 트리거가 많은 계산을 수행해야 하거나 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”을 정의했으며 모든 후속 표현식에는 점 연산자를 사용하여 total 다음에 새 키워드가 접두사로 붙습니다. 이제 각 행에 값을 추가하고 결과를 확인합니다. 처음에는 모든 학생의 총 점수가 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);
이 경우 데이터가 학생 테이블에 삽입되면 트리거 문이 자동으로 트리거됩니다. 각 학생의 총 점수는 트리거에 의해 계산됩니다. 이제 SELECT 문을 사용하여 트리거가 호출되는지 여부를 살펴보겠습니다.
SELECT * FROM table_name;
다음은 최종 출력입니다.
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 문을 사용하면 다음 명령을 사용하여 트리거를 활성화, 비활성화 또는 삭제할 수 있습니다.
특정 Trigger가 존재하는지 확인하는 쿼리
이 명령은 전체 데이터베이스에서 지정된 트리거를 확인합니다.
SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name'
트리거를 표시하는 쿼리
활성 데이터베이스에서 사용 가능한 모든 트리거는 다음 명령문으로 표시됩니다.
SHOW TRIGGERS;
트리거 비활성화 쿼리
아래 명령은 작업 데이터베이스에서 트리거를 비활성화합니다.
DISABLE TRIGGER trigger_name ON DATABASE;
특정 테이블 이름을 지정하여 트리거를 비활성화할 수도 있습니다.
DISABLE TRIGGER trigger_name ON table_name;
트리거 활성화 쿼리
다음 명령은 다시 활성화하기 전에 활성 데이터베이스의 지정된 테이블에 정의된 특정 트리거를 먼저 비활성화합니다.
ALTER TABLE table_name DISABLE TRIGGER trigger_name ALTER TABLE table_name ENABLE TRIGGER trigger_name
트리거를 활성화하기 전에 비활성화해야 합니다.
테이블의 모든 트리거를 활성화 또는 비활성화하는 쿼리
위의 SQL 문을 사용하여 특정 트리거 이름 대신 “ALL”을 대체하여 한 번에 모든 테이블 트리거를 비활성화하거나 활성화할 수 있습니다.
ALTER TABLE table_name DISABLE TRIGGER ALL ALTER TABLE table_name ENABLE TRIGGER ALL
트리거 삭제 또는 삭제 쿼리
트리거를 삭제하거나 전체 테이블을 삭제하여 트리거를 제거할 수 있습니다. 테이블이 삭제되면 모든 관련 트리거도 삭제됩니다.
DROP TRIGGER [trigger_name];
트리거가 삭제될 때마다 관련 데이터가 sys.objects 데이터 테이블에서 제거됩니다.
트리거의 장점
- 트리거를 구축하는 것은 간단하며 트리거 자체는 저장된 함수와 메서드를 호출할 수 있습니다.
- 사용자는 트리거를 사용하여 간단한 감사를 구현할 수 있습니다.
- 트리거를 사용하여 제약 조건 작업을 에뮬레이트할 수 있지만 안타깝게도 SQL Server를 사용하여 데이터베이스 시스템의 엔터티 간에 제약 조건을 만들 수는 없습니다.
- 무결성 제약 조건은 트리거를 사용하여 데이터베이스 전반에 걸쳐 구현될 수 있습니다.
- 새로 입력되거나 변경된 데이터를 행 단위로 확인하는 대신 그룹 유효성 검사가 필요한 경우 트리거가 도움이 될 수 있습니다.
트리거의 단점
SQL 트리거는 제한 사항으로 인해 일부 상황에서 최선의 선택이 아닐 수 있습니다.
- 트리거를 정확하게 문서화해야 합니다.
- 애플리케이션 구성 요소에 액세스할 수 없는 동시 데이터베이스 실행으로 인해 트리거는 디버그하기 어려울 수 있습니다.
- 트리거가 사용되면 DML 문은 더 복잡해집니다.
- 사소한 트리거 문제라도 명령문에서 논리적 오류로 이어질 가능성이 있습니다.
결론
트리거는 Transact-SQL 및 SQL의 매우 유용한 구성 요소이며 Oracle에서도 사용할 수 있습니다. 저장 메소드를 호출할 때 트리거를 사용하는 것이 중요합니다. 이러한 SQL 트리거를 통해 활동 타임라인을 분석하고 필요한 경우 이에 대응하는 방법을 결정할 수 있습니다. 데이터를 수집하기 위해 트리거에 연결된 특정 테이블을 확인할 수도 있습니다.
재귀는 트리거에 의해 활성화될 수 있습니다. 테이블의 트리거가 상위 테이블에서 명령을 실행할 때마다 트리거의 두 번째 반복이 시작되며 이를 재귀 트리거라고 합니다. 이는 ID 상관 관계를 해결하는 데 도움이 됩니다.
또한 트리거는 데이터베이스가 허용하는 업데이트 패턴을 조절합니다. SQL 제약 조건 키가 주로 기본 키와 외래 키가 존재하지 않는 경우 데이터베이스 시스템에서 데이터 무결성 제약 조건을 유지하는 것이 매우 유용합니다.
이 문서가 SQL 트리거에 대해 배우는 데 도움이 되었기를 바랍니다.
데이터베이스를 심층적으로 배우고 싶다면 SQL 및 NoSQL을 배울 수 있는 몇 가지 훌륭한 리소스가 있습니다.