혼공스 21일차(트리거)

트리거

• 트리거는 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해줍니다.
• 트리거를 사용하면 데이터에 오류가 발생하는 것을 막을 수 있습니다.
• 위를 데이터의 무결성이라고 부르기도 합니다.
• 트리거는 사전적의미로 방아쇠를 뜻합니다.
  (총의 방아쇠를 당기면 자동으로 총알이 나가는것처럼
  트리거는 테이블에 무슨 일이 일어나면 자동으로 실행됩니다.)

 

트리거 기본

• 트리거란 테이블에 INSERT, UPDATE,DELETE 작업이 발생하면 실행되는 코드입니다.
• 트리거는 테이블에서 DML문의 이벤트가 발생할 때 작동합니다.
• 테이블에 미리 부탁 되는 프로그램 코드라고 생각하면 됩니다.
• 전에 했던 스토어드 프로시저와 달리 트리거는 IN, OUT 매개변수를 사용할수 없습니다.)

 

트리거 기본 작동

 

1. 간단한 테이블을 만들어 줍니다.

USE market_db; 
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, '블랙핑크');

 

2. 위의 테이블에 트리거를 부착 합니다. (트리거 이름은 myTrigger 입니다.)

DELIMITER $$
CREATE TRIGGER myTrigger
	AFTER DELETE -- 트리거는 DELETE문이 발생한 이후에 작동하라는 의미 
    ON trigger_table
    FOR EACH ROW
BEGIN 
	SET @msg = '가수 그룹이 삭제됨' ; -- 트리거에서 실제 작동하는 부분 
END $$ 
DELIMITER ;

 

 

3. 그 후 DELETE문이 발생한 이후 트리거가 실행 되므로 DELETE문을 실행시켜줍니다.

DELETE FROM trigger_table WHERE id = 1;
SELECT @msg;

 

 

 

트리거 활용

• 트리거는 테이블에 입력,수정,삭제 되는 정보를 백업하는 용도로도 사용이 가능합니다.

 

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

 

1. 먼저 회원 테이블의 열을 일부분만 복사하여 진행하겠습니다.

(테이블의 이름은 singer 로 하겠습니다.)

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

 

2. INSERT, UPDATE 작업이 일어나면 변경전의 데이터를 저장하는 백업 테이블을 생성합니다.

(수정 또는 삭제인지 구분할 변경된 타입, 변경된 날짜, 변경한 사용자를 추가해주었습니다.)

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

 

3. 변경이 발생했을때 작동하는 signer_updateTrg 트리거를 만들어 줍니다.

(OLD 테이블은 UPDATE 또는 DELETE가 수행될때 변경되기 전의 데이터가 잠깐 저장되는 임시 테이블 입니다.)

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

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 ;

 

4. 삭제가 발생했을때 작동하는 signer_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 ;

 

 

5. 데이터를 업데이트도 해보고 삭제도 해보겠습니다.

 

여기서 주의 할점으로
DELETE 대신에 TRUNCATE TABLE 문으로 삭제 할경우 트리거가 작동하지 않으니 주의 하셔야 합니다.

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

SELECT * FROM backup_singer;

 

 

 

알아두기

• 테이블에 INSERT, UPDATE, DELETE 작업이 수행됨 임시로 사용되는 시스템 테이블 2개가 있습니다.
• 그 두개의 이름은 NEW 와 OLD 입니다.
• 트리거의 기존 데이터는 OLD 테이블에 , 새로운 데이터는 NEW 테이블에 잠깐 저장됩니다.