커서, 트리거

1. 커서 : https://riptutorial.com/ko/mysql/example/15257/%EC%BB%A4%EC%84%9C

2. 트리거

테이블에 삽입, 수정, 삭제 등의 작업(이벤트)이 발생할 때에 자동으로 작동하는 개체

트리거는 스토어드 프로시저와 작동이 비슷하지만 직접 실행시킬 수는 없고 오직 해당 테이블에 이벤트가 발생할 경우에만 실행된다.

그리고 트리거에는 스토어드 프로시저와 달리 IN, OUT 매개 변수를 사용할 수도 없다.

AFTER 트리거 : 테이블에 INSERT, UPDATE, DELETE 등의 작업이 일어났을 때 작동하는 트리거를 말하며 이름이 뜻하는 것처럼 해당 작업 후에 작동

BEFOR 트리거 : AFTER트리거와 마찬가지로 INSERT, UPDATE, DELETE 세 가지 이벤트로 작동한다.

에러 : Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

대응 : SET SQL_SAFE_UPDATES = 0;

-- SET SQL_SAFE_UPDATES = 0; -- 에러코드 1175나올시 처음한번만 적용
drop table if exists userTbl;
drop table if exists backup_userTbl;
drop trigger if exists backuserTbl_updateTrg;
drop trigger if exists backuserTbl_deleteTrg;
 
create table userTbl
(
userid char(8) not null primary key,
name varchar(10) not null,
mDate date
);
 
insert into userTbl values('a1','홍길동','2019-01-01');
insert into userTbl values('b1','이순신','2020-03-10');
 
create table backup_userTbl
(
userId char(8) not null primary key,
name varchar(10) not null,
mDate date,
modType char(2),
modDate date,
modUser varchar(256)
);
 
delimiter $$
create trigger backuserTbl_updateTrg -- 트리거 이름
	after update -- 업데이트 이후에 트리거 적용
    on userTbl -- 적용할 테이블
    for each row -- 각 행마다 적용
begin
	insert into backup_userTbl values(OLD.userId, OLD.name, OLD.mDate, '수정', curdate(), current_user());
end $$
delimiter ;
 
delimiter $$
create trigger backuserTbl_deleteTrg -- 트리거 이름
	after delete
    on userTbl
    for each row
begin
	insert into backup_userTbl values(OLD.userId, OLD.name, OLD.mDate, '삭제', curdate(), current_user());
end $$
delimiter ;
 
select * from userTbl;
select * from backup_userTbl;
update userTbl set name = '세종대왕' where userid = 'a1';
delete from userTbl where name = '이순신';
select * from userTbl;
select * from backup_userTbl;

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

트리거에서 INSERT, UPDATE, DELETE 작업이 수행되면 임시로 사용되는 시스템 테이블이 두개 있는데, 이름은 'NEW'와 'OLD'이다.

에러생성해보기

delimiter $$
create trigger backuserTbl_updateTrg -- 트리거 이름
	before update -- 업데이트 이후에 트리거 적용
    on userTbl -- 적용할 테이블
    for each row -- 각 행마다 적용
begin
	IF NEW.name = '관리자' then
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '이름은_관리자가_될수없습니다';
    END IF;
	insert into backup_userTbl values(OLD.userId, OLD.name, OLD.mDate, '수정', curdate(), current_user());
end $$
delimiter ;
 
update userTbl set name = '관리자' where userid = 'a1';

결과)

Error Code: 1644. 이름은_관리자가_될수없습니다

라는 오류를 내보낸다.

- 다중트리거 : 하나의 테이블에 동일한 트리거가 여러개 부착되어있는것

- 중첩트리거 : 트리거가 또 다른 트리거를 작동하는 것

ex) 고객이 구매테이블에 insert하면

트리거1 (구매 테이블 insert 트리거) -> 물품 테이블 update

트리거2 (물품 테이블 update 트리거) -> 배송 테이블 insert