※ 트리거 예제
--입고테이블
create table tblinsert(
num smallint identity not null primary key,
id smallint not null,
name varchar(40) null,
cnt int null
)
--출고테이블
create table tbloutput(
num smallint identity not null primary key,
id smallint not null,
name varchar(40) null,
cnt int null
)
-- 재고테이블
create table tblstock(
num smallint identity not null primary key,
id smallint not null,
name varchar(40) null,
cnt int null
)
/*
입고테이블에사용될트리거tr_insert
----------------------------
1. 입고테이블에데이터가입력된다.
insert into tblinsert values(20, '컴퓨터', 3)
insert into tblinsert values(20, '컴퓨터', 3)
insert into tblinsert values(30, 'VTR', 1)
2. 입고테이블에데이터가입력되면, 자동으로동시에재고테이블에데이터가입고된다.
3. 만약재고테이블에이미동일한데이터가있으면물건의누적갯수(cnt)만증가한다.
*/
create trigger tr_insert on tblinsert
for insert
as
declare @name nvarchar(40), @amount1 int, @amount2 int
set @name = (select name from inserted)
set @amount1 = (select cnt from inserted)
if exists(select name from tblstock where name = @name) --항목일치여부 확인
begin
set @amount2 = (select cnt from tblstock where name = @name)
update tblstock set cnt = @amount1+@amount2 where name = @name
end
else
begin
insert into tblstock
select id, name, cnt from inserted
end
-- 입고테이블 case2
create trigger tr_insert on tblinsert for insert as
if exists(select * from tblstock, inserted where tblstock.id = inserted.id)
begin
--기존에 입고 테이블에 있는 데이터이면 수량 만 증가
update tblstock
set tblstock.cnt = tblstock.cnt + inserted.cnt
from tblstock, inserted
where tblstock.id = inserted.id
end
else
begin
insert into tblstock
select id, name, cnt from inserted
end
/*
출고테이블에사용될트리거tr_output
----------------------------
1. 출고테이블에데이터입력(위참조)
2. 출고테이블에데이터가입력되면, 자동으로동시에재고테이블데이터수량이감소한다.
3. 만약재고테이블의재고수량이출고할수량보다작으면[출고할수없습니다]라는메세지를출력.
raiserror("메세지...", 10, 1) --에러메세지출력함수
*/
alter trigger tr_output on tbloutput
instead of insert
as
declare @name nvarchar(40), @amount1 int, @amount2 int
set @name = (select name from inserted)
set @amount1 = (select cnt from inserted)
if exists(select name from tblstock where name = @name)
begin
set @amount2 = (select cnt from tblstock where name = @name)
if(@amount2 > @amount1)
update tblstock set cnt = @amount2-@amount1 where name = @name
else
begin
if(@amount2 = @amount1)
delete tblstock where name = @name
else
raiserror('출고할수없습니다', 10, 1)
end
end
else
begin
raiserror('재고목록에없습니다', 10, 1)
end
raiserror('재고목록에없습니다', 10, 1) : 에러 메시지 출력
-- 출고테이블
alter trigger tr_Output on tbloutput for insert as
if exists(select * from tblstock, inserted
where tblstock.id = inserted.id and tblstock.cnt >= tblstock.cnt)
begin
update tblstock
set tblstock.cnt = tblstock.cnt - inserted.cnt
from tblstock, inserted
where tblstock.id = inserted.id
end
else
begin
raiserror('출고수량이재고수량보다커서출고못함', 10, 1)
end
'sql' 카테고리의 다른 글
| MSSQL 에서 Getdate()함수로 얻은 포맷 자유로이 변환하기 (0) | 2010.07.15 |
|---|---|
| 쿼리문 where in (0) | 2010.06.10 |
| 중복값제거 (0) | 2010.03.12 |
| inner join 사용할 때 문제점 (0) | 2010.03.02 |
| mysql 복구하기 (0) | 2009.12.17 |