트리거(Trigger).MySQL

2023.02.10 - [MySQL] - 커서(Cursor).MySQL

커서(Cursor).MySQL

 

이전 글에서 계속 됩니다.

 

---

트리거(Trigger)의 개요

트리거란?

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

---

-- 트리거 : 테이블에 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;

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;
update testtbl set txt = '블핑' where id = 3;	-- 수정 : 아무일이 일어나지 않음
select @msg;
delete from testtbl where id = 4;				-- 삭제 : 트리거 작업이 일어납니다.
select @msg;

-- 데이터를 삽입 했을때 ------------------------
drop trigger if exists trig_in;

delimiter //
create trigger trig_in
	after insert
    on testtbl
    for each row
begin
	set @msg1 = '가수 이름이 추가됨';
end	//
delimiter ;

set @msg1 = '';
insert into testtbl values (4, '마마무');
select @msg1;

select * from testtbl;

delete from testtbl where id = 4;

-- 수정 시의 트리거 발동 ----------------------------------
drop trigger if exists trig_upd;

delimiter //
create trigger trig_upd
	after update
    on testtbl
    for each row
begin
	set @msg2 = '가수 그룹이 수정됨'; 	-- 트리거 실행시 작동하는 코드
end //
delimiter ;

set @msg2 = '';
update testtbl set id = 3 where id =1;
select * from testtbl;
select @msg2;

 

---

트리거의 종류

AFTER 트리거

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

 

BEFORE 트리거

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

---

 

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

DROP TABLE if exists 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)	-- 변경한 사용자
);

-- 변경(update)과 삭제(delete)가 발생할 때 작동하는 트리거
-- 변경 발생시 트리거
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 ;

select * from backup_usertbl;

-- 삭제 발동시 작동 트리거

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 ;

update usertbl set addr = '몽고' where userid = 'jkw';
delete from usertbl where height = 177;

select * from backup_usertbl;

---

AFTER 트리거의 사용

• TRUNCATE TABLE 테이블이름
  • DELETE FROM <테이블이름> 문과 동일한 효과
  • DML문이 아니라 트리거를 작동시키지 않습니다.
• SIGNAL SQLSTATE '45000' 문
  
  • 사용자가 오류를 강제로 발생시키는 함수
  • 사용자가 정의한 오류 메시지 출력
  • 사용자가 시도한 INSERT는 롤백됩니다.

---

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

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

 

---

BEFORE 트리거의 사용

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

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

drop trigger if exists userTbl_beForeInsertTrg;

delimiter //
create trigger userTbl_BeforeInserTrg
	before insert
	on unsertbl
    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;

-- 생성된 트리거 확인
show triggers from sqldb;

 

---

기타 트리거

다중 트리거(Multiple Triggers)

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

 

중첩 트리거(Nested Triggers)

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

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

 

-- 중첩 트리거
-- 구매 테이블에서 구매가 있을때(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 ;



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;

insert into ordertbl values (NULL, 'JOHN', '배', 5)