[MySQL] 트리거(Trigger)

트리거

• 테이블에 DML문(Insert, Update, Delete 등) 이벤트가 발생될 때 작동
• 직접 실행 불가 - 테이블에 이벤트 일어나야 자동 실행
• IN, OUT 매개 변수를 사용핛 수 없음
• MySQL은 View에 트리거 부착 불가!!!

 

예시) After 트리거

create table tmp_tbl(
userid varchar(10),
workdate date,
bigo char(1));

desc tmp_tbl;

delimiter //
create trigger tri1
after update
on student -- student테이블을 update한 이후,
for each row
begin
	insert into tmp_tbl values('aa',curdate(),'U');
end //
delimiter ;

update student set stu_weight = stu_weight-10;

delimiter //
create trigger tri5
after update
on student -- student 테이블에서 업데이트 일어난 후
for each row
begin
	if(old.stu_weight> 40.00) then
		insert into tmp_tbl values('aabb',curdate(),'A');
        end if;
end //
delimiter ;

delimiter //
create trigger tri5
after update
on student -- student 테이블에서 업데이트 일어난 후
for each row
begin
	if(old.stu_weight> 40.00) then
		insert into tmp_tbl values('aabb',curdate(),'A');
        end if;
end //
delimiter ;

update student set stu_name='김길동'
where stu_name='옥한빛';

commit;

 

 

사원테이블에 사원이 추가될 때 5000보다 급여가 많으면 emp500 테이블에 입력된 사원번호, 사원이름, 입력된 날짜를 입력하는 트리거 작성

-- 사원테이블에 사원이 추가될 때 5000보다 급여가 많으면 emp500 테이블에 입력된 사원번호, 사원이름, 입력된 날짜를 입력하는 트리거 작성
create table emp500(
empno int(4),
ename varchar(10),
workdate date);

delimiter //
create trigger tri6
after insert 
on emp
for each row
begin
   if new.sal > 5000 then
    insert into emp500 values(new.empno,new.ename,now());
    end if;
end //
delimiter ;

insert into emp 
values(1111,'gildong','student',7839,now(),5600,null,10);

insert into emp 
values(2222,'jack','student',7839,now(),4600,null,10);

select *from emp500;

 

 

After 트리거 - delete

부서테이블의 데이터 삭제 시 dept_Del 테이블에 삭제된 데이터를 저장하는 트리거를 작성

-- 부서테이블의 데이터 삭제 시 dept_Del 테이블에 삭제된 데이터를 저장하는 트리거를 작성

create table dept_del(
userid varchar(10),
workdate date,
deptno int(2),
dname varchar(14),
loc varchar(13));

delimiter //
create trigger tri7
after delete
on dept
for each row
begin
	insert into dept_del values('user',now(),old.deptno,old.dname,old.loc);
end //
delimiter ;

delete from dept where deptno=10;

select *from dept_del;

트리거 종합

-- 1. 상품 정보(product)테이블에 열 이름이 ‘비고’ 라는 열을 varchar(20)으로 삽입해라.
ALTER TABLE product ADD 비고 varchar(20) AFTER p_group;
-- 2. 1번에서 삽입한 열이 상품 정보(product)테이블에 삽입되었는지 확인해라.
select *from product;
-- 3. 상품 정보(product)테이블에 ‘비고’ 열의 구조를 char(3)으로 변경해라.
alter table product modify 비고 char(3);
-- 4. 상품코드 401에 대한 거래내역 뷰(v_trade)를 만들어라.
create view v_trade as select * from trade where p_code=401;
-- 5. 상품 정보(product)테이블에 가장 최근에 들어온 거래처 코드 정보를 검색해라(top-n질의)
select c.c_code from customer c, product p, trade t where p.p_code = t.p_code and c.c_code=t.c_code order by c.c_code desc limit 1; 
-- 6. 상품을 삽입하는 프로시저를 생성해라.
-- call p_pro(‘403’, ’7.1채널 스피커’, 180000, ‘스피커’);
delimiter //
create procedure p_pro
(in v_p_code char(3),
in v_p_name varchar(30),
in v_p_cost int,
in v_p_group varchar(30))
begin
insert into product(p_code, p_name, p_cost, p_group)
values(v_p_code, v_p_name, v_p_cost, v_p_group);
end //
delimiter ;	

call p_pro('403', '7.1채널 스피커', 180000, '스피커');

트리거종합 中 삭제

create table product_del
(u_id varchar(10),
wdate date,
p_code int(6),
p_name varchar(30),
p_cost int,
p_group varchar(30));

-- 상품 삭제 시(product테이블에서) product_del 테이블에 삽입이 이루어지는트리거를 작성해라.
-- p_code , p_name , p_cost , p_group은 기존 product테이블에 있는 값으로 삽입해라.

-- delete from product where p_code=’201’;

delimiter //
create trigger tri8
after delete
on product
for each row
begin
	insert into product_del values('usd',now(),old.p_code,old.p_name,old.p_cost,old.p_group);
    end //
    delimiter ;
    
delete from product where p_code='201';
select *from product_del;