MySQL에서 트리거로 삽입·수정 작업을 막는 방법
MySQL에서 특정 조건을 만족하지 않는 데이터의 삽입(INSERT)이나 수정(UPDATE) 작업을 강제로 중단하려면 SIGNAL SQLSTATE 명령어를 사용해야 합니다. 이 명령어는 트리거 내부에서 오류를 발생시켜 해당 작업을 즉시 중단하고, 사용자에게 원하는 오류 메시지를 전달할 수 있게 해줍니다.
SIGNAL SQLSTATE 트리거 기본 문법
트리거를 작성할 때는 먼저 구분자(Delimiter)를 변경한 뒤, BEFORE INSERT 또는 BEFORE UPDATE 시점에 조건을 검사하도록 구성합니다.
DELIMITER //
CREATE TRIGGER yourTriggerName
BEFORE INSERT ON yourTableName
FOR EACH ROW
BEGIN
IF yourCondition THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'anyMessageToEndUser';
END IF;
END //
DELIMITER ;
여기서 SQLSTATE '45000'은 사용자가 직접 정의하는 일반적인 오류 상태 코드이며, MESSAGE_TEXT에는 최종 사용자에게 보여줄 오류 메시지를 지정합니다.
실전 예제: 허용 범위 밖의 값 입력 차단하기
이제 Id 값이 1~5 범위를 벗어나면 레코드 삽입을 막는 트리거를 만들어 보겠습니다. 먼저 테스트용 테이블을 생성합니다.
1. 테이블 생성
mysql> CREATE TABLE Insert_Prevent
-> (
-> Id int
-> );
Query OK, 0 rows affected (0.62 sec)
2. 삽입 방지 트리거 생성
mysql> DELIMITER //
mysql> CREATE TRIGGER InsertPreventTrigger BEFORE INSERT ON Insert_Prevent
-> FOR EACH ROW
-> BEGIN
-> IF(new.Id < 1 OR new.Id > 5) THEN
-> SIGNAL SQLSTATE '45000'
-> SET MESSAGE_TEXT = 'You cannot insert record';
-> END IF;
-> END //
Query OK, 0 rows affected (0.20 sec)
mysql> DELIMITER ;
이 트리거가 적용되면 Id 값이 1보다 작거나 5보다 큰 레코드는 모두 삽입이 거부됩니다.
3. 차단되는 경우 확인
허용 범위를 벗어나는 값을 삽입하면 트리거가 동작하여 다음과 같은 오류 메시지가 출력됩니다.
mysql> INSERT INTO Insert_Prevent VALUES(0);
ERROR 1644 (45000): You cannot insert record
mysql> INSERT INTO Insert_Prevent VALUES(6);
ERROR 1644 (45000): You cannot insert record
값 0과 6은 각각 범위(1~5)를 벗어났기 때문에 삽입이 성공적으로 차단된 것을 확인할 수 있습니다.
4. 정상적으로 삽입되는 경우
반대로 1부터 5 사이의 값을 입력하면 아무런 제약 없이 레코드가 저장됩니다.
mysql> INSERT INTO Insert_Prevent VALUES(1);
Query OK, 1 row affected (0.20 sec)
mysql> INSERT INTO Insert_Prevent VALUES(5);
Query OK, 1 row affected (0.17 sec)
mysql> INSERT INTO Insert_Prevent VALUES(2);
Query OK, 1 row affected (0.11 sec)
mysql> INSERT INTO Insert_Prevent VALUES(3);
Query OK, 1 row affected (0.23 sec)
5. 결과 조회
SELECT 문으로 테이블의 전체 레코드를 확인해 보겠습니다.
mysql> SELECT * FROM Insert_Prevent;
실행 결과는 다음과 같습니다.
+------+
| Id |
+------+
| 1 |
| 5 |
| 2 |
| 3 |
+------+
4 rows in set (0.00 sec)
정리
MySQL의 SIGNAL SQLSTATE 명령어를 트리거와 함께 사용하면 애플리케이션 코드를 수정하지 않고도 데이터베이스 수준에서 잘못된 데이터 입력을 손쉽게 차단할 수 있습니다. UPDATE 작업도 마찬가지로 BEFORE UPDATE 트리거에 동일한 방식을 적용하면 되므로, 데이터 무결성을 강화하는 데 매우 유용한 기법입니다.