혼자 공부하는 SQL 6주차

6주차 미션

- market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거 작성하고 인증샷

 

트리거는 INSERT, UPDATE, DELETE 문이 작동할 때 자동으로 실행되는 프로그래밍 기능입니다. 트리거를 활용하면 데이터가 살제될 때 해당 데이터를 다른 곳에 자동으로 백업할 수 있습니다. 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해주죠.

 

예를 들어, 회사원이 퇴사를 하면 직원 테이블에 퇴사한 직원의 정보를 삭제하면 됩니다. 즉, 퇴사한 직원의 데이터를 DELETE 문으로 지우게 되죠. 그런데 나중에 퇴사한 직원이 회사에 다녔던 기록을 요청할 수도 있습니다. 어떻게 퇴사한 직원 정보를 알 수 있을까요? 원칙적으로 그 직원의 정보는 데이터베이스에 존재하지 않기 때문에 알 수 있는 방도가 없습니다.

 

하지만 트리거를 사용하면 이를 방지할 수 있습니다!

 

그 직원의 정보를 삭제하기 전에 그 데이터를 다른 곳에 복사해 놓으면 됩니다. 바로 퇴사한 직원 테이블에요. 트리거를 사용해 자동으로 저장할 수 있습니다. 이것이 트리거의 대표적인 용도라고 할 수 있습니다.

 

 

트리거의 활용

 

그렇다면 실제 예제를 통해 트리거를 제대로 살펴보겠습니다.

 

market_db의 member 테이블에 입력된 회원 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거를 작성해보겠습니다.

 

singer 테이블로 복사해서 진행하겠습니다.

 

CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);

 

  위의 쿼리를 쓰면 백업테이블을 미리 생성해놓을 수 있습니다. 저는 member 테이블에서 INSERT나 UPDATE 작업이 일어날경우, 변경되기 전의 데이터를 저장할 백업 테이블인 singer 테이블을 생성했습니다.

 

백업 테이블에는 추가로 수정인지 삭제인지 구분할 변경된 타입, 변경된 날짜, 변경한 사용자를 아래의 쿼리를 사용해 추가했습니다. 

 

CREATE TABLE backup_singer
( mem_id CHAR(8) NOT NULL,
mem_name VARCHAR(10) NOT NULL,
mem_number INT NOT NULL,
addr CHAR(2) NOT NULL,
modType CHAR(2), -- 변경된 타입, '수정' 또는 '삭제'
modDate DATE, -- 변경된 날짜
modUser VARCHAR(30) -- 변경한 사용자
);

 

자, 이제 UPDATE가 발생했을 때 작동하는 singer_uqdateTrg 트리거를 만들어보겠습니다.

 

DELIMITER $$
CREATE TRIGGER singer_uqdateTrg -- 트리거 이름
	AFTER UPDATE -- 변경 후에 작동하도록 지정
    ON singer -- 트리거 부착할 테이블
    FOR EACH ROW
BEGIN
	INSERT INTO backup_singer VALUES(OLD.mem_id, OLD.mem_name,
		OLD.mem_number, OLD.addr, '수정', CURDATE(), CURRENT_USER());
END $$
DELIMITER ;

 

위의 쿼리에서 볼 수 있듯이 OLD 테이블은 UPDATE가 수행될 때, 변경되기 전의 데이터가 잠깐 저장되는 임시 테이블입니다.  OLD 테이블에 UPDATE 문이 작동되면 업데이트되기 전의 데이터가 백업 테이블인 backup_singer에 저장됩니다. 즉, 원래 데이터가 보존되는 것이죠.

 

CURDATE()와 CURRENT_USER()는 각각 현재 날짜, 현재 작업중인 사용자를 알려줍니다.

 

이번에는 아래의 쿼리를 사용하여 DELETE가 발생했을 때 작동하는 singer_deleteTrg 트리거를 생성해보겠습니다.

 

DELIMITER $$
CREATE TRIGGER singer_deleteTrg -- 트리거 이름
	AFTER DELETE -- 삭제 후에 작동하도록 지정
    ON singer -- 트리거 부착할 테이블
    FOR EACH ROW
BEGIN
	INSERT INTO backup_singer VALUES(OLD.mem_id, OLD.mem_name,
		OLD.mem_number, OLD.addr, '삭제', CURDATE(), CURRENT_USER());
END $$
DELIMITER ;

 

이제 트리거가 제대로 작동하는지 확인하기 위해 데이터를 변경해보겠습니다. 아래의 쿼리를 이용해 한 건의 데이터를 업데이트하고, 여러 건을 삭제해봤습니다.

 

UPDATE singer SET addr='영국' WHERE mem_id='BLK';
DELETE FROM singer WHERE mem_number>=7;

 

---

 

하지만 어찌된 영문인지 다음과 같은 오류메시지가 떴습니다.

 

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

 

이 메시지는 키값을 이용해 UPDATE나 DELETE만을 허용하도록 되어 있는데, 그렇지 않을 때 workbench에서 경고를 주는 것입니다. 즉, 하나의 레코드만을 UPDATE, DELETE하도록 설정되어 있는데, 다수의 레코드를 UPDATE나 DELETE할 때 발생을 하는 것입니다.

 

이것은 안전을 위해 MySQL에서 기본값으로 설정해 둔 것인데 저희는 실습을 진행하려면 이 안전 모드를 풀어야 합니다.

 

다음과 같은 쿼리로 간단하게 해결할 수 있습니다.

 

SET SQL_SAFE_UPDATES=0; -- 일시적 Safe 모드 해제

 

---

 

이제 다시 아래의 쿼리를 실행해보겠습니다.

 

UPDATE singer SET addr='영국' WHERE mem_id='BLK';
DELETE FROM singer WHERE mem_number>=7;

 

그러고 난 후, 백업 테이블을 조회해보면 1건이 수정되고 4건이 삭제된 것을 확인할 수 있습니다.

 

결과 화면

 

그렇다면 singer 테이블은 잘 수정이 되고 삭제가 되었을까요?

 

결과 화면

위의 그림에서 볼 수 있듯이 주황색이 수정, 삭제되기 전 원래 signer 테이블인데 빨간색처럼 addr가 영국으로 수정되고, mem_number가 7 이상인 데이터는 삭제가 잘 된 것을 확인할 수 있습니다.

 

그리고 안전을 위해 아래의 쿼리를 이용해 다시 Safe 모드를 설정해줍니다.

 

SET SQL_SAFE_UPDATES=1;

 

오늘의 실습 끝~!