[days17] 트리거(Trigger) 예제

1. 상품, 입고, 판매 테이블 생성

CREATE TABLE 상품 (
   상품코드        VARCHAR2(6) NOT NULL PRIMARY KEY
  ,상품명           VARCHAR2(30)  NOT NULL
  ,제조사        VARCHAR2(30)  NOT NULL
  ,소비자가격  NUMBER
  ,재고수량     NUMBER DEFAULT 0
);


CREATE TABLE 입고 (
   입고번호      NUMBER PRIMARY KEY
  ,상품코드      VARCHAR2(6) NOT NULL CONSTRAINT FK_ibgo_no
                 REFERENCES 상품(상품코드)
  ,입고일자     DATE
  ,입고수량      NUMBER
  ,입고단가      NUMBER
);

CREATE TABLE 판매 (
   판매번호      NUMBER  PRIMARY KEY
  ,상품코드      VARCHAR2(6) NOT NULL CONSTRAINT FK_pan_no
                 REFERENCES 상품(상품코드)
  ,판매일자      DATE
  ,판매수량      NUMBER
  ,판매단가      NUMBER
);

상품 테이블에 데이터 삽입

INSERT INTO 상품(상품코드,  상품명, 제조사, 소비자가격) VALUES
        ('AAAAAA', '디카', '삼싱', 100000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
        ('BBBBBB', '컴퓨터', '엘디', 1500000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
        ('CCCCCC', '모니터', '삼싱', 600000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
        ('DDDDDD', '핸드폰', '다우', 500000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
        ('EEEEEE', '프린터', '삼싱', 200000);
COMMIT;
SELECT * FROM 상품;

2. [입고]테이블에 상품이 입고가 되면 [상품]테이블에 상품의 [재고수량]이 수정되는 트리거 작성

create or replace trigger insipgo
after 
insert on 입고
for each row
begin
 update 상품
 set 재고수량 = 재고수량 +:NEW.입고수량
 where 상품코드 = :NEW.상품코드;
end;

입고 테이블에 데이터 삽입

INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
              VALUES (1, 'AAAAAA', '2004-10-10', 5,   50000);  -- :NEW.
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
              VALUES (2, 'BBBBBB', '2004-10-10', 15, 700000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
              VALUES (3, 'AAAAAA', '2004-10-11', 15, 52000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
              VALUES (4, 'CCCCCC', '2004-10-14', 15,  250000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
              VALUES (5, 'BBBBBB', '2004-10-16', 25, 700000);
COMMIT;

select * from 상품;

3. [입고]테이블에 [수량]을 수정하면 [상품]테이블의 [재고수량]도 수정되는 트리거 작성

create or replace trigger upipgo
after
update on 입고
for each row
begin
    update 상품 
    set 재고수량 = 재고수량 - :OLD.입고수량 + :NEW.입고수량
    where 상품코드 = :NEW.상품코드;
    --BBBB 컴퓨터 엘디 1500000 [40] -> 45
    --                            25->30
    --                      현재재고수량 40 - 25 +30 ==> 45
end;

4. 입고취소에 해당하는 [상품] 테이블의 [재고수량]도 수정하는 트리거 작성

create or replace trigger delipgo
after
delete on 입고
for each row
begin
    update 상품
    set 재고수량 = 재고수량 - :OLD.입고수량
    where 상품코드 = :OLD.상품코드;
end;

5. [판매]테이블에 자료가 추가 되는 경우 [상품]테이블의 [재고수량]이 변경 되도록 트리거 작성

    재고수량 부족하면 오류 발생

create or replace trigger insPan
before
insert on 판매
for each row
declare
    vqty number;
begin
    select 재고수량 into vqty
    from 상품
    where 상품코드 = :NEW.상품코드;
    
    if vqty>= :NEW.판매수량 then
     update 상품
     set 재고수량 = 재고수량 - :NEW.판매수량
     where 상품코드 = :NEW.상품코드;
    else
     raise_application_error(-20007, '재고수량 부족으로 판매 불가');
    end if;
end;

INSERT INTO 판매 (판매번호, 상품코드, 판매일자, 판매수량, 판매단가) VALUES
               (1, 'AAAAAA', '2004-11-10', 5, 1000000);
COMMIT;
SELECT * FROM 상품;
SELECT * FROM 판매;

판매수량을 25로 올리면 오류

6. [판매]테이블의 자료가 변경되는 경우 [상품]테이블의 [재고수량]이 변경되도록 트리거 작성

create or replace trigger upPan
before
update on 판매
for each row
declare
    vqty number;
begin
    select 재고수량 into vqty
    from 상품
    where 상품코드 = :NEW.상품코드;
    
    if :NEW.판매수량> (vqty + :OLD.판매수량) then
        raise_application_error(-20008, '판매 수정 오류 !!!');
    else 
        update 상품
        set 재고수량 = 재고수량 - :new.판매수량 + :old.판매수량
        where 상품코드 = :NEW.상품코드;
    end if;
--exception
end;

update 판매 set 판매수량=200 where 판매번호=1;

update 판매 set 판매수량=10 where 판매번호=1;