이것이MySQL이다 - 03) 스토어드 프로그램

DB/MySQL

이것이MySQL이다 - 03) 스토어드 프로그램

록's 2023. 2. 10. 12:38

728x90

트리거

트리거(Trigger)의 개요

트리거란?

사전적 의미로 ‘방아쇠’
방아쇠 당기면 ‘자동’으로 총알이 나가듯이 테이블에 무슨 일이 일어나면 ‘자동’으로 실행
제약 조건과 더불어 데이터 무결성을 위해 MySQL에서 사용할 수 있는 기능
테이블에 DML문(Insert, Update, Delete 등) 이벤트가 발생될 때 작동
테이블에 부착되는 프로그램 코드
직접 실행 불가
- 테이블에 이벤트 일어나야 자동 실행
IN, OUT 매개 변수를 사용할 수 없음
MySQL은 View에 트리거 부착 불가

트리거 실습

testDB에 테이블 생성

-- 트리거 : 테이블에 DML(i, u, d)문이 실행 될때 자동으로 SQL문을 실행
create database if not exists testDB;
use testdb;

create table if not exists testTbl (id int, txt varchar(10));

insert into testTbl values (1, '레드벨벳');
insert into testTbl values (2, '잇지');
insert into testTbl values (3, '블랙핑크');

testTbl에 트리거 부착

-- 트리거 부착
delimiter //
create trigger testTrg		-- 트리거 이름
	after delete			-- 삭제 후에 작동하도록 지정
	on testTbl				-- 트리거를 부착할 테이블
    for each row			-- 각 행마다 적용시킴
begin
	set @msg = '가수 그룹이 삭제됨'; -- 트리거실행 시 작동되는 코드들
end //
delimiter ;

데이터 삽입, 수정, 삭제

-- 데이터를 삽입, 수정, 삭제
set @msg = '';
insert into testTbl values(4, '마마무');		-- 삽입시 아무일도 일어나지 않는다.
select @msg;
update testTbl set txt = '블핑' where ID =3;	-- 데이터 수정 시 아무일도 일어나지 않는다.
select @msg;
delete from testTbl where id = 4;		-- 데이터 삭제시 트리거 작업이 일어나게 된다.
select @msg;

실습 테이블 다르게 해서 다시 해보기

-- --------------------------------
create database if not exists testDB;
use testdb;

create table if not exists Trgtable (id int, txt varchar(10));

insert into Trgtable values (1, '레드벨벳');
insert into Trgtable values (2, '잇지');
insert into Trgtable values (3, '블랙핑크');

-- delete
delimiter //
create trigger Trg3		
	after delete
	on Trgtable
    for each row
begin
	set @msg = '가수 그룹이 삭제됨'; 
end //
delimiter ;

-- insert
delimiter //
create trigger Trg4		
	after insert
	on Trgtable
    for each row
begin
	set @msg = '가수 그룹이 추가됨'; 
end //
delimiter ;


-- update
delimiter //
create trigger Trg2		
	after update
	on Trgtable		
    for each row
begin
	set @msg = '가수 그룹이 업데이트됨'; 
end //
delimiter ;

set @msg = '';
insert into Trgtable values(4, '마마무');		-- 삽입시 아무일도 일어나지 않는다.
select @msg;
update Trgtable set txt = '블핑' where ID =3;	-- 데이터 수정 시 아무일도 일어나지 않는다.
select @msg;
delete from Trgtable where id = 4;		-- 데이터 삭제시 트리거 작업이 일어나게 된다.
select @msg;

트리거의 종류

AFTER 트리거

테이블에 INSERT, UPDATE, DELETE 등의 작업이 일어났을 때 작동
이름이 뜻하는 것처럼 해당 작업 후에(After) 작동

BEFORE 트리거

BEFORE 트리거는 이벤트가 발생하기 전에 작동
INSERT, UPDATE, DELETE 세 가지 이벤트로 작동

트리거의 사용

트리거 문법

AFTER 트리거의 사용

예제 요구 사항
- 회원 테이블에 update나 delete를 시도하면 수정 또는 삭제된 데이터를 별도의 테이블에 보관하고 변경된 일자와 변경한 사람을 기록

insert나 update 작업이 일어나는 경우, 변경되기 전의 데이터를 저장할 테이블 생성

-- AFTER 트리거 사용
-- 회원 테이블에 update, delete를 시도하면 수정 또는 삭제된 데이터를 별도의 테이블에 보관하고 변경된 일자와 변경한 사람을 기록

use sqldb;

drop table buytbl;	-- 구매 테이블은 필요 없으므로 삭제

create table backup_usertbl
(
	userID char(8) not null primary key,
    name varchar(10) not null,
    birthYear int not null,
    addr char(2) not null,
    mobile1 char(3),
    mobile2 char(8),
    height smallint,
    mDate date,
    modType char(2),	-- 변경된 타입. 수정 또는 삭제
    modDate date, 		-- 변경된 날짜
    modUser varchar(256) -- 변경한 사용자
);

AFTER 트리거의 사용

변경(Update) 발생시 작동하는 backUserTbl_UpdateTrg 트리거 생성

-- 변경(update)이 발생할 때 작동하는 트리거
drop trigger if exists backUserTbl_UpdateTrg;


delimiter //
create trigger backUserTbl_UpdateTrg 	-- 트리거 이름
	after update	-- 변경 후에 작동하도록 지정
    on userTbl	 	-- 트리거를 부착할 테이블
    for each row	
begin
	insert into backup_userTbl values(OLD.userID, OLD.name, OLD.birthYear,
		OLD.addr, OLD.mobile1, OLD.mobile2, OLD.height, OLD.mDate,
			'수정', curdate(), current_user() );
end //
delimiter ;

AFTER 트리거의 사용

삭제(Delete) 발생시 작동하는 backUserTbl_DeleteTrg 트리거 생성

AFTER 트리거의 사용

데이터 업데이트 및 삭제

-- 삭제(delete)가 발생할 때 작동하는 트리거
drop trigger if exists backUserTbl_DeleteTrg;


delimiter //
create trigger backUserTbl_DeleteTrg 	-- 트리거 이름
	after delete	-- 변경 후에 작동하도록 지정
    on userTbl	 	-- 트리거를 부착할 테이블
    for each row	
begin
	insert into backup_userTbl values(OLD.userID, OLD.name, OLD.birthYear,
		OLD.addr, OLD.mobile1, OLD.mobile2, OLD.height, OLD.mDate,
			'삭제', curdate(), current_user() );
end //
delimiter ;

update userTbl set addr = '몽고' where userID = 'JKW';
delete from userTbl where height >= 177;

수정 또는 삭제된 내용이 잘 보관되어 있는지 결과 확인

select * from backup_userTbl;

AFTER 트리거의 사용

테이블의 모든 행 데이터 삭제
DELETE 대신 TRUNCATE TABLE문 사용

백업 테이블 확인

TRUNCATE TABLE로 삭제 시에는 트리거가 작동하지 않음, DELETE 트리거는 DELETE문에만 작동

AFTER 트리거의 사용

INSERT 트리거를 생성

-- insert 트리거 
drop trigger if exists userTbl_insertTrg;
delimiter //
create trigger userTbl_insertTrg		-- 트리거 이름
	after insert		-- 입력 후에 작동하도록 지정
    on userTbl		-- 트리거를 부착할 테이블
    for each row
begin
	signal sqlstate '45000' 	-- 강제 에러 발생
		set message_text = '테이터의 입력을 시도했습니다. 귀하의 정보가 서버에 기록되었습니다';
end //
delimiter ;

데이터 입력

insert into usertbl values('ABC', '에비씨', 1977, '서울', '011', '1111111', 181, '2019-12-25');

경고 메시지가 출력된 후에, INSERT 작업은 롤백이 되고 userTbl에는 데이터가 삽입되지 않음

트리거의 사용

AFTER 트리거의 사용

TRUNCATE TABLE 테이블이름
- DELETE FROM 테이블이름문과 동일한 효과
- DML문이 아니라 트리거를 작동시키지 않음

SIGNAL SQLSTATE ‘45000’ 문
- 사용자가 오류를 강제로 발생시키는 함수
- 사용자가 정의한 오류 메시지 출력
- 사용자가 시도한 INSERT는 롤백

트리거가 생성하는 임시 테이블

INSERT, UPDATE, DELETE 작업이 수행되면 임시 사용하는 시스템 테이블
이름은 ‘NEW’와 ‘OLD’

BEFORE 트리거의 사용

테이블에 변경이 가해지기 전 작동
BEFORE 트리거 활용 예
- BEFORE INSERT 트리거를 부착해 놓으면 입력될 데이터 값을 미리 확인해서 문제가 있을 경우에 다른 값으로 변경
BEFORE 트리거 실습
- 값이 입력될 때, 출생년도의 데이터를 검사해서 데이터에 문제가 있으면 값을 변경시켜서 입력시키는 BEFORE INSERT 트리거 작성

BEFORE 트리거의 사용

트리거 생성

-- before 트리거 : 트리거 실행 이전에 SQL 실행 - 입렵되는 데이터의 문제가 있는지 확인해서 수정
-- 1900년 이전의 데이터를 입력 되면 0 으로 표기, 현재 년도보다 더 높은 년도 입력 되면 현재 년도로 수정
-- sqldb.sql를 실행해서 sqldv 초기화
use sqldb;

drop trigger if exists userTbl_BeforeInsertTrg;

delimiter //
create trigger userTbl_BeforeInsertTrg 	-- 트리거 이름
	before insert		-- 입력 전에 작동하도록 지정
    on userTbl 			-- 트리거를 부착할 테이블
    for each row 
begin
	if new.birthYear < 1900 then			-- 1900년 이전에 연도가 입력시 0으로 표기
		set new.birthYear = 0;
	elseif new.birthYear > Year(curdate()) then	-- 현재 년도보다 더 큰 미래 년도가 입력시 현재 년도로 변경
		set new.birthYear = year(curdate());
	end if;
end //
delimiter ;

값 입력 (두 값 모두 출생년도에 문제 있음)

insert into userTbl values('AAA', '에이', 1877, '서울', '011', '1112222', 181, '2022-12-25');
insert into userTbl values('BBB', '비이', 2977, '경기', '011', '1113333', 171, '2019-3-25');

SELECT * FROM userTbl문으로 확인

BEFORE 트리거의 사용

SHOW TRIGGERS문으로 데이터베이스에 생성된 트리거 확인

show triggers from sqldb;

트리거 삭제

drop trigger userTbl_beforeInsertTrg;

기타 트리거에 관한 내용

다중 트리거 (Multiple Triggers)

하나의 테이블에 동일한 트리거가 여러 개 부착되어 있는 것
- ex) AFTER INSERT 트리거 한 개 테이블에 2개 이상 부착

중첩 트리거 (Nested Triggers)

트리거가 또 다른 트리거를 작동시키는 것

트리거의 작동 순서

하나의 테이블에 여러 개의 트리거가 부착된 경우 트리거의 작동 순서 지정 가능

중첩 트리거 작동 실습

연습용 DB 생성

drop database if exists triggerdb;
create database if not exists triggerdb;

중첩 트리거 작동 실습

테이블 생성

-- 중첩 트리거 작동 실습
drop database if exists triggerdb;

create database if not exists triggerdb;

use triggerdb;

create table orderTbl 	-- 구매 테이블
(
	orderNo int auto_increment primary key, 	-- 구매 일련번호
    userId varchar(5),		-- 구매한 회원 아이디
    prodName varchar(5),	-- 구매한 물건
    orderamount int			-- 구매한 개수
);

create table prodTbl 	-- 물품 테이블
(
	prodName varchar(5), -- 물건 이름
    account int			-- 남은 물건 수량
);

create table deliverTbl		-- 배송 테이블
(
	deliverNo int auto_increment primary key, 	-- 배송 일련번호
    prodName varchar(5),	-- 배송할 물건
    account int unique		-- 배송할 물건 개수
);

데이터 입력

insert into prodTbl values ('사과', 100);
insert into prodTbl values ('배', 100);
insert into prodTbl values ('귤', 100);

구매 테이블(orderTbl)과 물품 테이블(prodTbl)에 트리거 부착

-- 물품 테이블에서 개수를 감소시키는 트리거 
-- 구매 테이블에서 구매가 있을때(insert) 물품 테이블에 개수를 감소
drop trigger if exists orderTrg;

delimiter //
create trigger orderTrg		-- 트리거 이름 : 구매 트리거
	after insert			-- orderTbl에 insert : 구매자가 물품 구매가 있을때
    on orderTbl 		-- 트리거를 부착할 테이블 
    for each row
begin
	update prodTbl set account = account - new.orderamount
		where prodName = new.prodName; -- 제품 테이블의 재고수량을 주문수량을 제외하고 재고수량 수정
end //
delimiter ;

-- 배송 테이블에 새 배송 건을 입력하는 트리거
drop trigger if exists prodTrg;
delimiter //
create trigger prodTrg 	-- 트리거 이름 : 물품 트리거
	after update		
	on prodTbl		-- 트리거를 부착할 테이블 
    for each row
begin
	declare orderAmount int;
    -- 주문 개수 = (변경 전의 개수 - 변경 후의 개수)
    set orderAmount = OLD.account - new.account;
    insert into deliverTbl(prodName, account)
		values(new.prodName, orderAmount);
end //
delimiter ;

기타 트리거에 관한 내용

중첩 트리거 작동 실습

고객이 구매한 데이터 입력

insert into orderTbl values (null, 'JOHN', '배', 5);

중첩 트리거가 잘 작동했는지 세 테이블 확인

select * from orderTbl;
select * from prodTbl;
select * from deliverTbl;

중첩 트리거 작동 실습

배송 테이블(deliverTbl)의 열 이름을 변경해서 (3)번의 INSERT가 실패하도록 실습

데이터 입력

테이블 확인

데이터가 변경되지 않았음.
(3)번의 INSERT가 실패하면 (1)번 INSERT, (2)번 UPDATE 모두 롤백됨

728x90