SQL - 트리거

트리거

트리거는 총의 방아쇠를 당기면 자동으로 총알이 나가듯이 INSERT, UPDATE, DELETE 문이 작동할 때 실행되는 기능으로 데이터를 삭제할 때 해당 데이터를 다른 테이블에 백업한 후에 삭제하는 기능을 사용할 수 있다.

이러한 트리거 기능은 백업하지 않고 바로 지워버리는 등의 실수를 방지할 수 있고 데이터의 오류가 발생되는 것을 막을 수 있다. 이런 방법으로 데이터의 무결성을 유지할 수 있다.

 

기본 작동

트리거는 DML의 이벤트가 발생할 때 작동한다. 테이블에 미리 부착되는 코드라 생각하면 된다.

트리거는 스토어드 프로시저와 문법이 비슷하지만 CALL문으로 호출할 수 없고 DML의 이벤트가 발생할 때만 자동으로 실행된다. 추가적으로 트리거에는 IN, OUT 매개 변수를 사용할 수 없다.

 

트리거 부착

AFTER ~ : 트리거가 특정 이벤트(INSERT, UPDATE, DELETE) 후에 실행될 수 있게 설정환다.

ON ~ : 트리거가 작동할 테이블을 설정한다.

FOR EACH ROW :  트리거가 작동할 때 각 행마다 적용한다.

*동일한 기능을 하는 구문을 사용해도 ALTER로 지정한 이벤트에서만 트리거가 작동한다.

CREATE TABLE IF NOT EXISTS trigger_table(
    id INT,
    txt VARCHAR(10)
);
INSERT INTO trigger_table VALUES(1, '레드벨벳'); 
INSERT INTO trigger_table VALUES(2, '잇지'); 
INSERT INTO trigger_table VALUES(3, '블랙핑크'); 
SELECT * FROM trigger_table;

-- TRIGGER --

DELIMITER $$
CREATE TRIGGER myTrigger
    AFTER DELETE
    ON trigger_table
    FOR EACH ROW
BEGIN
    SET @msg = '가수 그룹이 삭제됨';
END $$
DELIMITER ;

 

- 트리거의 이름을 myTrigger로 지정한다.

- AFTER DELETE로 DELETE문 이후에 작동하라는 구문을 작성한다.

- ON myTrigger로 트리거를 부착할 테이블을 지정한다.

- FOR EACH ROW는 각 행마다 적용시킨다는 의미이다.

 

OLD, NEW 테이블

OLD : 트리거가 실행되기 전의 컬럼 데이터를 참조한다. UPDATE, DELETE에 사용한다.

NEW : 트리거가 실행된 후의 컬럼 데이터를 참조한다. INSERT, UPDATE에 사용한다.

* UPDATE에 OLD를 사용하면 업데이트 전의 컬럼 데이터를 가져오고, NEW를 사용하면 업데이트 후의 데이터를 가져온다.

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

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)
);

 

- 일반적인 테이블과 백업 테이블을 생성한다. 백업 테이블은 변경타입, 시간, 유저 테이블을 추가적으로 생성한다.

 

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

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 ;

DELIMITER $$
CREATE TRIGGER singer_insertTrg
    AFTER INSERT
    ON singer
    FOR EACH ROW
BEGIN
    INSERT INTO backup_singer VALUES(NEW.mem_id, NEW.mem_name, NEW.mem_number, NEW.addr, '추가', CURDATE(), CURRENT_USER());
END $$
DELIMITER ;

 

- ALTER 명령어로 INSERT, UPDATE, DELETE시에 작동할 트리거를 작성한다.

- UPDATE와 DELETE에는 OLD테이블을 INSERT에는 NEW 테이블을 추가하는 구문을 작성한다.

 

INSERT INTO singer (SELECT mem_id, mem_name, mem_number, addr FROM member WHERE mem_id='BLK');
UPDATE singer SET addr='영국' WHERE mem_id='BLK';
DELETE FROM singer WHERE mem_number >= 7;

 

- 결과를 확인한다.