(DB) 트리거(Trigger)

정의 

- 테이블에 삽입, 수정, 삭제 등의 작업(이벤트)이 발생할 때에 자동으로 작동하는 데이터 베이스 개체

 

 

특징 

-프로시저와 달리 직접 실행시킬 수 없고 오직 해당 테이블에 이벤트가 발생할 경우에만 실행 (매개변수는 사용불가)
-이벤트발생 한 후 ROLLBACK 할 수 없다.

 

 

형식

DELIMITER $$

CREATE TRIGGER 트리거 이름 트리거타임 트리거이벤트 ON 테이블이름 FOR EACH ROW 
BEGIN
	
	트리거 관련 명령문

END $$
DELIMITER;

트리거 타임: BEFORE / AFTER
트리거 이벤트: INSERT / UPDATE / DELETE

 

 

실습

실습1. 행 삭제 시 msg 나오는 트리거

트리거 작성

트리거 발동

SET @msg:='';	/*변수 초기화*/

DELETE
FROM
   tb_member_trigger
WHERE
   m_id = 'id015';

SELECT @msg;

 

 

실습2. data 수정,삭제 시 backup_table에 내역 남는 after 트리거

/* backup 테이블 생성 */
CREATE TABLE `tb_member_trigger_backup` (
`m_id` VARCHAR(200) NOT NULL,
`m_pw` VARCHAR(200) NOT NULL,
`m_name` VARCHAR(200) NOT NULL,
`m_level` INT(11) NULL DEFAULT NULL,
`m_email` VARCHAR(200) NOT NULL,
`m_addr` VARCHAR(200) NOT NULL,
`m_reg_date` DATE NOT NULL,
`m_modify_type` VARCHAR(50) NULL DEFAULT NULL,
`m_modify_date` DATE NULL DEFAULT NULL,
`m_modify_user` VARCHAR(200) NULL DEFAULT NULL
)

/* 트리거 작성 */
/* after - update */
BEGIN
INSERT INTO tb_member_trigger_backup
(  m_id , m_pw , m_name , m_level , m_email
 , m_addr , m_reg_date , m_modify_type , m_modify_date , m_modify_user
) VALUES (
   OLD.m_id , OLD.m_pw , OLD.m_name , OLD.m_level , OLD.m_email
 , OLD.m_addr , OLD.m_reg_date , '수정' , CURDATE() , CURRENT_USER());
END

/* after - delete */
BEGIN
INSERT INTO tb_member_trigger_backup
(  m_id , m_pw , m_name , m_level , m_email
 , m_addr , m_reg_date , m_modify_type , m_modify_date , m_modify_user
) VALUES (
   OLD.m_id , OLD.m_pw , OLD.m_name , OLD.m_level , OLD.m_email
 , OLD.m_addr , OLD.m_reg_date , '수정' , CURDATE() , CURRENT_USER());
END

 

 

실습3. 등급을 문자로 입력해도 숫자로 db에 저장되게 하는 before-insert 트리거

/* 트리거 작성 */
BEGIN
CASE
WHEN NEW.m_level = '관리자' THEN SET NEW.m_level = '1';
WHEN NEW.m_level = '판매자' THEN SET NEW.m_level = '2';
ELSE SET NEW.m_level = '3';
END CASE;
END

/* data insert로 트리거 발동*/
INSERT INTO tb_member_trigger
	(m_id, m_pw, m_name, m_level, m_email, m_addr, m_reg_date)
VALUES
('id014', 'pw014', '홍14', '판매자', '홍14@ksmart.or.kr', '전라북도 전주시 덕진구 금암동', NOW());

결과