MySQL에서 트리거를 사용하는 방법

저자는 Write for DOnations 프로그램을 선택했습니다.

소개

관계형 데이터베이스 및 UPDATE로 작업할 때.

그러나 트리거를 통해 특정 이벤트가 발생할 때마다 미리 정의된 작업을 자동으로 수행하도록 SQL 데이터베이스에 지시할 수도 있습니다. 예를 들어 트리거를 사용하여 모든 DELETE 문의 감사 추적 로그를 유지하거나 행이 업데이트되거나 테이블에 추가될 때마다 집계된 통계 요약을 자동으로 업데이트할 수 있습니다.

이 자습서에서는 다양한 SQL 트리거를 사용하여 행이 삽입, 업데이트 또는 삭제되는 작업을 자동으로 수행합니다.

전제 조건

이 가이드를 따르려면 SQL 기반 RDBMS(관계형 데이터베이스 관리 시스템)를 실행하는 컴퓨터가 필요합니다. 이 가이드의 지침과 예제는 다음 환경을 사용하여 검증되었습니다.

• Ubuntu 20.04용 초기 서버 설정 가이드에 설명된 대로 관리 권한이 있는 루트가 아닌 사용자와 UFW로 구성된 방화벽이 있는 Ubuntu 20.04를 실행하는 서버
• 3단계에 설명된 대로 서버에 MySQL을 설치하고 보호합니다.
• 데이터베이스에서 데이터를 조작하기 위해 SELECT, INSERT, UPDATE 및 DELETE 쿼리 실행에 대한 기본 지식 SQL 가이드에서 데이터를 삭제하는 방법에 설명된 대로
• SQL 가이드에서 중첩 쿼리를 사용하는 방법에 설명된 중첩 쿼리 사용에 대한 기본 지식
• How To Use Mathematical Expressions and Aggregate Functions in SQL 가이드에 설명된 집계 수학 함수 사용에 대한 기본 지식.

참고: 많은 RDBMS는 자체 SQL 구현을 사용합니다. 트리거는 SQL 표준의 일부로 언급되지만 표준은 트리거의 구문이나 트리거를 구현하는 엄격한 방법을 적용하지 않습니다. 결과적으로 구현은 데이터베이스마다 다릅니다. 이 자습서에 설명된 명령은 MySQL 데이터베이스의 구문을 사용하며 다른 데이터베이스 엔진에서는 작동하지 않을 수 있습니다.

또한 함수 사용을 연습할 수 있도록 샘플 데이터가 로드된 일부 테이블이 있는 데이터베이스가 필요합니다. MySQL 서버에 연결하고 이 가이드의 예제에 사용된 테스트 데이터베이스를 만드는 방법에 대한 자세한 내용은 다음 MySQL에 연결 및 샘플 데이터베이스 설정 섹션을 참조하십시오.

MySQL에 연결 및 샘플 데이터베이스 설정

이 섹션에서는 다음 섹션의 예제를 따를 수 있도록 MySQL 서버에 연결하고 샘플 데이터베이스를 생성합니다.

이 가이드에서는 상상의 수집품 컬렉션을 사용합니다. 현재 소유하고 있는 수집품에 대한 세부 정보를 저장하고, 총 가치를 쉽게 사용할 수 있도록 유지하고, 수집품을 제거하면 항상 흔적이 남도록 합니다.

SQL 데이터베이스 시스템이 원격 서버에서 실행되는 경우 로컬 시스템에서 서버로 SSH합니다.

ssh sammy@your_server_ip

그런 다음 MySQL 서버 프롬프트를 열고 sammy를 MySQL 사용자 계정의 이름으로 바꿉니다.

mysql -u sammy -p

collectibles라는 데이터베이스를 생성합니다.

CREATE DATABASE collectibles;

데이터베이스가 성공적으로 생성되면 다음과 같은 출력을 받게 됩니다.

Output
Query OK, 1 row affected (0.01 sec)

collectibles 데이터베이스를 선택하려면 다음 USE 문을 실행합니다.

USE collectibles;

다음 출력을 받게 됩니다.

Output
Database changed

데이터베이스를 선택한 후 그 안에 샘플 테이블을 생성할 수 있습니다. 테이블 collectibles에는 데이터베이스의 수집품에 대한 단순화된 데이터가 포함됩니다. 다음 열을 포함합니다.

• name: 이 열에는 최대 50자varchar 데이터 유형을 사용하여 표현되는 각 수집품의 이름이 있습니다.
• value: 이 열은 decimal 데이터 유형을 사용하여 수집품의 시장 가치를 저장하며 소수점 앞에 최대 값은 5이고 <2 값이 뒤에 있습니다.

다음 명령을 사용하여 샘플 테이블을 생성합니다.

CREATE TABLE collectibles (
    name varchar(50),
    value decimal(5, 2)
);

다음 출력이 인쇄되면 테이블이 생성된 것입니다.

Output
Query OK, 0 rows affected (0.00 sec)

다음 테이블은 collectibles_stats라고 하며 컬렉션에 있는 모든 수집품의 누적 가치를 추적하는 데 사용됩니다. 다음 열이 있는 단일 데이터 행을 보유합니다.

• 개수: 이 열은 int 데이터 유형을 사용하여 표현되는 소유 수집품의 수를 보유합니다.
• value: 이 열은 소수점 앞에 최대 5 값이 있는 decimal 데이터 유형을 사용하여 모든 수집품의 누적 가치를 저장합니다. 그리고 그 뒤에 2개의 값이 있습니다.

다음 명령을 사용하여 샘플 테이블을 생성합니다.

CREATE TABLE collectibles_stats (
    count int,
    value decimal(5, 2)
);

다음 출력이 인쇄되면 테이블이 생성된 것입니다.

Output
Query OK, 0 rows affected (0.00 sec)

세 번째이자 마지막 테이블은 collectibles_archive라고 하며 컬렉션에서 제거된 모든 수집품을 추적하여 절대 사라지지 않도록 합니다. 삭제 날짜가 추가된 collectibles 테이블과 유사한 데이터를 보유합니다. 다음 열을 사용합니다.

• name: 이 열에는 최대 50 문자로 varchar 데이터 유형을 사용하여 표현된 제거된 각 수집품의 이름이 있습니다.< /리>
• value: 이 열은 삭제되기 전 최대 5 값을 가진 십진수 데이터 유형을 사용하여 삭제 시점의 수집품 시장 가치를 저장합니다. 소수점과 그 뒤의 2 값.
• removed_on: 이 열은 NOW()타임스탬프 데이터 유형을 사용하여 보관된 각 수집품의 삭제 날짜와 시간을 저장합니다., 이 테이블에 새 행이 삽입될 때마다 현재 날짜를 의미합니다.

다음 명령을 사용하여 샘플 테이블을 생성합니다.

CREATE TABLE collectibles_archive (
    name varchar(50),
    value decimal(5, 2),
    removed_on timestamp DEFAULT CURRENT_TIMESTAMP
);

다음 출력이 인쇄되면 테이블이 생성된 것입니다.

Output
Query OK, 0 rows affected (0.00 sec)

그런 다음 다음 INSERT INTO 작업을 실행하여 빈 수집품 컬렉션의 초기 상태로 collectibles_stats 테이블을 로드합니다.

INSERT INTO collectibles_stats SELECT COUNT(name), SUM(value) FROM collectibles;

INSERT INTO 작업은 집계 함수를 사용하여 계산된 값으로 collectibles_stats에 단일 행을 추가하여 collectibles 테이블의 모든 행을 계산하고 value 열과 SUM 함수를 사용하여 모든 수집품의 가치를 합산합니다. 다음 출력은 행이 추가되었음을 나타냅니다.

Output
Query OK, 1 row affected (0.002 sec)
Records: 1  Duplicates: 0  Warnings: 0

테이블에서 SELECT 문을 실행하여 확인할 수 있습니다.

SELECT * FROM collectibles_stats;

아직 데이터베이스에 수집품이 없기 때문에 초기 항목 수는 0이고 누적 값은 NULL입니다.

Output
+-------+-------+
| count | value |
+-------+-------+
|     0 |  NULL |
+-------+-------+
1 row in set (0.000 sec)

이것으로 가이드의 나머지 부분을 따르고 MySQL에서 트리거를 사용할 준비가 된 것입니다.

트리거 이해

트리거는 지정된 이벤트가 해당 테이블에서 발생할 때마다 데이터베이스에 의해 자동으로 실행되는 특정 테이블에 대해 정의된 명령문입니다. 데이터베이스 사용자가 수동으로 실행해야 하는 것을 기억해야 하는 대신 테이블에서 특정 문이 실행될 때마다 일부 작업이 일관되게 수행되도록 트리거를 사용할 수 있습니다.

테이블과 관련된 모든 트리거는 사용자 정의 이름과 트리거 실행 시기를 데이터베이스 엔진에 지시하는 한 쌍의 조건으로 식별됩니다. 이들은 두 개의 개별 클래스로 그룹화할 수 있습니다.

• 데이터베이스 이벤트: INSERT, UPDATE 또는 DELETE 문이 테이블에서 실행될 때 트리거가 실행될 수 있습니다.
• 이벤트 시간: 또한 트리거는 해당 명령문 이전 또는 이후에 실행될 수 있습니다.

두 조건 그룹을 결합하면 결합 조건이 충족될 때마다 자동으로 실행되는 총 6개의 개별 트리거 가능성이 생성됩니다. 조건을 충족하는 문이 실행되기 전에 발생하는 트리거는 BEFORE INSERT, BEFORE UPDATE 및 BEFORE DELETE입니다. 데이터가 테이블에 삽입 또는 업데이트되기 전에 데이터를 조작하고 유효성을 검사하거나 감사 또는 보관 목적으로 삭제된 행의 세부 정보를 저장하는 데 사용할 수 있습니다.

조건을 만족하는 문이 실행된 후 발생하는 트리거는 AFTER INSERT, AFTER UPDATE 및 AFTER DELETE입니다. 명령문 이후 데이터베이스의 최종 상태를 기반으로 별도의 테이블에서 요약된 값을 업데이트하는 데 사용할 수 있습니다.

입력 데이터의 유효성 검사 및 조작 또는 삭제된 행 보관과 같은 작업을 수행하기 위해 데이터베이스는 트리거 내에서 데이터 값에 액세스할 수 있도록 허용합니다. INSERT 트리거의 경우 새로 삽입된 데이터만 사용할 수 있습니다. UPDATE 트리거의 경우 원본 데이터와 업데이트된 데이터 모두에 액세스할 수 있습니다. 마지막으로 DELETE 트리거를 사용하면 참조할 새 데이터가 없기 때문에 원래 행 데이터만 사용할 수 있습니다.

트리거 본문 내에서 사용할 데이터는 현재 데이터베이스에 있는 데이터에 대한 OLD 레코드와 쿼리가 저장할 데이터에 대한 NEW 레코드 아래에 노출됩니다. OLD.column_name 및 NEW.column_name 구문을 사용하여 개별 열을 참조할 수 있습니다.

다음 예는 새 트리거를 만드는 데 사용되는 SQL 문의 일반 구문을 보여줍니다.

CREATE TRIGGER trigger_name trigger_condition
ON table_name
FOR EACH ROW
trigger_actions;

구문을 더 작은 부분으로 분석해 보겠습니다.

• CREATE TRIGGER는 데이터베이스에서 새 트리거를 생성하는 데 사용되는 SQL 문의 이름입니다.
• trigger_name은 테이블 이름과 열 이름이 의미를 설명하는 데 사용되는 방식과 유사하게 해당 역할을 설명하는 데 사용되는 트리거의 사용자 정의 이름입니다.< /리>
• ON table_name은 트리거가 table_name 테이블에서 발생하는 이벤트를 모니터링해야 함을 데이터베이스에 알립니다.
• trigger_condition은 BEFORE INSERT와 같이 트리거가 실행되어야 하는 시기를 정의하는 6가지 가능한 선택 중 하나입니다.
• FOR EACH ROW는 트리거 이벤트의 영향을 받는 각 행에 대해 트리거를 실행해야 함을 데이터베이스에 알립니다. 일부 데이터베이스는 FOR EACH ROW 이외의 추가 실행 패턴을 지원합니다. 그러나 MySQL의 경우 트리거 실행을 유발한 명령문의 영향을 받는 각 행에 대해 트리거 본문에서 명령문을 실행하는 것이 유일한 옵션입니다.
• trigger_actions는 트리거의 본문이며 트리거가 실행될 때 발생하는 일을 정의합니다. 일반적으로 하나의 유효한 SQL 문입니다. 트리거 본문에 여러 문을 포함하여 BEGIN 및 END 키워드를 사용하여 블록의 문 목록을 묶는 복잡한 데이터 작업을 수행할 수 있습니다. 그러나 이것은 이 자습서의 범위를 벗어납니다. 트리거를 정의하는 데 사용되는 구문에 대해 자세히 알아보려면 트리거에 대한 공식 문서를 확인하세요.

다음 섹션에서는 INSERT 및 UPDATE 작업 전에 데이터를 조작하는 트리거를 만듭니다.

BEFORE INSERT 및 BEFORE UPDATE 트리거로 데이터 조작

이 섹션에서는 INSERT 및 UPDATE 문이 실행되기 전에 트리거를 사용하여 데이터를 조작합니다.

이 예에서는 트리거를 사용하여 데이터베이스의 모든 수집품이 일관성을 위해 대문자 이름을 사용하도록 합니다. 트리거를 사용하지 않으면 각 INSERT 및 UPDATE 문에 수집 가능한 대문자 이름을 사용해야 합니다. 잊어버리면 데이터베이스가 데이터를 있는 그대로 저장하여 데이터 세트에 실수가 발생할 수 있습니다.

$12.50 상당의 우주선 모델이라는 수집 가능한 항목 예시를 삽입하여 시작합니다. 항목 이름은 문제를 설명하기 위해 소문자로 작성됩니다. 다음 문을 실행합니다.

INSERT INTO collectibles VALUES ('spaceship model', 12.50);

다음 메시지는 항목이 추가되었음을 확인합니다.

Output
Query OK, 1 row affected (0.009 sec)

SELECT 쿼리를 실행하여 행이 삽입되었는지 확인할 수 있습니다.

SELECT * FROM collectibles;

다음 출력이 화면에 인쇄됩니다.

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| spaceship model | 12.50 |
+-----------------+-------+
1 row in set (0.000 sec)

수집 가능한 항목은 그대로 저장되었으며 이름은 소문자로만 표기되었습니다.

향후 모든 수집품이 항상 대문자로 작성되도록 하려면 BEFORE INSERT 트리거를 생성합니다. 트리거 문이 실행되기 전에 실행되는 트리거를 사용하면 발생하기 전에 데이터베이스에 전달될 데이터를 조작할 수 있습니다.

다음 문을 실행합니다.

CREATE TRIGGER uppercase_before_insert BEFORE INSERT
ON collectibles
FOR EACH ROW
SET NEW.name = UPPER(NEW.name);

이 명령은 collectibles라는 테이블에서 모든 INSERT 문 이전에 실행될 uppercase_before_insert라는 트리거를 만듭니다.

트리거 SET NEW.name = UPPER(NEW.name)의 명령문은 삽입된 각 행에 대해 실행됩니다. SET SQL 명령은 오른쪽에 있는 값을 왼쪽에 할당합니다. 이 경우 NEW.name은 삽입문이 저장할 name 열의 값을 나타냅니다. 수집 가능한 이름에 UPPER 함수를 적용하고 열 값에 다시 할당하면 데이터베이스에 저장될 값의 대소문자를 변환하고 있습니다.

참고: CREATE TRIGGER 명령을 실행할 때 ERROR 1419 (HY000)와 유사한 오류 메시지가 나타날 수 있습니다. SUPER 권한이 없으며 바이너리 로깅이 활성화되어 있습니다. * 덜 안전한 log_bin_trust_function_creators 변수를 사용하고 싶습니다).

MySQL 8부터 MySQL 데이터베이스 엔진은 로컬 설치 구성이 재정의하지 않는 한 기본적으로 이진 로깅을 활성화합니다. 바이너리 로그는 수정 사항을 설명하는 저장된 이벤트의 형태로 데이터베이스 내용을 수정하는 모든 SQL 문을 추적합니다. 이러한 로그는 특정 시점 데이터 복구 중에 데이터베이스 복제본을 동기화 상태로 유지하기 위해 데이터베이스 복제에 사용됩니다.

바이너리 로깅이 활성화되면 MySQL은 복제된 환경에서 데이터 안전과 무결성을 보장하기 위한 예방책으로 트리거 및 저장 프로시저 생성을 허용하지 않습니다. 트리거와 저장 프로시저가 복제에 미치는 영향을 이해하는 것은 이 가이드의 범위를 벗어납니다.

그러나 로컬 환경에서 학습 목적으로 MySQL이 트리거 생성을 방지하는 방식을 안전하게 재정의할 수 있습니다. 재정의된 설정은 유지되지 않으며 MySQL 서버를 다시 시작하면 원래 값으로 돌아갑니다.

바이너리 로깅의 기본 설정을 재정의하려면 루트로 MySQL에 로그인하고 다음 명령을 실행합니다.

SET GLOBAL log_bin_trust_function_creators = 1;

log_bin_trust_function_creators 설정은 트리거 및 저장된 함수를 생성하는 사용자가 안전하지 않은 이벤트가 바이너리 로그에 기록되도록 하는 트리거를 생성하지 않도록 신뢰할 수 있는지 여부를 제어합니다. 기본적으로 설정 값은 0이며 수퍼유저만 바이너리 로깅이 활성화된 환경에서 트리거를 만들 수 있습니다. 값을 1로 변경하면 CREATE TRIGGER 문을 실행하는 모든 사용자가 의미를 이해하도록 신뢰됩니다.

설정을 업데이트한 후 루트로 로그아웃하고 사용자로 다시 로그인한 다음 CREATE TRIGGER 문을 다시 실행합니다.

MySQL의 바이너리 로깅 및 복제에 대해 자세히 알아보고 트리거와 어떻게 관련되는지 알아보려면 공식 MySQL 문서인 MySQL에서 복제를 설정하는 방법을 참조하는 것이 좋습니다.

복제 또는 엄격한 지정 시간 복구 요구 사항이 있는 프로덕션 환경에서 트리거를 사용하기 전에 바이너리 로그 일관성에 미치는 영향을 평가했는지 확인하십시오.

참고: MySQL 사용자 권한에 따라 CREATE TRIGGER 명령을 실행할 때 오류가 발생할 수 있습니다. 코드>. 사용자에게 TRIGGER 권한을 부여하려면 MySQL에 루트로 로그인하고 다음 명령을 실행하여 필요에 따라 MySQL 사용자 이름과 호스트를 바꿉니다.

GRANT TRIGGER on *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;

사용자 권한을 업데이트한 후 루트로 로그아웃했다가 사용자로 다시 로그인하고 CREATE TRIGGER 문을 다시 실행합니다.

MySQL은 트리거가 성공적으로 생성되었음을 확인하기 위해 다음 메시지를 인쇄합니다.

Output
Query OK, 1 row affected (0.009 sec)

이제 INSERT 쿼리에 대한 소문자 인수를 다시 사용하여 새 수집품을 삽입해 봅니다.

INSERT INTO collectibles VALUES ('aircraft model', 10.00);

그리고 다시 한 번 collectibles 테이블에서 결과 행을 확인합니다.

SELECT * FROM collectibles;

다음 출력이 화면에 인쇄됩니다.

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| spaceship model | 12.50 |
| AIRCRAFT MODEL  | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

그러나 이번에는 삽입하려는 항목과 다른 모든 문자가 대문자인 AIRCRAFT MODEL이라는 새 항목이 표시됩니다. 트리거는 백그라운드에서 실행되었으며 행이 데이터베이스에 저장되기 전에 대소문자를 변환했습니다.

이름이 대문자로 저장되도록 이제 모든 새 행이 트리거에 의해 보호됩니다. 그러나 UPDATE 문을 사용하여 무제한 데이터를 저장할 수 있습니다. 동일한 효과로 UPDATE 문을 보호하려면 다른 트리거를 만듭니다.

CREATE TRIGGER uppercase_before_update BEFORE UPDATE
ON collectibles
FOR EACH ROW
SET NEW.name = UPPER(NEW.name);

두 트리거의 차이점은 트리거 기준에 있습니다. 이번에는 BEFORE UPDATE입니다. 즉, 테이블에서 UPDATE 문이 실행될 때마다 트리거가 실행되어 모든 업데이트에서 기존 행에 영향을 줍니다. , 이전 트리거에서 다루는 새 행 외에.

MySQL은 트리거가 성공적으로 생성되었다는 확인 메시지를 출력합니다.

Output
Query OK, 0 row affected (0.009 sec)

새 트리거의 동작을 확인하려면 우주선 모델의 가격 값을 업데이트해 보세요.

UPDATE collectibles SET value = 15.00 WHERE name = 'spaceship model';

WHERE 절은 업데이트할 행을 이름으로 필터링하고 SET 절은 값을 15.00으로 변경합니다.

명령문이 단일 행을 변경했음을 확인하는 다음 출력을 받게 됩니다.

Output
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

collectibles 테이블에서 결과 행을 확인합니다.

SELECT * FROM collectibles;

다음 출력이 화면에 인쇄됩니다.

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
| AIRCRAFT MODEL  | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

이제 실행된 명령문에 의해 가격이 15.00로 업데이트되었을 뿐만 아니라 이제 이름에 SPACESHIP MODEL이 표시됩니다. UPDATE 문을 실행하면 트리거가 실행되어 업데이트된 행의 값에 영향을 줍니다. 저장하기 전에 이름 열이 대문자로 변환되었습니다.

이 섹션에서는 INSERT 이전과 UPDATE 쿼리 이전에 작동하는 두 개의 트리거를 생성하여 데이터베이스에 저장하기 전에 데이터를 확인했습니다. 다음 섹션에서는 BEFORE DELETE 트리거를 사용하여 삭제된 행을 별도의 테이블에 복사하여 보관합니다.

BEFORE DELETE 트리거를 사용하여 행을 삭제하기 전에 작업 실행

더 이상 항목을 소유하지 않더라도 삭제에 대한 항목을 별도의 테이블에 남겨둘 수 있습니다. 이 자습서의 시작 부분에서 컬렉션에서 제거된 모든 수집품을 추적하기 위해 collectibles_archive라는 두 번째 테이블을 만들었습니다. 이 섹션에서는 삭제된 항목을 DELETE 문 전에 실행되는 트리거와 함께 보관합니다.

다음 명령문을 실행하여 아카이브 테이블이 완전히 비어 있는지 확인하십시오.

SELECT * FROM collectibles_archive;

다음 출력이 화면에 인쇄되어 collectibles_archive 테이블이 비어 있음을 확인합니다.

Output
Empty set (0.000 sec)

이제 collectibles 테이블에 대해 DELETE 쿼리를 실행하면 테이블의 모든 행이 추적 없이 삭제될 수 있습니다.

이를 해결하기 위해 collectibles 테이블의 모든 DELETE 쿼리 전에 실행할 트리거를 생성합니다. 이 트리거의 목적은 삭제가 발생하기 전에 삭제된 개체의 복사본을 아카이브 테이블에 저장하는 것입니다.

다음 명령을 실행합니다.

CREATE TRIGGER archive_before_delete BEFORE DELETE
ON collectibles
FOR EACH ROW
INSERT INTO collectibles_archive (name, value) VALUES (OLD.name, OLD.value);

트리거 이름은 archive_before_delete이며 collectibles 테이블에 대한 DELETE 쿼리 BEFORE에 발생합니다. . 삭제할 각 행에 대해 INSERT 문이 실행됩니다. 차례로 INSERT 문은 OLD 레코드에서 가져온 데이터 값이 있는 collectibles_archive 테이블에 새 행을 삽입합니다. 삭제: OLD.name은 name 열이 되고 OLD.value는 value 열이 됩니다.

데이터베이스는 트리거 생성을 확인합니다.

Output
Query OK, 0 row affected (0.009 sec)

트리거가 있는 상태에서 기본 수집품 테이블에서 수집품을 삭제해 보십시오.

DELETE FROM collectibles WHERE name = 'SPACESHIP MODEL';

출력은 쿼리가 성공적으로 실행되었음을 확인합니다.

Output
Query OK, 1 row affected (0.004 sec)

이제 모든 수집품을 나열하십시오.

SELECT * FROM collectibles;

다음 출력이 화면에 인쇄됩니다.

Output
+----------------+-------+
| name           | value |
+----------------+-------+
| AIRCRAFT MODEL | 10.00 |
+----------------+-------+
1 row in set (0.000 sec)

이제 AIRCRAFT MODEL만 남습니다. SPACESHIP MODEL이(가) 삭제되어 더 이상 테이블에 없습니다. 그러나 이전에 생성된 트리거를 사용하여 이 삭제는 collectibles_archive 테이블에 등록되어야 합니다. 확인해 봅시다.

다른 쿼리를 실행합니다.

SELECT * FROM collectibles_archive;

다음 출력이 화면에 인쇄됩니다.

Output
+-----------------+-------+---------------------+
| name            | value | removed_on          |
+-----------------+-------+---------------------+
| SPACESHIP MODEL | 15.00 | 2022-11-20 11:32:01 |
+-----------------+-------+---------------------+
1 row in set (0.000 sec)

삭제는 트리거에 의해 해당 테이블에 자동으로 기록되었습니다. name 및 value 열은 삭제된 행의 데이터로 채워졌습니다. 세 번째 열인 removed_on은 정의된 트리거를 통해 명시적으로 설정되지 않았으므로 테이블 생성 중에 결정된 기본값인 새 행이 생성된 날짜를 사용합니다. 그 때문에 트리거의 도움으로 추가된 모든 항목에는 항상 삭제 날짜가 주석으로 표시됩니다.

이 트리거를 사용하면 이제 모든 DELETE 쿼리가 collectibles_archive에 로그 항목을 생성하여 이전에 소유한 수집품에 대한 정보를 남길 수 있습니다.

다음 섹션에서는 트리거 문 다음에 실행되는 트리거를 사용하여 모든 수집품을 기반으로 집계된 값으로 요약 테이블을 업데이트합니다.

AFTER INSERT, AFTER UPDATE 및 AFTER DELETE 트리거를 사용하여 데이터 조작 후 작업 실행

앞의 두 섹션에서 데이터베이스를 업데이트하기 전에 원래 데이터를 기반으로 작업을 수행하기 위해 기본 문 전에 실행된 트리거를 사용했습니다. 이 섹션에서는 의도한 명령문 이후에 실행되는 트리거를 사용하여 모든 수집품의 누적 가치와 항상 최신 개수로 요약 테이블을 업데이트합니다. 이렇게 하면 요약 테이블 데이터가 데이터베이스의 현재 상태를 고려하는지 확인할 수 있습니다.

먼저 collectibles_stats 테이블을 검사합니다.

SELECT * FROM collectibles_stats;

아직 이 테이블에 정보를 추가하지 않았으므로 소유한 수집 가능한 항목의 수는 0이므로 누적 값은 NULL입니다.

Output
+-------+-------+
| count | value |
+-------+-------+
|     0 |  NULL |
+-------+-------+
1 row in set (0.000 sec)

이 테이블에 대한 트리거가 없기 때문에 수집품을 삽입하고 업데이트하기 위해 이전에 발행된 쿼리가 이 테이블에 영향을 미치지 않았습니다.

목표는 collectibles_stats 테이블의 단일 행에 값을 설정하여 수집품의 수와 총 가치에 대한 최신 정보를 표시하는 것입니다. 모든 INSERT, UPDATE 또는 DELETE 작업 후에 테이블 내용이 업데이트되도록 하려고 합니다.

모두 해당 쿼리 이후에 실행되는 세 개의 개별 트리거를 생성하여 이를 수행할 수 있습니다. 먼저 AFTER INSERT 트리거를 만듭니다.

CREATE TRIGGER stats_after_insert AFTER INSERT
ON collectibles
FOR EACH ROW
UPDATE collectibles_stats
SET count = (
    SELECT COUNT(name) FROM collectibles
), value = (
    SELECT SUM(value) FROM collectibles
);

트리거 이름은 stats_after_insert이며 collectibles에 대한 INSERT 쿼리마다 AFTER를 실행합니다. 트리거 본문에서 UPDATE 문을 실행하는 테이블. UPDATE 쿼리는 collectibles_stats에 영향을 미치고 count 및 value 열을 중첩된 쿼리에서 반환된 값으로 설정합니다.

• SELECT COUNT(name) FROM collectibles는 수집품 수를 가져옵니다.
• SELECT SUM(value) FROM collectibles는 모든 수집품의 총 가치를 가져옵니다.

데이터베이스는 트리거 생성을 확인합니다.

Output
Query OK, 0 row affected (0.009 sec)

이제 이전에 삭제한 우주선 모델을 수집품 테이블에 다시 삽입하여 요약 테이블이 제대로 업데이트되는지 확인하십시오.

INSERT INTO collectibles VALUES ('spaceship model', 15.00);

데이터베이스는 다음과 같은 성공 메시지를 인쇄합니다.

Output
Query OK, 1 row affected (0.009 sec)

다음을 실행하여 소유한 모든 수집품을 나열할 수 있습니다.

SELECT * FROM collectibles;

다음 출력이 화면에 인쇄됩니다.

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| AIRCRAFT MODEL  | 10.00 |
| SPACESHIP MODEL | 15.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

총 25.00 가치가 있는 두 개의 수집 가능한 항목이 있습니다. 새로 삽입된 항목 다음에 요약 테이블을 검사하려면 다음 쿼리를 실행합니다.

SELECT * FROM collectibles_stats;

이번에는 요약 테이블에 소유한 모든 수집 가능한 항목의 수가 2로, 누적 값이 25.00으로 나열되어 이전 출력과 일치합니다.

Output
+-------+-------+
| count | value |
+-------+-------+
|     2 | 25.00 |
+-------+-------+
1 row in set (0.000 sec)

stats_after_insert 트리거는 INSERT 쿼리 후에 실행되며 collectibles_stats 테이블을 현재 데이터(count 및 value) 컬렉션에 대해 설명합니다. 마지막 삽입뿐만 아니라 전체 컬렉션 콘텐츠에 대한 통계가 수집됩니다. 이제 컬렉션에 두 항목(항공기 및 우주선 모델)이 포함되어 있으므로 요약 테이블에는 두 항목과 해당 합계 값이 나열됩니다. 이 시점에서 수집품 테이블에 새 수집품 항목을 추가하면 요약 테이블이 올바른 값으로 업데이트됩니다.

그러나 기존 항목을 업데이트하거나 수집품을 삭제해도 요약에는 전혀 영향을 미치지 않습니다. 그 차이를 메우기 위해 동일한 작업을 수행하지만 다른 이벤트에 의해 트리거되는 두 개의 추가 트리거를 만듭니다.

CREATE TRIGGER stats_after_update AFTER UPDATE
ON collectibles
FOR EACH ROW
UPDATE collectibles_stats
SET count = (
    SELECT COUNT(name) FROM collectibles
), value = (
    SELECT SUM(value) FROM collectibles
);

CREATE TRIGGER stats_after_delete AFTER DELETE
ON collectibles
FOR EACH ROW
UPDATE collectibles_stats
SET count = (
    SELECT COUNT(name) FROM collectibles
), value = (
    SELECT SUM(value) FROM collectibles
);

이제 stats_after_update 및 stats_after_delete라는 두 개의 새 트리거를 만들었습니다. collectibles 테이블에서 UPDATE 또는 DELETE 문을 실행할 때마다 두 트리거 모두 collectible_stats 테이블에서 실행됩니다.

이러한 트리거가 성공적으로 생성되면 다음 출력이 인쇄됩니다.

Output
Query OK, 0 row affected (0.009 sec)

이제 수집품 중 하나의 가격 값을 업데이트합니다.

UPDATE collectibles SET value = 25.00 WHERE name = 'AIRCRAFT MODEL';

WHERE 절은 업데이트할 행을 이름으로 필터링하고 SET 절은 값을 25.00으로 변경합니다.

출력은 명령문이 단일 행을 변경했음을 확인합니다.

Output
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

다시 한 번 업데이트 후 요약 테이블의 내용을 확인하십시오.

SELECT * FROM collectibles_stats;

value는 이제 업데이트 후 올바른 값인 40.00을 나열합니다.

Output
+-------+-------+
| count | value |
+-------+-------+
|     2 | 40.00 |
+-------+-------+
1 row in set (0.000 sec)

마지막 단계는 요약 테이블이 수집품 삭제를 제대로 반영하는지 확인하는 것입니다. 다음 문을 사용하여 항공기 모델을 삭제해 보십시오.

DELETE FROM collectibles WHERE name = 'AIRCRAFT MODEL';

다음 출력은 쿼리가 성공적으로 실행되었음을 확인합니다.

Output
Query OK, 1 row affected (0.004 sec)

이제 모든 수집품을 나열하십시오.

SELECT * FROM collectibles;

다음 출력이 화면에 인쇄됩니다.

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
+-----------------+-------+
1 row in set (0.000 sec)

이제 SPACESHIP MODEL만 남습니다. 다음으로 요약 테이블에서 값을 확인합니다.

SELECT * FROM collectibles_stats;

다음 출력이 인쇄됩니다.

Output
+-------+-------+
| count | value |
+-------+-------+
|     1 | 15.00 |
+-------+-------+
1 row in set (0.000 sec)

개수 열은 이제 기본 테이블에 하나의 수집품만 표시합니다. 총 값은 15.00이며 SPACESHIP MODEL의 값과 일치합니다.

이 세 가지 트리거는 INSERT, UPDATE 및 DELETE 쿼리 후에 공동으로 작동하여 요약 테이블을 수집품의 전체 목록과 동기화 상태로 유지합니다.

다음 섹션에서는 데이터베이스에서 기존 트리거를 조작하는 방법을 배웁니다.

트리거 나열 및 삭제

이전 섹션에서는 새 트리거를 만들었습니다. 트리거는 테이블과 마찬가지로 데이터베이스에 정의된 명명된 개체이므로 필요할 때 트리거를 나열하고 조작할 수도 있습니다.

모든 트리거를 나열하려면 SHOW TRIGGERS 문을 실행합니다.

SHOW TRIGGERS;

출력에는 이름을 포함한 모든 트리거, 시간이 있는 트리거 이벤트(BEFORE 또는 AFTER 명령문 실행), 트리거 본문의 일부인 명령문 및 기타 명령문이 포함됩니다. 트리거 정의에 대한 광범위한 세부 정보:

Output, simplified for readability
+-------------------------+--------+--------------+--------(...)+--------+(...)
| Trigger                 | Event  | Table        | Statement   | Timing |(...)
+-------------------------+--------+--------------+--------(...)+--------+(...)
| uppercase_before_insert | INSERT | collectibles | SET    (...)| BEFORE |(...)
| stats_after_insert      | INSERT | collectibles | UPDATE (...)| AFTER  |(...)
| uppercase_before_update | UPDATE | collectibles | SET    (...)| BEFORE |(...)
| stats_after_update      | UPDATE | collectibles | UPDATE (...)| AFTER  |(...)
| archive_before_delete   | DELETE | collectibles | INSERT (...)| BEFORE |(...)
| stats_after_delete      | DELETE | collectibles | UPDATE (...)| AFTER  |(...)
+-------------------------+--------+--------------+--------(...)+--------+(...)
6 rows in set (0.001 sec)

기존 트리거를 삭제하려면 DROP TRIGGER SQL 문을 사용할 수 있습니다. 더 이상 수집 가능한 이름에 대문자를 적용하고 싶지 않을 수 있으므로 uppercase_before_insert 및 uppercase_before_update가 더 이상 필요하지 않습니다. 다음 명령을 실행하여 이 두 트리거를 제거합니다.

DROP TRIGGER uppercase_before_insert;
DROP TRIGGER uppercase_before_update;

두 명령 모두에 대해 MySQL은 성공 메시지로 응답합니다.

Output
Query OK, 0 rows affected (0.004 sec)

이제 두 개의 트리거가 사라진 상태에서 소문자로 된 새 수집품을 추가해 보겠습니다.

INSERT INTO collectibles VALUES ('ship model', 10.00);

데이터베이스에서 삽입을 확인합니다.

Output
Query OK, 1 row affected (0.009 sec)

SELECT 쿼리를 실행하여 행이 삽입되었는지 확인할 수 있습니다.

SELECT * FROM collectibles;

다음 출력이 화면에 인쇄됩니다.

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
| ship model      | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

새로 추가된 소장품은 소문자입니다. 원래 출력에서 이름이 변경되지 않았으므로 이전에 대소문자를 변환한 트리거가 더 이상 사용되지 않음을 확인했습니다.

이제 이름별로 트리거를 나열하고 삭제하는 방법을 알게 되었습니다.

결론

이 가이드를 따라 SQL 트리거가 무엇이고 MySQL에서 이를 사용하여 INSERT 및 UPDATE 쿼리 전에 데이터를 조작하는 방법을 배웠습니다. BEFORE DELETE 트리거를 사용하여 삭제된 행을 별도의 테이블에 보관하는 방법과 AFTER 문 트리거를 사용하여 요약을 지속적으로 최신 상태로 유지하는 방법을 배웠습니다.

함수를 사용하여 일부 데이터 조작 및 유효성 검사를 데이터베이스 엔진으로 오프로드하여 데이터 무결성을 보장하거나 일일 데이터베이스 사용자로부터 일부 데이터베이스 동작을 숨길 수 있습니다. 이 자습서에서는 해당 용도로 트리거를 사용하는 기본 사항만 다루었습니다. 여러 문으로 구성된 복잡한 트리거를 구축하고 조건 논리를 사용하여 작업을 훨씬 더 세분화하여 수행할 수 있습니다. 이에 대한 자세한 내용은 트리거에 대한 MySQL 설명서를 참조하십시오.

SQL 언어와 작업에 대한 다양한 개념에 대해 자세히 알아보려면 SQL 사용 방법 시리즈의 다른 가이드를 확인하는 것이 좋습니다.