[SQL] 쿼리테스트 - 트리거

문제

다음 두 테이블이 있다.

CREATE TABLE item (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  quantity INT NOT NULL
);

CREATE TABLE item_archive (
  archive_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

item 테이블에서 행이 삭제(DELETE) 될 때마다, 삭제된 행의 name 값을 item_archive 테이블에 복사(insert)하는 트리거를 작성하라. 아래 코드의 빈칸을 채워 완성하라.

DELIMITER $$
CREATE [  blank  ] item_delete [  blank  ] ON item
FOR EACH ROW
BEGIN
  INSERT INTO item_archive(name) VALUES ( [  blank  ] );
END;
$$
DELIMITER ;

---

정답

빈칸에 들어갈 정답은 다음과 같다.

1. 첫 번째 blank: TRIGGER
2. 두 번째 blank: AFTER DELETE
3. 세 번째 blank: OLD.name

완성 코드는 다음과 같다.

DELIMITER $$
CREATE TRIGGER item_delete AFTER DELETE ON item
FOR EACH ROW
BEGIN
  INSERT INTO item_archive(name) VALUES (OLD.name);
END;
$$
DELIMITER ;

---

왜 정답이 위와 같은가? — 상세 해설

1) 트리거의 선언 형태와 위치

• 트리거를 만들 때 사용하는 키워드는 CREATE TRIGGER 이다. 따라서 첫 번째 빈칸은 TRIGGER 이어야 한다.
• 트리거는 언제(시점)와 어떤 이벤트(INSERT / UPDATE / DELETE)에 반응할지 지정해야 한다. 문제 요구사항은 삭제(DELETE)된 데이터의 name을 보관하는 것이므로 DELETE 이벤트에 반응해야 한다. 또한 삭제가 발생한 뒤(after) 해당 값을 아카이브에 넣으면 안전하므로 AFTER DELETE가 적절하다. (BEFORE DELETE도 사용 가능하나 아래에 차이 설명)
• 그래서 두 번째 빈칸은 AFTER DELETE 이다.

2) OLD vs NEW: 삭제 트리거에서 사용해야 할 값

• 트리거 내부에서 참조할 수 있는 특수 레코드가 있다: NEW와 OLD 이다.

  • INSERT 트리거에서는 새로 들어오는 행을 NEW.col로 읽을 수 있다.
  • UPDATE 트리거에서는 OLD.col(수정 전 값)과 NEW.col(수정 후 값) 둘 다 사용 가능하다.
  • DELETE 트리거에서는 삭제되는 행의 기존 값을 참조할 수 있는 것은 OLD.col 뿐이다. NEW는 존재하지 않는다.

• 문제는 삭제 시점에 name 값을 아카이브 테이블에 넣으라는 요구이므로 삭제되는 행의 name을 참조해야 한다. 따라서 OLD.name을 사용해야 한다. 이것이 세 번째 빈칸의 정답이다.

3) AFTER DELETE와 BEFORE DELETE의 차이

• BEFORE DELETE는 삭제가 실제로 수행되기 전에 실행된다. BEFORE에서 어떤 검증을 하고 삭제를 취소하는 방식(예외발생 등)을 구현할 수 있다.
• AFTER DELETE는 삭제가 완료된 후에 실행된다. 아카이브처럼 실제 삭제 행위를 기록(복사)하는 경우에는 AFTER가 더 직관적이며, 트랜잭션 관점에서도 동일 트랜잭션 내에서 작동한다(아래 참조).
• 실제로는 BEFORE에서도 OLD.name을 읽어 아카이브에 넣는 것이 가능하다. 다만 논리적으로 “삭제가 확정된 뒤 기록”이라는 의미로 AFTER를 더 많이 사용한다.

4) 트리거와 트랜잭션 관계

• MySQL의 트리거는 호출되는 SQL문과 동일한 트랜잭션 컨텍스트에서 실행된다. 즉, 만약 DELETE가 이후에 롤백되면 트리거의 INSERT도 롤백된다.
• 따라서 트리거로 아카이브를 쓸 때 삭제가 취소되면 아카이브도 취소된다는 점을 인지해야 한다. 영구 보관을 원하면 별도의 비동기 프로세스(예: 로그 테이블에 쓰고 별도로 flush) 설계를 고려해야 한다.

5) FOR EACH ROW의 의미

• FOR EACH ROW는 행 단위(row-level) 트리거임을 의미한다. DELETE 한 번으로 여러 행이 삭제될 경우 이 트리거는 삭제되는 각 행마다 한 번씩 실행된다.
• 따라서 DELETE FROM item WHERE ...로 N개 행이 삭제되면 트리거는 N번 실행되어 각 OLD.name 값을 아카이브에 삽입한다.

6) 사용 시 주의사항

• 트리거에서 COMMIT/ROLLBACK 불가: MySQL에서는 트리거 내부에서 트랜잭션 제어문(COMMIT/ROLLBACK)을 호출할 수 없다. 트리거는 호출 쿼리의 트랜잭션을 따라 간다.
• 성능 영향: 대량 삭제 시 트리거에서 아카이브 INSERT가 빈번히 발생하므로 성능 영향이 있을 수 있다. 특히 아카이브 테이블에 인덱스가 많으면 삽입 비용이 커진다. 배치 아카이브가 더 적절할 수 있다.
• 권한: 트리거를 생성하려면 적절한 권한이 필요하다(CREATE TRIGGER 권한).
• 컬럼 확장: 실제 아카이브에는 삭제 시각(deleted_at), 삭제자(deleted_by), 전체 덤프 등 추가 정보를 보관하는 것이 보통이다.
• 다중 컬럼 보존: 만약 여러 컬럼을 보존하고자 하면 INSERT INTO item_archive(name, quantity, ...) VALUES (OLD.name, OLD.quantity, ...); 처럼 확장하면 된다.

7) 예제 동작 시연

1. 원본 데이터 삽입:

INSERT INTO item (name, quantity) VALUES ('apple', 10), ('banana', 5);

2. 삭제 실행:

DELETE FROM item WHERE name = 'apple';

3. 아카이브 확인:

SELECT * FROM item_archive;
-- 'apple' 이 item_archive.name 으로 들어간 것을 확인

8) 트리거 정의 시 구문 주의 (클라이언트별)

• 많은 MySQL 클라이언트는 여러 문(statement)을 전송할 때 기본 구분자(;)를 사용하므로 프로시저/트리거 정의 시에는 DELIMITER를 변경해야 한다. 예:

DELIMITER $$
CREATE TRIGGER item_delete AFTER DELETE ON item
FOR EACH ROW
BEGIN
  INSERT INTO item_archive(name) VALUES (OLD.name);
END;
$$
DELIMITER ;

• GUI 툴(예: DBeaver, DataGrip)에서는 DELIMITER가 필요하지 않고 프로시저 블록 전체를 선택해 실행하면 되기도 한다. 환경에 따라 DELIMITER 처리법이 다르므로 주의해야 한다.

---

결론

• 빈칸 정답: CREATE TRIGGER item_delete AFTER DELETE ON item FOR EACH ROW ... VALUES (OLD.name); 이다.
• 삭제 트리거에서 삭제된 행의 값을 참조하려면 OLD.column을 사용해야 한다.
• AFTER DELETE는 삭제 확정 뒤 아카이브할 때 자연스럽고, 트리거는 호출 쿼리의 트랜잭션 범위를 따른다(롤백 시 함께 롤백).