[DB_MySQL] 이것이 MySQL이다 ch10 스토어드 프로그램 4(트리거, trigger)
bobo122023. 2. 12. 09:18
728x90
반응형
1. 트리거(Trigger)의 개요
◎ 트리거란?
▷ 사전적 의미로 ‘방아쇠’ ▷방아쇠 당기면 ‘자동’으로 총알이 나가듯이 테이블에 무슨 일이 일어나면 ‘자동’으로 실행 ▷제약 조건과 더불어 데이터 무결성을 위해 MySQL에서 사용할 수 있는 기능 ▷테이블에 DML문(Insert, Update, Delete 등) 이벤트가 발생될 때 작동 ▷테이블에 부착되는 프로그램 코드 ▷직접 실행 불가 ▶ 테이블에 이벤트 일어나야 자동 실행 ▷IN, OUT 매개 변수를 사용할 수 없음 ▷MySQL은 View에 트리거 부착 불가
1. 테이블 생성
-- 트리거 : 테이블에 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, '블랙핑크');
select * from testtbl;
2. 트리거 생성 및 출력
drop trigger if exists testTrg;
-- 트리거 부착
delimiter //
create trigger testTrg -- 트리거를 하나 생성
after delete -- delete를 실행하고 난 뒤 트리거 작동
on testTbl -- testTbl에 트리거를 부착
for each row -- 한 행씩 실행이 되게 설정
begin
set @msg = '가수 그룹이 삭제됨'; -- 트리거가 일어났을때 실행할 구문
end //
delimiter ;
-- 데이터를 삽입, 수정, 삭제
set @msg = ' ';
insert into testTbl values(4,'마마무');
-- 삽입시 아무일도 일어나지 않는다.
select @msg;
set SQL_SAFE_UPDATES = 0;
update testTbl set txt = '블핑' where ID = 3;
-- 데이터 수정시 아무일이 일어나지 않는다.
select @msg;
delete from testTbl where id=4;
-- 데이터 삭제시 트리거 작업이 일어나게 된다.
select @msg;
-- '가수 그룹이 삭제됨' 반환
2. 트리거의 종류
◎ AFTER 트리거 ▷ 테이블에 INSERT, UPDATE, DELETE 등의 작업이 일어났을 때 작동 ▷이름이 뜻하는 것처럼 해당 작업 후에(After) 작동
◎BEFORE 트리거 ▷BEFORE 트리거는 이벤트가 발생하기 전에 작동 ▷INSERT, UPDATE, DELETE 세 가지 이벤트로 작동
3. 트리거의 사용
◎ 트리거 문법
◎ AFTER 트리거의 사용 ▷ 예제 요구 사항
1. 회원 테이블에 update나 delete를 시도하면 수정 또는 삭제된 데이터를 별도의 테이블에 보관하고 변경된 일자와 변경한 사람을 기록 ▷ insert나 update 작업이 일어나는 경우, 변경되기 전의 데이터를 저장할 테이블 생성
-- AFTER 트리거 사용
-- 회원 테이블에 update, delete를 시도하면 수정 또는 삭제된 데이터를
-- 별도의 테이블에 보관하고 변경된 일자와 변경한 사람을 기록
use sqldb;
drop table buytbl;
drop table backup_usertbl;
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) -- 변경한 사용자
);
2. 변경(Update) 발생시 작동하는 backUserTbl_UpdateTrg 트리거 생성
-- 변경(update)이 발생할 때 작동하는 트리거
drop trigger if exists backUserTbl_UpdateTrg;
delimiter //
create trigger backUserTbl_UpdateTrg
after update -- 업데이트 이후에 동작
on usertbl -- 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 ;
3. 삭제(Delete) 발생시 작동하는 backUserTbl_DeleteTrg 트리거 생성
-- 삭제(delete)가 발생할 때 작동하는 트리거
drop trigger if exists backUserTbl_DeleteTrg;
delimiter //
create trigger backUserTbl_DeleteTrg
after delete -- 삭제 이후에 동작
on usertbl -- 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 ;
4. 데이터 업데이트 및 삭제
set SQL_SAFE_UPDATES = 0;
update usertbl set addr = '몽고' where userID = 'JKW';
delete from usertbl where height >= 177;
5. 수정 또는 삭제된 내용이 잘 보관되어 있는지 결과 확인
select * from backup_userTbl;
◎ 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', '11111111', 181, '2019-12-25');
'데이터의 입력을 시도했습니다. 귀하의 정보가 서버에 기록되었습니다.'를 반환
◎ 트리거가 생성하는 임시 테이블 ▷ INSERT, UPDATE, DELETE 작업이 수행되면 임시 사용하는 시스템 테이블 ▷이름은 ‘NEW’와 ‘OLD’
◎ BEFORE 트리거의 사용
▷ 테이블에 변경이 가해지기 전 작동 ▷BEFORE 트리거 활용 예 ▶ BEFORE INSERT 트리거를 부착해 놓으면 입력될 데이터 값을 미리 확인해서 문제가 있을 경우에
다른 값으로 변경 ▷BEFORE 트리거 실습 ▶값이 입력될 때, 출생년도의 데이터를 검사해서 데이터에 문제가 있으면 값을 변경시켜서 입력시키는
BEFORE INSERT 트리거 작성
1. 트리거 생성
-- BEFORE 트리거 : 트리거 실행 이전에 SQL 실행 - 입력되는 데이터의 문제가 있는지 확인해서 수정
-- 1900년 이전의 데이터가 입력되면 0으로 표기, 현재년도보다 더 높은 년도 입력되면 현재년도로 수정
-- sqldb.sql을 실행해서 sql 초기화
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
set new.birthYear = 0;
elseif new.birthYear > year(curdate()) then
set new.birthYear = year(curdate());
end if;
end //
delimiter ;
3. 기타 트리거에 관한 내용 ◎ 다중 트리거 (Multiple Triggers) ▷하나의 테이블에 동일한 트리거가 여러 개 부착되어 있는 것 ▶ ex) AFTER INSERT 트리거 한 개 테이블에 2개 이상 부착
◎중첩 트리거 (Nested Triggers) ▷트리거가 또 다른 트리거를 작동시키는 것
◎ 중첩 트리거 예시 1. 테이블 생성
-- 중첩 트리거
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
);
insert into prodTbl values('사과', 100);
insert into prodTbl values('배', 100);
insert into prodTbl values('귤', 100);
select * from prodTbl;
2. 구매 테이블에서 구매가 있을 때 테이블 개수 감소
-- 중첩 트리거
-- 구매 테이블에서 구매가 있을 때(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 ;
3. 배송 테이블에 새 배송 건을 입력하는 트리거
-- 배송 테이블에 새 배송 건을 입력하는 트리거
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 ;
select * from ordertbl;
select * from prodtbl;
select * from delivertbl;
◎ 트리거의 작동 순서 ▷ 하나의 테이블에 여러 개의 트리거가 부착된 경우 트리거의 작동 순서 지정 가능
트리거는 테이블에 부착해서 사용해야합니다.
변경, 삭제, 삽입 등의 변동사항이 있을 때 따로 이력을 남길 수 있고 무작위로 변경하지 못하게 할 때 사용하면 좋을 것 같아요!!!
