Stored trigger (트리거)

트리거

 

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

2. 특징
- 프로시저와 달리 직저 실행시킬 수 없고 오직 해당 테이블에 이벤트가 발생할 경우에만 실행된다.
- 이벤트 발생 후 ROLLBACK 할 수 없다.

 

 

 

 

트리거 형식 정의

 

 

 

트리거 실습

만들고자 하는 테이블 우클릭 -> 새로생성 -> 트리거

 

실습 1

DELIMITER $$

CREATE TRIGGER tb_member_trigger_after_delete
AFTER DELETEON tb_member_trigger FOR EACH ROW 
BEGIN
	BEGIN
	SET @msg='DELETE 트리거 발동';
	END

END $$
DELIMITER;

 

실습 2

SET @msg:='';

DELETE
FROM
   tb_member_trigger
WHERE
   m_id = 'id015';

SELECT @msg;

 

실습 3. after 트리거 -> tb_member_trigger_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` VARCHAR(200) 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
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

 

실습 4. after update 트리거 -> tb_member_trigger_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

 

실습 5. after_delete 트리거 -> tb_member_trigger_after_delete

BEGIN
	set @msg := 'tb_member_trigger 테이블 delete';	
END

 

실습 6. after_update 트리거 -> tb_member_trigger_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