trigger(트리거)

trigger 정의

• 테이블에 연결해서 사용하는 형태
• INSERT, UPDATE, DELETE 작업이 수행 될때 특정 코드가 동작하도록 하는 구문
• 예시)
  • 쇼핑몰에서 소비자가 PC를 1개 샀습니다. 그러면 판매글에서 남은 수량이 1개 줄어 들겠죠?
    이 일련의 과정을 코드로 짯다고 생각해 봅시다.
    그러면 제가 PC를 1개 사는 코드 / 판매글의 남은 수량이 1개 줄어드는 코드
    각각 작성하고 실행시켜 주어야 겠죠?
    그런데 쇼핑몰에서 물건을 사는 행위는 광장히 많이 일어 납니다.
    그때 마다 코드를 각각 실행 주기에는 비효율적이기 때문에
    물건 1개 사면, 자동적으로 판매글의 남은 수량 1개 줄어드는것
    이 역할을 해줄수 있는 애가 바로 trigger  입니다.

---

trigger 의 종류

AFTER

• DML(INSERT, UPDATE, DELETE)문 직후에 동작하는 트리거

BEFORE

• DML(INSERT, UPDATE, DELETE)문 이전에동작하는 트리거

INSTEAD OF

• 뷰에 부착하는 trigger  

---

trigger  변수참조 키워드

:OLD

• 참조전 COLUMN값
• INSERT 시에는 모든 필드는 NULL
• INSERT: 입력전 자료, UPDATE: 수정전 자료, DELETE:삭제전 자료

:NEW

• 참조후 COLUMN값
• DELETE 시 모든 필드는 NULL
• INSERT: 입력후 자료, UPDATE: 수정후 자료

UPDATE OR DELETE를 시도하면 기존자료를 백업테이블에 보관
AFTER 트리거는 보통 UPDATE, DELETE 사용 ( :OLD, :NEW )
BEFORE 트리거는 보통 INSERT 사용 ( :NEW )

---

trigger의 형태

CREATE OR REPLACE TRIGGER 트리거명
	트리거타입(종료) --예시) AFTER UPDATE OR DELETE
    ON 부착시킬테이블명
    OPTION(FOR EACH ROW) -- FOR EACH ROW :행레벨 트리거
    /* DECLARE(생략가능)*/
BEGIN

END;

AFTER 트리거

• DML(INSERT, UPDATE, DELETE)문 직후에 동작하는 트리거
  • 값 추가 | 업데이트 | 삭제 후 → trigger  BEGIN문 실행

예제1) 자동 입력 트리거

사원 테이블에 새로운 데이터가 들어오면(즉, 신입 사원이 들어오면) 급여 테이블에 새로운 데이터(즉 신입 사원의 급여 정보)를 자동으로 생성하도록 하기 위해서 사원 테이블에 트리거를 작성해 봅시다. (신입사원의 급여는 일괄적으로 200으로 합니다. ) 

1단계 : 테이블 만들기

--사원 테이블 생성
CREATE TABLE EMP01(
  EMPNO NUMBER(4) PRIMARY KEY,
  EMPNAME VARCHAR2(45),
  EMPJOB  VARCHAR2(60)
);
-- 급여 테이블 생성
CREATE TABLE SAL01(
	SALNO NUMBER(4) PRIMARY KEY,
	SAL   NUMBER(7,2),
	EMPNO NUMBER(4) REFERENCES EMP01(EMPNO)
);

2.급여번호를 자동 생성하는 시퀀스를 정의하고 이 시퀀스로부터 일련번호를 얻어 급여번호에 부여합시다. 

CREATE SEQUENCE SAL01_SALNO_SEQ;

3. 트리거 생성

/
CREATE OR REPLACE TRIGGER TRG_02 -- TRG_02라는 트리거명 생성
--EMP01 테이블에 데이터 추가 한 후 트리거 실행
AFTER INSERT --추가 후 트리거 생성
ON EMP01 -- EMP01 테이블 연결
--
FOR EACH ROW -- 컬럼의 모든행 적용
BEGIN 
INSERT INTO SAL01 -- SAL01 테이블에 정보 추가
VALUES(
SAL01_SALNO_SEQ.NEXTVAL,  -- SALNO 컬럼에 시퀀스
200,-- SAL 컬럼에 추가
:NEW.EMPNO); -- :NEW 입력되는 번호(EMPNO) 바로 가져와라
END;
/

4. 정보 추가

INSERT INTO EMP01 VALUES(1, '이수빈', '프로그래머');
INSERT INTO EMP01 VALUES(2, '전수빈', '프로그래머');

결과

SELECT * FROM EMP01;
SELECT * FROM SAL01;

 

---

예제2)자동 삭제 트리거

 사원번호 2를 급여 테이블에서 참조하고 있기 때문에 삭제가 불가능하다. 사원이 삭제되려면 그 사원의 급여 정보도 급여 테이블에서 삭제되어야 합니다. 사원의 정보가 제거 될 때 그 사원의 급여 정보도 함께 삭제하는 내용을 트리거로 작성하도록 합시다. 

1. 삭제 트리거 생성

CREATE OR REPLACE TRIGGER TRG_03
AFTER DELETE ON EMP01
FOR EACH ROW
BEGIN
DELETE FROM SAL01 WHERE EMPNO=:old.EMPNO; -- :old 변경전 데이터
END;
/

2. 삭제

DELETE FROM EMP01 WHERE EMPNO=1;

 결과

---

예제 3) 갱신트리거

이미 입고된 상품에 대해서 입고 수량이 변경되면 상품 테이블의 재고수량 역시 변경되어야 합니다. 이를 위한 갱신 트리거 작성해 봅시다. 

1. 트리거 생성

CREATE OR REPLACE TRIGGER TRG03
AFTER UPDATE ON IPGO
FOR EACH ROW
BEGIN
UPDATE PRODUCT
SET PROD_JAEGO = PROD_JAEGO + (-:old.IPGO_QTY+:new.IPGO_QTY) 
WHERE PROD_ID = :new.PROD_ID;
END;

2. 입고 번호 3번은 냉장고가 입고된 정보를 기록한 것으로서 입고 번호 3번의 입고수량을 10으로 변경하였더니 냉장고의 재고 수량 역시 15로 변경되었습니다.

예제1)

입고 테이블에 상품이 입력되면 입고 수량을 상품 테이블의 재고 수량에 추가하는 트리거를 작성해 봅시다. 

1.1 테이블 생성

CREATE TABLE PRODUCT(
	PROD_ID   VARCHAR2(6) PRIMARY KEY,	--상품코드
	PROD_NAME VARCHAR2(12) NOT NULL,     --상품명
	PROD_JEJO VARCHAR(12),               --제조사
	PROD_SALE NUMBER(8),				--소비자가격
	PROD_JAEGO NUMBER DEFAULT 0		--재고수량
);

CREATE TABLE IPGO(
	IPGO_ID NUMBER(6) PRIMARY KEY,				  --입고번호
	PROD_ID VARCHAR2(6) REFERENCES PRODUCT(PROD_ID), --상품코드
	IPGO_DAY DATE DEFAULT SYSDATE,				  --입고일자, 입력안되면 오늘날짜로 기본값 설정함
	IPGO_QTY NUMBER(6),						  --입고수량
	IPGO_COST NUMBER(8),						  --입고단가
	IPGO_AMOUNT NUMBER(8)					  --입고금액
);

1.2 데이터 추가

INSERT INTO PRODUCT(PROD_ID, PROD_NAME, PROD_JEJO, PROD_SALE)
	VALUES('A00001','세탁기', 'LG', 500); 
INSERT INTO PRODUCT(PROD_ID, PROD_NAME, PROD_JEJO, PROD_SALE)
	VALUES('A00002','컴퓨터', 'LG', 700);
INSERT INTO PRODUCT(PROD_ID, PROD_NAME, PROD_JEJO, PROD_SALE)
	VALUES('A00003','냉장고', '삼성', 600);

1.3 조회

SELECT *FROM PRODUCT;
SELECT *FROM IPGO;

PRODUCT 테이블 조회

IPGO 테이블 조회

2. 트리거 생성

CREATE OR REPLACE TRIGGER TRg_04
AFTER INSERT ON IPGO
FOR EACH ROW
BEGIN
UPDATE PRODUCT
SET PROD_JAEGO = PROD_JAEGO + :NEW.IPGO_QTY
WHERE PROD_ID = :NEW.PROD_ID;
END;

2.1 입고 추가

INSERT INTO IPGO(IPGO_ID, PROD_ID, IPGO_QTY, IPGO_COST, IPGO_AMOUNT) 
VALUES(1, 'A00001', 5, 320, 1600);
INSERT INTO IPGO(IPGO_ID, PROD_ID, IPGO_QTY, IPGO_COST, IPGO_AMOUNT) 
VALUES(2, 'A00001', 2, 320, 640);

2.3 조회

SELECT *FROM PRODUCT;
SELECT *FROM IPGO;

IPGO 테이블 조회

PRODUCT 테이블 조회

 

 

---

BEFORE 트리거

• INSERT,UPDATE, DELETE문이 실행될때, 되기 전에 먼저 트리거 실행
  • trigger BEGIN문 실행 → 테이블 값 추가| 업데이트 삭제
• INSERT 문에서는 :OLD 쓸 수 없음 => INSERT 되기 전에 값이 없기 때문에

예제1) 값을 추가 할때 추가되기 전, 첫글자만 가져와서 ***를 붙이고 추가하는 트리거

:OLD 쓸 수 없는데, 그 이유는 INSERT 되기 전에 값은 없기 때문이다

1. 트리거 생성

CREATE OR REPLACE TRIGGER USER_INSERT_TRI
	BEFORE INSERT ON TBL_USER
    FOR EACH ROW
DECLARE

BEGIN
	:NEW.NAME := SUBSTR(:NEW.NAME, 1,1) || '***";
    DBMS_OUTPUT.PUT_LINE(':NEW.NAME');
END;

2. 정보 추가

INSERT INTO TBL_USER VALUES('A123','홍길동');
INSERT INTO TBL_USER VALUES('B123','이순신');
INSERT INTO TBL_USER VALUES('C123','박찬호');

결과

 BEFORE 트리거 항상 해당하는 DML문이 실행되기 전 먼저 작동 

예제 1)

분류테이블에 추가되거나, 변경될 때 분류코드를 항상 대문자로 처리하는 트리거 예제

1. 트리거 생성

 CREATE or REPLACE TRIGGER tg_lprod_upper
        BEFORE INSERT  
        ON  lprod --LPROD 테이블 값 입력전에 트리거 실행
       FOR EACH ROW
    BEGIN
		:NEW.lprod_gu := UPPER(:NEW.lprod_gu);
    END;

1.1 조회

     SELECT *  FROM  lprod;

LPROD 테이블 값 추가전

2. 값 추가

 INSERT INTO lprod 
    VALUES (lprod_seq.NEXTVAL, 'tt07', '트리거 테스트 ');

    SELECT *  FROM  lprod;

LPROD 테이블 값 추가 후 트리거 적용

• tt07 트리거 의해 TT07로 값 변경

문장내에서 DML을 구분하기 위한 함수

INSERTING

• 트리거된 문장이 INSERT 이면  TRUE 리턴

UPDATING

• 트리거된 문장이 UPDATE이면  TRUE 리턴

DELETING

• 트리거된 문장이 DELETE이면  TRUE 리턴

예제1)

 장바구니 테이블에 입력이 발생할 때 재고 수불 테이블에 출고, 현재고를 변경하는 트리거 예제

CREATE or REPLACE TRIGGER tg_cart_qty_change
      AFTER  insert or update or delete    ON  cart
      FOR EACH ROW
   DECLARE 
       v_qty NUMBER; -- v_qty 변수명을 NUMBER 타입으로 지정
       v_prod VARCHAR2(20); -- v_prod 변수명을 VARCHAR2 타입으로 지정
 BEGIN
    IF INSERTING THEN -- 트리거된 문장이 INSERT 이면  TRUE 리턴
         v_qty := NVL(:NEW.cart_qty,0); --v_qty = cart_qty INSERT 후 값
         v_prod := :NEW.cart_prod;
    ELSIF UPDATING THEN --트리거된 문장이 UPDATE 이면  TRUE 리턴
         v_qty :=  NVL(:NEW.cart_qty,0) - NVL(:OLD.cart_qty,0);
         v_prod := :NEW.cart_prod;
    ELSIF DELETING THEN --트리거된 문장이 DELETE 이면  TRUE 리턴
         v_qty :=  -(NVL(:OLD.cart_qty,0));
         v_prod := :OLD.cart_prod;
    END IF;   
    
       UPDATE remain SET REMAIN_0 = REMAIN_0 + v_qty,  
                       REMAIN_J_99 = REMAIN_J_99 - v_qty
        WHERE remain_year = '2023' AND remain_prod = v_prod;
    
       DBMS_OUTPUT.PUT_LINE('수량 :' || v_qty);

EXCEPTION 
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('예외 발생:' || SQLERRM);     
END;

 

Trigger 삭제

DROP TRIGGER 트리거명;