💡
DB 테이블에 DML 기반의 이벤트(삽삭갱)가 발생될 때 동작하는 데이터베이스 개체
MySQL은 View에 트리거를 부착할 수 없다.
트리거의 종류 : After, Before insert | update | delete
create
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body
trigger_time : { BEFORE | AFTER }
trigger_event : { INSERT | DELETE | UPDATE }
trigger_order : { FOLLOWS | PRECEDES } other_trigger_name테이블에 여러 트리거가 부착되어 있을 때, 다른 트리거보다 먼저 혹은 나중에 실행되도록 설정함.
Procedure나 Function → alter문으로 내용 변경이 불가하다.
Trigger → alter문 자체가 불가하다.
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 ;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 ;
delete from userTbl where height >= 177;
select * from backup_userTbl;use sqlDb;
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');signal sqlstate ‘45000’ → 사용자가 에러를 강제로 발생시키는 함수이다.
→ 에러가 발생하면 insert 작업이 rollback 된다.
- 생성된 트리거 확인 방법
SHOW TRIGGER userTbl_BeforeInsertTrg;임시 테이블
트리거에서 insert, update, delete 작업이 수행되면, 임시로 사용되는 시스템 테이블이 두 개 생긴다.
- OLD : UPDATE, DELETE 시에 생성, 사라질 값을 임시로 보관.
- NEW : INSERT, UPDATE 시에 생성, 저장될 값을 임시로 보관. → NEW의 값이 테이블에 반영됨.
INSERT → NEW 테이블 → 테이블
DELETE → → 테이블 → OLD 테이블
UPDATE → NEW 테이블 → 테이블 → OLD 테이블
NEW 테이블 값을 조작하여 입력될 값을 바꿀수 있다.
BEFORE와 AFTER
- BEFORE : 테이블에 작업이 가해지기 전에 동작한다. BEFORE INSERT는 값 검사하여 변경이나 rollback 가능하다.
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 ;
INSERT INTO userTbl values ('AAA', '에이', 1877, '서울', '011', '1112222', 181, '2019-12-25'), ('BBB', '비이', 2977, '경기', '011', '1113333', 121, '2019-12-25');
select * from userTbl;- NEW에 값을 집어 넣을 때, SET을 이용함.
- .으로 컬럼 접근할 수 있음.
다중 트리거, 중첩 트리거
- 다중 트리거 : 동일한 트리거 (Before | After + Insert | Delete | Update 가 같은.)가 한 테이블에 여러개 부착된 것.
- 중첩 트리거 : 트리거가 다른 트리거를 작동시키는 것.
중첩 트리거는 시스템 성능을 악화시킬 수 있다. 어느 한 트랜잭션의 실패시 모든 트리거 작업이 자동으로 rollback 되기 때문이다.
트리거 작동 순서
{ FOLLOWS | PRECEDES } other_trigger_nameFOR EACH ROW 다음에 씀.
중첩 트리거
drop database if exists triggerDB;
create database 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);-- 물품 테이블 개수 감소
drop trigger if exists orderTrg;
delimiter //
create trigger orderTrg
after 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;
alter table deliverTbl change prodName productName varchar(5);
insert into orderTbl values (null, 'DANG', '사과', 9); -- 중첩된 트리거 작업이 실패하면 그 앞에 수행된 모든 트리거의 변경 사항도 rollback 된다.MySQL은 재귀 트리거를 허용하지 않는다.
Hello