[SQL/ORACLE] 트리거(Trigger) 형식 및 사용예제

 

트리거(Trigger)

: 방아쇠를 당기는것. 사건이 발생했을 때 특정 영역 수행

• 이벤트 처리(~했을 때 수반되는 처리)
• 특정테이블에 이벤트(insert, delete, update)가 발생했을 시 다른테이블에 연관된 내용을 변경하도록 하는 일

트리거 형식

    CREATE [OR REPLACE] TRIGGER 트리거명
        (BEFORE | AFTER) (INSERT | DELETE | UPDATE) --이벤트(사건발생)
        ON 테이블명 --이벤트가 발생하는 테이블
        [FOR EACH ROW] --실행될 문장 행에 각각 적용
        [WHEN 조건식]
        BEGIN
            --이벤트 발생시 실행할 문장(주로 DML) => 이벤트 처리부
    END;
    /

트리거 사용예제

    CREATE OR REPLACE TRIGGER welcome
        AFTER INSERT ON emp2 --emp2테이블에서 행입력이 발생했다면
        FOR EACH ROW
        BEGIN
            DBMS_OUTPUT.PUT_LINE('환영합니다');
    END;
    /

    INSERT INTO emp2 VALUES (7000,'길동',20); -- INSERT시 트리거 실행
    INSERT INTO emp2 VALUES (7001,'라임',10);

바인드변수

• 매개변수, 전달변수, PL/SQL에서는 변수명 앞에 ':'을 붙여서 사용
• :new - 새로 입력(INSERT, UPDATE)된 데이터
• :old - 기존 데이터(이미 저장되어있는 데이터)
  ⇒ begin~end에서 사용법 ) :new.컬럼명 , :old.컬럼명

사용예제

    CREATE OR REPLACE TRIGGER welcome
        AFTER INSERT ON emp2 --emp2테이블에서 행입력이 발생했다면
        FOR EACH ROW
        BEGIN
            DBMS_OUTPUT.PUT_LINE('['||:new.ename||']'||'님의 입사를 환영합니다');
    END;
    /

    --문제) 사원테이블 emp3에 사원정보가 empno, ename, sal, hiredate 입력되었을 때 
    --급여테이블(salary[no,sal])에 그 사원에 대한 급여정보가 자동으로 입력되도록 트리거를 생성하시오

    create or replace trigger sal_info
        after insert on emp3 
        for each row
        begin
           insert into salary (no,sal)
                      values (salary_seq.nextval,  :new.sal);  
        end;
        /

    INSERT INTO emp3 VALUES (7004,'초초',3000,sysdate);

트리거 활용 테이블 만들기

-- 상품테이블
    CREATE TABLE 상품(
        상품코드 CHAR(4) CONSTRAINT 상품_pk PRIMARY KEY,
        상품명 VARCHAR2(15) NOT NULL,
        제조사 VARCHAR2(15),
        소비자가격 NUMBER,
        재고수량 NUMBER DEFAULT 0
    );

    -- 입고테이블
    CREATE TABLE 입고(
    입고번호 NUMBER CONSTARINT 입고_pk PRIMARY KEY,
    상품코드 CHAR(4) CONSTRAINT 입고_fk REFERENCES 상품(상품코드),
    입고일자 DATE DEFAULT SYSDATE,
    입고수량 NUMBER,
    입고단가 NUMBER,
    입고금액 NUMBER
    );

    -- 입고시퀀스 생성
    CREATE SEQUENCE 입고_seq 
        START WITH 1
        INCREMENT BY 1
        NOCYCLE
        NOCACHE;

-- 상품테이블에 정보입력
    insert into 상품(상품코드, 상품명, 제조사, 소비자가격) values ('a001','마우스','삼성','1000');
    insert into 상품(상품코드, 상품명, 제조사, 소비자가격) values ('a002','키보드','LG','2000');
    insert into 상품(상품코드, 상품명, 제조사, 소비자가격) values ('a003','모니터','샤오미','5000');

    -- 입력트리거 (입고테이블에 상품이 입력되었을 때 재고수량 증가)
    예) 입고테이블에 키보드가 10개 입고되었을 때 자동으로 상품테이블의 'A002'상품의 재고가 0->10으로 변경

    CREATE OR REPLACE TRIGGER produce_insert
    AFTER INSERT ON 입고
    FOR EACH ROW
    BEGIN
        UPDATE 상품 SET 재고수량 = :new.입고수량 + 재고수량
        WHERE 상품코드 = :new.상품코드; 
    END;
    /

    -- 입고테이블/ 입고번호, 상품코드, 입고일자, 입고수량, 입고단가, 입고금액
    INSERT INTO 입고 (입고번호, 상품코드, 입고수량, 입고단가, 입고금액) VALUES (입고_seq.nextval, 'a002', 10, 1000, 10000);

    -- 수정트리거 (입고테이블에 상품의 입고수량이 변경되었을때 상품테이블의 재고수량 변경)
    CREATE OR REPLACE TRIGGER product_update
    AFTER UPDATE ON 입고
    FOR EACH ROW
    BEGIN
        UPDATE 상품 SET 재고수량 = :old.재고수량 - :old.입고수량 + :new.입고수량
        WHERE 상품코드 = :new.상품코드;
    END;
    /

    UPDATE 입고 SET 입고수량 = 40 WHERE 입고번호 = 2;

    -- 삭제트리거 
    CREATE OR REPLACE TRIGGER product_delete
    AFTER DELETE ON 입고 --이벤트: 입고테이블에서 삭제 발생시
    FOR EACH ROW
    BEGIN
       UPDATE 상품 SET 재고수량 = 재고수량 - :old.입고수량 WHERE 상품코드 = :new.상품코드;
    END;
    /
    DELETE FROM 입고 WHERE 입고번호=2;