PL/SQL : TRIGGER

TRIGGER : 데이터의 변경이 일어날 때 자동으로 같이 실행되는 프로시저

프로시저/수동 처리 ↔ 트리거/자동처리

 

데이터베이스에 이벤트 발생

이벤트 : 어떤 액션을 했을 때 내용이 바뀌는 것=INSERT, UPDATE, DELETE

→ 트리거 작동 〓 데이터베이스 관리의 자동화

잘 활용하면 서로 관계가 있는 여러 테이블끼리의 데이터 관리를 자동화할 수 있다.

 

같은 테이블에서는 트리거를 만들 수 없다. 

→ 항상 다른 테이블에 연쇄 반응을 일으키도록 만들어야 한다.

 

테이블에서만 사용이 가능하고, VIEW, 인라인 뷰, 서브쿼리 다 사용이 불가하다.

AutoCommit을 가지고 있어 insert, update, delete시 commit과 rollback이 불가능하다.

 

웹 이벤트 : 메뉴/버튼/이미지 클릭됨,... → 자바스크립트로 처리

자바 이벤트 : 윈도우(자바 자체가 처리)

DB 이벤트 : 데이터가 변경됨 → TRIGGER가 처리!

 

 

트리거의 특징?

- 자동으로만 실행된다(수동 실행 불가)

- 오라클 자체에서 실행되기 때문에 자바에서는 코드 확인이나 분석이 어렵다

- BEFORE/AFTER 

- 변경 대상을 먼저 반영한 후 관련된 테이블에도 반영한다 = AFTER***

- 관련된 테이블에 먼저 반영한 후 변경 대상에 반영한다 = BEFORE

- Transaction을 사용할 수 없다. (∵ AutoCommit)

 

트리거 제작 형식

CREATE [OR REPLACE] TRIGEER tri_name
(BEFORE|AFTER) (INSERT|UPDATE|DELETE) ON table_name
--table_name에서 insert, update, delete가 일어났을 때 제어해라.
[FOR EACH ROW] --실행될 문장을 동시에 각 행에 적용
[DECLARE]
--변수 선언/선언할 변수가 없으면 DECLARE 생략 가능
BEGIN
--구현부 ... (적용할 테이블-자동화처리-에 대한 내용)
END;
/

'입고' 액션이 일어났을 때, '재고' 테이블에 입고된 부분을 반영해 주어라 등의 자동처리가 가능.

이 때 두 개의 테이블에 수량, 물품명, 코드번호 등 같은 내용이 있어야 처리가 가능하다.

 

***바인드 변수***

변수 중 새롭게 입력된 데이터와 이미 보유하고 있던 데이터를 구분한다.

:OLD는 데이터가 변경되기 이전에 가지고 있던 값(TRIGGER문을 실행되게 한 데이터 변경 이전의 값)이고,

:NEW는 데이터가 변경된 후 가지고 있는 값이다.

새로 입력된 데이터를 가져올 때는 :NEW, 이미 입력되어 있던 데이터를 가져올 때는 :OLD를 사용한다.

단, :NEW는 AFTER TRIGGER문에서는 사용될 수 없다. 

→ TRIGGER문이 AFTER TRIGGER 가 실행되기 전에 적용되기 때문이다.

https://stackoverflow.com/questions/10639331/new-and-old-trigger-code

--입고->수량,상품번호,금액
INSERT INTO 입고 VALUES('A0001',5,5000)
 :NEW.수량 (5)
 :NEW.상품번호 ('A0001')
 :NEW.금액 (5000)
--새로 입력된 데이터를 가져옴-> :NEW ->INSERT
--이미 입력되어 있는 데이터를 가져옴-> :OLD ->UPDATE,DELETE

 

 

간단한 입고/출고/재고 예제

 

테이블 설계

--상품
CREATE TABLE product(
  pno NUMBER,
  pname VARCHAR2(50),
  uprice NUMBER);  --unit price

--입고
CREATE TABLE input(
  pno NUMBER,
  quantity NUMBER,
  price NUMBER);

--출고
CREATE TABLE output(
  pno NUMBER,
  quantity NUMBER,
  price NUMBER);

--재고 -> 자동화 처리~
CREATE TABLE curproduct(
  pno NUMBER,
  quantity NUMBER,
  price NUMBER);
  
INSERT INTO product VALUES(100,'A',1000);
INSERT INTO product VALUES(101,'B',1500);
INSERT INTO product VALUES(102,'C',2000);
INSERT INTO product VALUES(103,'D',2500);
INSERT INTO product VALUES(104,'E',3000);
INSERT INTO product VALUES(105,'F',3500);

COMMIT;

 

입고 Trigger

CREATE OR REPLACE TRIGGER input_Trigger
AFTER INSERT ON input --input테이블에 insert된 이후에
FOR EACH ROW --각 행에 대해 처리해라
DECLARE
 v_cnt NUMBER; --이 트리거에서 쓰일 변수 선언
BEGIN
 --재고 테이블에서 pno가 일치하는 행이 있는지 검사 후 v_cnt에 대입
 --재고가 있으면 COUNT(*)=1, 없으면 0이 나올 것
 SELECT COUNT(*) INTO v_cnt
 FROM curproduct
 WHERE pno=:NEW.pno;
 IF v_cnt=0 THEN 
 --수량이 0이라면 재고 테이블에 row 추가
 INSERT INTO curproduct VALUES(:NEW.pno, :NEW.quantity, :NEW.price);
 ELSE
 --수량이 0이 아니라면 기존 수량과 금액에 가산
 UPDATE curproduct SET
 quantity = quantity+:NEW.quantity,
 price = price+:NEW.price
 WHERE pno=:NEW.pno;
 END IF;
END;
/

→ pno는 기존 curproduct 테이블에 있는 수많은 ROW의 pno를 의미하고,

:NEW.pno는 input테이블에 insert된(변화가 일어나 새로워진 대상) ROW의 pno를 의미한다.

즉, 입고 테이블에 새로 들어온 상품번호를 가져와 재고 테이블에 들어온 만큼의 수량과 총 금액을 추가해 준다.

 

출고 Trigger

CREATE OR REPLACE TRIGGER output_Trigger
AFTER INSERT ON output
FOR EACH ROW
DECLARE
 v_account NUMBER;
BEGIN
 SELECT quantity INTO v_account
 FROM curproduct
 WHERE pno=:NEW.pno;
 IF v_account=:NEW.quantity THEN --수량이 0이되면 delete할것임
  DELETE FROM curproduct
  WHERE pno=:NEW.pno;
 ELSE --상품이 남아있음
  UPDATE curproduct SET
  quantity = quantity-:NEW.quantity,
  price = price-:NEW.price
  WHERE pno=:NEW.pno;
 END IF;
END;
/

 

--상품명, 소개, poster, 금액
CREATE TABLE goods(
 no NUMBER,
 name VARCHAR2(1000) CONSTRAINT goods_name_nn NOT NULL,
 poster VARCHAR2(260) CONSTRAINT goods_poster_nn NOT NULL,
 price NUMBER,
 CONSTRAINT goods_no_pk PRIMARY KEY(no)
);

CREATE SEQUENCE goods_no_seq
 START WITH 1
 INCREMENT BY 1
 NOCYCLE
 NOCACHE;

CREATE INDEX goods_idx ON goods(name);

CREATE TABLE goods_input(
no NUMBER,
gno NUMBER,
account NUMBER,
price NUMBER,
CONSTRAINT gi_no_pk PRIMARY KEY(no),
CONSTRAINT gi_gno_fk FOREIGN KEY(gno) REFERENCES goods(no)
);

CREATE TABLE goods_output(
no NUMBER,
gno NUMBER,
account NUMBER,
price NUMBER,
CONSTRAINT go_no_pk PRIMARY KEY(no),
CONSTRAINT go_gno_fk FOREIGN KEY(gno) REFERENCES goods(no)
);

CREATE TABLE goods_now( 
--재고
no NUMBER,
gno NUMBER,
account NUMBER,
price NUMBER,
CONSTRAINT gn_no_pk PRIMARY KEY(no),
CONSTRAINT gn_gno_fk FOREIGN KEY(gno) REFERENCES goods(no)
);

CREATE SEQUENCE gi_no_seq
START WITH 1
INCREMENT BY 1
NOCYCLE
NOCACHE;

CREATE SEQUENCE gn_no_seq
START WITH 1
INCREMENT BY 1
NOCYCLE
NOCACHE;

CREATE SEQUENCE go_no_seq
START WITH 1
INCREMENT BY 1
NOCYCLE
NOCACHE;

INSERT INTO goods VALUES(
  goods_no_seq.nextval,
  '[만개MADE] 19가지 자연원료로 만든 만개한알 (40g*3입) 1팩',
  'https://recipe1.ezmember.co.kr/cache/data/goods/21/02/05/1000016391/1000016391_detail_017.jpg',
  12800);
INSERT INTO goods VALUES(
goods_no_seq.nextval,
'[만개특가] 닌자 마스터프렙 프로페셔널 초퍼 블렌딩',
'https://recipe1.ezmember.co.kr/cache/data/goods/21/06/25/1000020329/1000020329_detail_078.jpg',
89000);
INSERT INTO goods VALUES(
goods_no_seq.nextval,
'[만개특가] 영양간식 스트롱숏다리 1봉 20-32미(250g)',
'https://recipe1.ezmember.co.kr/cache/data/goods/21/07/28/1000021160/1000021160_detail_042.jpg',
8900);
INSERT INTO goods VALUES(
goods_no_seq.nextval,
'[만개특가] 라인바싸 탄산수 500ml 40입 무료배송 (플레인/레몬/자몽)',
'https://recipe1.ezmember.co.kr/cache/data/goods/20/09/38/1000012254/1000012254_detail_067.jpg',
14900);
INSERT INTO goods VALUES(
goods_no_seq.nextval,
'[美친특가] 휴비딕 꼬마곰/해마/펭귄/오리 탕온도계 (HBT-1/HBT-10HBT-20/HBT-30)',
'https://recipe1.ezmember.co.kr/cache/data/goods/22/03/13//1000026640/1000026640_detail_069.jpg',
5900);

COMMIT;

--트리거 INSERT
CREATE OR REPLACE TRIGGER input_insert_tr
AFTER INSERT ON goods_input
FOR EACH ROW
DECLARE
 --변수 선언
--이 변수에 대해 확인을 하겠다
v_cnt NUMBER; 
BEGIN
 --goods_now에 대한 자동화내용 구현
 SELECT COUNT(*) INTO v_cnt
 FROM goods_now
 WHERE gno=:NEW.gno;
 IF v_cnt=0 THEN
  INSERT INTO goods_now VALUES(gn_no_seq.nextval,:NEW.gno, :NEW.account, :NEW.price);
 ELSE
  UPDATE goods_now SET 
   account=account+:NEW.account,
   price=price+:NEW.price
  WHERE gno=:NEW.gno;
 END IF;
END;
/

--트리거 UPDATE
CREATE OR REPLACE TRIGGER input_update_tr
AFTER UPDATE ON goods_input
FOR EACH ROW
DECLARE
 --변수 선언
BEGIN
 --goods_now에 대한 자동화내용 구현
 UPDATE goods_now SET
  account=:OLD.account-:OLD.account+:NEW.account,
  price=:OLD.price-:OLD.price+:NEW.price
 WHERE gno=:NEW.gno;
END;
/

--트리거 DELETE
CREATE OR REPLACE TRIGGER input_delete_tr
AFTER INSERT ON goods_output
FOR EACH ROW
DECLARE
 --변수선언
 v_account NUMBER;
BEGIN
 --goods_now에 대한 자동화내용 구현
 SELECT account INTO v_account
 FROM goods_now
 WHERE gno=:NEW.gno;
 IF v_account=:NEW.account THEN
  DELETE FROM goods_now
  WHERE gno=:NEW.gno;
 ELSE
  UPDATE goods_now SET
  account = account-:NEW.account,
  price = price-:NEW.price
  WHERE gno=:NEW.gno;
 END IF;
END;
/
--insert해서 들어가는것, update set해서 들어가는 것 다 new값