Chapter 7. Trigger, Procedure, View

1. Trigger

 ⦁ trigger는 테이블에 insert, update, delete 등의 이벤트가 발생했을 때 전후로 자동으로 무언가를 실행시키는 것을 의미

 ⦁ insert는 주로 after, delete는 주로 before, update는 after, before 둘다에서 사용

 ⦁ 테이블내에서 trigger를 선택

트리거 설정 영역

 

 ⦁ 원하는 이벤트 발생 전후를 전택하여 +를 클릭하면 명령어를 작성할 수있는 페이지가 나옴

+를 통한 실행 영역 생성

 

 ⦁ old는 기존에 있던 것, new는 새로 생성되는 것을 의미

#user_mst_AFTER_INSERT
CREATE DEFINER=`root`@`localhost` TRIGGER `user_mst_AFTER_INSERT` AFTER INSERT ON `user_mst` FOR EACH ROW 
BEGIN
	insert into user_dtl(user_id)
	values (new.user_id);
END

#user_mst_BEFORE_DELETE
CREATE DEFINER=`root`@`localhost` TRIGGER `user_mst_BEFORE_DELETE` BEFORE DELETE ON `user_mst` FOR EACH ROW 
BEGIN
	delete
    from
		role_dtl
	where
		user_id = old.user_id;
        
	delete
    from
		user_dtl
	where
		user_id = old.user_id;
END

 

2. Procedure

 ⦁ procedure는 하나의 요청으로 여러 SQL문을 사용할 수 있는 방식

 ⦁ 자바에서 메소드와 같은 역할

 ⦁ 'show_all_user_mst'는 메소드명

 ⦁ 매개변수 사용 시 (_변수명 자료형)으로 입력함

 ⦁ begin, end는 메소드의 중괄호라고 생각하면됨

 ⦁ procedure 실행하기 위해 call을 사용

 ⦁ 변수 선언 시 declare를 사용

#procedure 실행문
CREATE DEFINER=`root`@`localhost` PROCEDURE `show_all_user_mst`(_MSG varchar(30))
BEGIN
	select
		_MSG as MESSAGE,
		user_id,
        username,
        password,
        name,
        email
	from
		user_mst;
END

#procedure를 실행할 때
call  show_all_user_mst('안녕하세요');

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_product`(_product_name varchar(45))
BEGIN
	#변수 선언
	declare new_key varchar(50);
    
    select
		concat('prm', lpad(count(*) + 1, '6', '0')) into new_key
	from
		product_mst;
	
    insert into product_mst
    values (new_key, _product_name);
END

 

3. View

 ⦁ view는 데이터베이스에 존재하는 가상 테이블

 ⦁ 실제 테이블처럼 행과 열을 가지고 있지만, 실제로 데이터를 저장하진 않음

 ⦁ 여러 테이블을 확인하거나 여러 select문을 사용해야할 때의 번거로움을 줄여줌

#view 생성
create view library_view as
select
	#row_number()는 아래부터 숫자를 줌
    #partition by는 이름이 같은 그룹내에서 순번을 줌
    #order by로 중복을 없애고 순번을 줌
	row_number() over(/*partition by 도서관명 */order by 도서관명) as 도서관순번,
	도서관명
from
	library_mst
group by 
    도서관명;