PL/SQL 객체) TRIGGER

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

TRIGGER

- 방아쇠 객체

- 한 액션이 일어났을 때, 연쇄적으로 일어나야 할 작업을 작성해둔 객체

- DML | DDL | Logon/Logoff

 

DML TRIGGER

- 회원 테이블의 레코드를 delete하면, 해당 레코드의 정보를 탈퇴 회원 테이블에 insert 처리

- 게시글 정보를 update하면, 게시글 로그 테이블에 insert 처리

 

문법

create [or replace] trigger 트리거명
	before / after
	insert or update or delete on 테이블
	[for each row]
declare
	-- 지역변수 선언부
begin
	-- 실행부
exception
	-- 예외처리부
end;
/

 

[옵션]

① before / after

→ 기존 DML 이전에 실행 (before) / 기존 DML 이후에 실행 (after) : fk제약조건이 걸린 경우만 유효

 

② insert on member

→ member 테이블에 insert가 일어나면 (기존 DML), 트리거 실행

 

③ insert or delete on member

→ member 테이블에 insert 또는 delete가 일어나면 (기존 DML), 트리거 실행

 

④ update of 컬럼1, 컬럼2 on member

→ member 테이블 컬럼1 또는 컬럼2에서 update가 일어나면 (기존 DML), 트리거 실행

 

⑤ for each row

→ 작성하면 행 레벨 트리거 (행 단위로 트리거 실행)

    예) update 시 6행이 변경 (기존 DML), 트리거는 각 행 별로 실행 되므로 총 6번 실행

→ 작성하지 않으면 문장 레벨 트리거

    예) update 시 6행이 변경 (기존 DML), 트리거는 총 1번 실행

 

 

의사 레코드 (Pseudo Record)

- 가상 레코드

- for each row 옵션이 적용된 행 레벨 트리거인 경우 적용

	DML 처리 전 (:old)	DML 처리 후 (:new)
insert	null	삽입된 행 정보
update	수정 전 행 정보	수정 후 행 정보
delete	삭제 전 행 정보	null

 

---

tb_member에 DML (insert, update, delete)이 실행되면, 변경 내역을 기록하는 테이블 tb_member_log에 insert처리

-- 기존 테이블
select * from tb_member;

-- 변경 내용 기록용 테이블 
create table tb_member_log (
    no number,
    member_id varchar2(20),
    log varchar2(2000) not null,
    log_date date default sysdate,
    constraint pk_tb_member_log_no primary key(no)
);

-- 번호매기기용 sequence 생성
create sequence seq_tb_member_log_no;

-- 트리거 생성
-- tb_member에 DML이 실행되면, 변경 내역을 기록하는 테이블인 tb_member_log에 insert 처리
create or replace trigger trig_member_log
    before
    insert or update or delete on tb_member	-- insert, update, delete 시 트리거 실행
    for each row	-- 각 행 별로 트리거 실행
-- delclare
    -- 지역변수 없을 시 생략 가능
begin
    -- inserting, updating, deleting에 대한 boolean 속성 가짐
    if inserting then
        -- insert 발생
        insert into tb_member_log (no, member_id, log) 
            values(seq_tb_member_log_no.nextval, :new.id, :new.id || ' 회원가입!');
        dbms_output.put_line('tb_member에 insert가 발생했습니다.');
    
    elsif updating then
        -- update 발생
        insert into tb_member_log (no, member_id, log)
            values(seq_tb_member_log_no.nextval, :new.id, :old.id || '에서 ' || :new.id || '로 변경!');
        dbms_output.put_line('tb_member에 update가 발생했습니다.');
    
    elsif deleting then
        -- delete 발생
        insert into tb_member_log (no, member_id, log)
            values(seq_tb_member_log_no.nextval, :old.id, :old.id || ' 회원 탈퇴!');
        dbms_output.put_line('tb_member에 delete가 발생했습니다.');
    
    end if;
    -- trigger에서는 트랙잭션 처리하지 않음! -> 기존 DML문의 트랙잭션과 함께 처리됨
end;
/

inserting, updating, deleting에 대한 boolean 값을 가진 속성을 이용하여 insert/update/delete가 일어났을 때에 대한 처리를 해주었으며, 상황에 알맞은 의사 레코드를 사용해주어야 합니다.

 

현재 tb_member 테이블과 tb_member_log 테이블 사이에 fk제약조건이 걸려있지 않으므로 before와 after 상관없으므로 before로 했습니다.

 

※ trigger에서는 별도의 트랙잭션 처리를 해주지 않습니다. → 기존 DML문의 트랙잭션과 함께 처리되기 때문!!!

 

tb_member에 행 추가 시

-- tb_member 행 추가 시
insert into tb_member values (seq_tb_member_no.nextval, 'abcde');

select * from tb_member;
select * from tb_member_log;

tb_member 테이블

tb_member_log 테이블

 

 

트랜잭션 처리 확인 (rollback)

tb_member 테이블

tb_member_log 테이블

tb_member 테이블에 'abcde'에 대한 insert 처리 후 rollback을 하니 tb_member_log에도 같이 rollback 처리 되었습니다.

 

트랜잭션 처리 확인 (commit)

tb_member 테이블

tb_member_log 테이블

이번엔 tb_member 테이블을 commit; 처리하니 동일하게 tb_member_log에도 commit처리가 되어 아무리 rollback을 해도 원상복구 되지 않습니다.

 

tb_member 수정 시

-- tb_member 업데이트
update tb_member
set id = 'xyz'
where no = 62;

select * from tb_member;
select * from tb_member_log;

tb_member 테이블

tb_member_log 테이블

 

tb_member 삭제 시

-- tb_member 삭제
delete from tb_member
where no = 62;

select * from tb_member;
select * from tb_member_log;

tb_member 테이블

tb_member_log 테이블

 

---

입출고 관리

입출고 수량을 상품 테이블 재고 컬럼에 반영

-- 입출고 관리
create table tb_product (
    pcode varchar2(20),
    pname varchar2(100),
    price number,
    stock number default 0, -- 재고
    constraint pk_tb_product_pcode primary key(pcode),
    constraint ck_tb_product_stock check(stock >= 0)
);

create table tb_product_io (
    no number,
    pcode varchar2(20),
    cnt number,
    status char(1),  -- I/O 입고/출고 여부
    io_date date default sysdate,
    constraint pk_tb_product_io_no primary key(no),
    constraint fk_tb_product_io_pcode foreign key(pcode) references tb_product(pcode)
);

-- no 번호 매기기용 sequence
create sequence seq_tb_product_io_no;

insert into tb_product values('apple_iphone_13', '아이폰13', 1000000, default);
insert into tb_product values('samsung_galaxy_22', '갤럭시22', 1200000, default);

select * from tb_product;
commit;

-- 입출고 수량을 상품 테이블 재고 컬럼에 반영하는 trigger
create or replace trigger trig_product_stock
    before
    insert on tb_product_io
    for each row
begin
    -- 입고
    if :new.status = 'I' then
        update tb_product 
        set stock = stock + :new.cnt
        where pcode = :new.pcode;
    -- 출고
    else
        update tb_product
        set stock = stock - :new.cnt
        where pcode = :new.pcode;
    end if;
end;
/

tb_product 테이블

 

입고 시

insert into tb_product_io
    values (seq_tb_product_io_no.nextval, 'apple_iphone_13', 100, 'I', default);
    
select * from tb_product;
select * from tb_product_io;

tb_product 테이블

tb_product_io 테이블

 

출고 시

insert into tb_product_io
    values (seq_tb_product_io_no.nextval, 'apple_iphone_13', 50, 'O', default);
    
select * from tb_product;
select * from tb_product_io;

tb_product 테이블

tb_product_io 테이블

 

기존 재고 수량보다 더 많이 출고할 경우

insert into tb_product_io
    values (seq_tb_product_io_no.nextval, 'apple_iphone_13', 80, 'O', default);
-- ORA-02290: check constraint (KH.CK_TB_PRODUCT_STOCK) violated

stock 재고 컬럼에 check 제약 조건으로 stock >= 0을 하였으므로, 기존 재고 수량보다 더 많이 출고할 경우,

check 제약 조건 위반에 대한 에러메세지가 출력됩니다.

"ORA-02290: check constraint (KH.CK_TB_PRODUCT_STOCK) violated"

 

tb_product 테이블과 tb_product_io 테이블은 하나의 트랜잭션으로 간주하기 때문에,

tb_product_io에서 제약조건 위반으로 insert처리가 되지 않았으면 tb_product 테이블에서도 update 처리가 안됩니다.