[혼공SQL] 트리거

   트리거

트리거는 테이블에서 DML문(INSERT, UPDATE, DELETE 등)의 이벤트가 발생할 때 자동으로 작동하는 기능을 말한다. 이는 테이블에 미리 부착되어 있다.

 

트리거는 어떤 DB에서 데이터를 삭제할 때, 해당 데이터를 완전히 삭제하는 것이 아닌, 과거 내역을 저장하는 histroy_DB에 저장하는 등의 행동을 할 때, 사람이 직접 하게 되면 이를 놓치거나 잘못 입력하여 다르게 작동시킬 수 있다.

 

트리거는 한번 선언하여 테이블에 붙여둠으로써 이를 해결하고 자동화하여 편리함을 제공한다.

 

   트리거의 사용

트리거는 아래의 SQL문과 같이 사용이 가능하다. 스토어드 프로시저와 비슷하지만 조금 다른 부분이 존재하며, 아래와 같은 형식이 쓰이니 익혀두는 것을 추천한다.

DELIMITER $$
CREATE TRIGGER myTrigger	-- 트리거 이름 설정 및 생성
	AFTER DELETE		-- DELETE문이 끝나고 실행되도록 세팅
    ON trigger_table	-- trigger_table에 부착
    FOR EACH ROW		-- trigger에서 필수로 붙여지는 키워드
BEGIN
	SET @msg = '데이터가 삭제됨';	-- 트리거가 할 행동
END $$
DELIMITER ;

 

 

이렇게 되면 DELETE문이 발생할 때 myTrigger가 작동된다. 즉, 다른 DML인 INSERT나 UPDATE에는 반응하지 않는다. 또한 AFTER ~ 키워드 외에 BEFORE ~도 있지만, 잘 사용하지 않으므로 AFTER만 다룬다.

 

   트리거의 사용

위에서 사용한 예시는 아주 단순한 트리거의 선언이라고 봐도 무방하다. 앞전에 설명했던 어떤 데이터를 삭제 또는 수정할 때, 이 내용을 백업해두는 트리거를 선언하면서 트리거의 진짜 속을 파헤쳐보자.

 

우선 해당 예제를 진행하기 위해 필요한 테이블 2개를 선언하자. singer 테이블에 데이터를 삭제 또는 수정하여 backup_singer 테이블에 작성해줄 것이다.(예제용 테이블이므로 PK와 같은 제약조건은 생략한다.)

CREATE TABLE singer(
	mem_id CHAR(8),
    mem_name CHAR(10),
    mem_number INT,
    addr CHAR(2)
);

 

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), -- 변경된 타입. '수정' or '삭제'
    modDate 	DATE, -- 변경된 날짜
    modUser 	VARCHAR(3) -- 변경한 사용자
);

백업용 DB는 singer의 데이터를 그대로 가져오므로 singer와 같은 열은 NOT NULL 제약 조건이 있어야한다.

 

또한 삭제 또는 수정과 관련된 시간 등의 정보도 저장할 수 있게 되어있다.

 

 

이제 트리거를 만들어보자. 아래에 생성되는 트리거는 singer 테이블에 UPDATE를 하였을 때 작동되는 트리거이다.

DELIMITER $$
CREATE TRIGGER singerUpdateTrigger
	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라는 테이블이 존재한다. 이 테이블은 무엇일까? DELETE까지 트리거를 생성하고 다뤄본다.

 

삭제 트리거는 아래와 같다.

DELIMITER $$
CREATE TRIGGER singerDeleteTRigger
	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 ;

 

위 두 개의 트리거는 singer 테이블에서 DELETE와 UPDATE가 발생하였을 때 자동으로 backup_singer에 데이터를 채워줄 것이다. 이런 방식으로 트리거를 사용하는 것이다.

 

한가지 주의해야할 것은, DELETE가 아닌 TRUNCATE로는 트리거가 실행되지 않는다. 이 점 꼭 주의하자.

 

 

 

하지만 의문점이 들 수 있다. DELETE나 UPDATE에 사용하는 트리거는 AFTER이다. 즉, 데이터를 삭제하거나 수정한 후 이전의 값을 backup_singer 테이블에 저장한다는 뜻인데... 삭제되거나 수정되기 전 데이터는 어디에 저장하는 것일까?

 

바로 NEW와 OLD 테이블이다.

 

   트리거가 사용하는 임시테이블. NEW와 OLD

앞서 OLD 테이블을 보았다. 이 테이블은 무엇일까?

 

제목에서도 알 수 있듯이 트리거는 임시 테이블을 2개 사용한다. 그것이 바로 NEW와 OLD이다.

이 테이블에 대해서 알아보자.

 

  INSERT와 NEW

NEW는 INSERT가 발생할 때, INSERT되는 데이터를 임시로 저장하는 테이블이다. 

 

지금까지 우리는 INSERT를 하면 해당 데이터가 바로 DB에 들어간다고 생각하였지만 사실은 그렇지 않다. NEW 테이블에 데이터가 저장되었다가 실제 테이블에 저장되는 방식이다.

 

하지만 NEW 테이블은 잘 사용하지 않는다. 왜냐하면 우리가 INSERT하고자 하는 데이터는 어차피 우리가 넣고자 하는 테이블에 결국은 들어가있을테니까.

 

  DELETE와 OLD

트리거를 테이블에 등록할 때 우리는 AFTER를 사용했다. (BEFORE는 잘 쓰지 않는다고 한다) 그러면 어떻게 데이터를 삭제한 후에 backup 테이블에 저장할 수 있는걸까?

 

바로 OLD 테이블 덕분이다. 데이터가 만약 삭제된다면 삭제되는 데이터는 삭제되기 전 OLD 테이블에 저장된다. 그래서 우리가 DELETE시 OLD 테이블에 데이터를 꺼내서 사용하는 것이다.

 

  UPDATE와 NEW 그리고 OLD

UPDATE에서도 OLD를 사용한 것을 위의 예제에서 살펴보았다. 하지만 UPDATE는 NEW 테이블 또한 사용하게 된다.

 

UPDATE하기 위한 새로운 값은 NEW 테이블에 저장이 되고, 실제 테이블에서는 예전 값을 삭제하고 새 값으로 교체한다. 실제 테이블에서 예전 값이 삭제되기 전 OLD 테이블에 예전 값을 저장하는 방식으로 진행된다.