14_트리거

[트리거] trigger
자동화와 연결
trigger의미는 연쇄반응, 목적은 무결성의 원칙을 유지

형식)
create trigger 트리거이름
on 테이블명 for [insert/delete/update]
as 트리거 수행내용(sql문장)

-insert,delete,update에 대한 트리거만 생성할수 있다.
-한테이블에 데이터가 입력,수정,삭제 동작을 하면
  그것에 대한 영향이 다른 테이블에 미치게 한다.
-업무자동화(업무적인 룰을 DB단계에서 구현)
-다른테이블을 컨트롤
-논리적으로 일관성을 유지할 수 있음
-select에는 이벤트를 발생시키지 않음

ex)회원이 상품을 구매하면 상품재고가 줄어들고 구매는 늘어남.

ex)업체---입고---상품---구매--회원(마일리지)

use mydb
트랜잭션과 관련된 inserted/deleted테이블이 중간에 존재

begin tran
insert T1(unum,uname) values (1,'홍길동')
insert T1(unum,uname) values (2,'봉선화')

begin tran
delete from T1 where unum=1

begin tran
update T1
set uname='민들레'
where unum=2

rollback
commit

create table T1(
 unum  int identity primary key,
 uname varchar(20)
)
create table T2(
 unum  int identity primary key,
 uname varchar(20)
)

insert T1(uname) values('백장미')
select * from T1
select * from T2

insert T1(uname) values('민들레')
create trigger T1_insert
on  T1 for insert --이벤트
as
 declare @nm varchar(20)
 set @nm=(select uname from inserted)
 insert T2(uname) values (@nm)

문)T1 '민들레' 삭제하면 T2도 삭제되는 트리거 작성하시오
create trigger T1_del
on T1 for delete
as
 declare @nm varchar(20)
 set @nm=(select uname from deleted)
 delete from T2 where uname=@nm

delete from T1 where uname='민들레'
select * from t1
select * from t2

문)T1 '백장미'가 수정되면 T2이름도 수정되는 트리거 작성하시오
create trigger T1_update
on T1 for update
as
 declare @nm1 varchar(20),@nm2 varchar(20)
 set @nm1=(select uname from inserted)
 set @nm2=(select uname from deleted)
 update T2 set uname=@nm1 where uname=@nm2

update T1 set uname='흑장미' where uname='백장미'

실전예제)
create table 상품(
상품코드 char(4)   primary key,
상품명  varchar(30) not null,
상품가격 int     check(상품가격>=0),
재고수량 int      check(재고수량>=0) default 0
)
insert 상품 values('AAA','냉장고',1000,5)
insert 상품 values('BBB','에어콘',5000,3)
insert 상품 values('CCC','선풍기',3000,1)
select * from 상품

create table 입고(
입고번호 int identity primary key,
상품코드 char(4)   foreign key references 상품(상품코드),
입고일자 smalldatetime default getdate(),
입고수량 int     check(입고수량>=0),
입고단가 int     check(입고단가>=0),
입고금액 as  (입고수량*입고단가)
)

create trigger 입고_insert
on 입고 for insert
as
 declare @qty int, @cd char(4)
 set @qty=(select 입고수량 from inserted) 
 set @cd=(select 상품코드 from inserted)
 update 상품 set 재고수량=재고수량+@qty
 where 상품코드=@cd

insert 입고(상품코드,입고수량,입고단가) values ('AAA',8,500)

select * from 상품
select * from 입고

drop table 상품
drop table 입고

delete from 입고 where 입고번호=1

create trigger 입고_del --트리거작성하시오
on 입고 for delete
as
 declare @qty int,@cd varchar(4)
 set @qty=(select 입고수량 from deleted)
 set @cd=(select 상품코드 from deleted)
 update 상품 set 재고수량=재고수량-@qty
 where 상품코드=@cd