1030 oracle - trigger

 

TRIGGER

-사전적으로 방아쇠,연쇄반응을 의미
-특정 이벤트(dml,ddl 등)이 있을때, 일련의 동작을 수행하는 db객체

-트리거 종류

1.DML 트리거
ex)회원삭제,정보변경시 데이터를 옮긴다던가 일련의 동작 수행.
2.DDL 트리거
3.Logon/off 트리거 

-트리거 문법

/*
트리거 문법
    
    create [or replace] trigger trig_트리거명
        before | after          --원 dml 실행 전/후 트리거 실행 결정.
        insert | update | delete on 테이블명 (2개이상이라면 or로 연결)
        [for each row--행 레벨 트리거(원 dml 적응행마다 실행 4행이면 4번), 생략하면 문장 레벨 트리거
    [declare    
        지역변수 선언
    ]
    begin
        실행문   --트랜잭션 처리를 하지 않음.. 원 dml문과 같은 트랙잭션에 자동으로 속하게 된다.
    end;
    /
        
*/

 

 

-트리거 예제

-특정 테이블에서 INSERT,UPDATE,DELETE 발생 시 트리거가 발생되어

또 다른 테이블에 특정 데이터를 INSERT하는 예제

EX)회사 테이블에서 삭제시 퇴사자 테이블에 INSERT됨

create table tb_user (
    id varchar2(20),
    name varchar2(50) not null,
    constraints pk_tb_user_id primary key(id)
);

create table tb_user_log(
    no number,
    user_id varchar2(20), 
    content varchar2(4000),
    created_at date default sysdate,
    constraints pk_tb_user_log_no primary key(no),
    constraints fk_tb_user_log_user_id foreign key(user_id) references tb_user(id)
);

alter table tb_user_log drop constraints fk_tb_user_log_user_id; --제약조건을 날림(참조 만함)

create sequence seq_tb_user_log_no ;--옵션없이 기본값으로 시퀀스 생성

--의사 레코드(행레벨 트리거에서만 가능) :old :new
--                              원 dml 실행
--insert                                                  :new
--update         :old                                  :new
--delete          :old


--트리거 생성
create or replace trigger trig_tb_user_log
    after --before
    insert or update or delete on tb_user
    for each row
begin
    if inserting then --insert시 true인 속성
        insert into 
                tb_user_log(no, user_id, content)
        values (
                seq_tb_user_log_no.nextval,
                :new.id,
                :new.id ||'회원이 가입했습니다.'
                );
                
    elsif updating then --update일시 true인 속성 ,updating (컬럼명) 검사 할 수 있음! updating('name')
         insert into
                tb_user_log(no, user_id, content)
        values (
                seq_tb_user_log_no.nextval,
                :new.id, --:old.id
                :old.id ||'에서 ' || :new.name || '으로 변경했습니다.'
                );
                
    elsif deleting then --delete 일때 true인 속성
         insert into
                    tb_user_log(no, user_id, content)
            values (
                    seq_tb_user_log_no.nextval,
                    :old.id,
                    :old.id ||'회원님이 탈퇴했습니다..'
                    );
    
    end if;
end;
/

insert into tb_user (id,name) values ('honggd','홍길동');
--trigger로 실행된 dml문은 원 dml문과 같은 트랙잭션으로 묶인다.
--1.원 dml이 commint 되면 trigger의 dml문도 함께 commit 
--2.원 dml이 rollback 되면 trigger의 dml문도 함께 rollback
-->롤백하고 테이블을 확인하면 log테이블을 지우지않았는데도 같이 롤백되어서 사라짐
select * from tb_user;
select * from tb_user_log;

update 
    tb_user
set
    name = name || '길동'
where
    id = 'honggd';
    
delete from tb_user where id = 'honggd';
--ORA-02292: 무결성 제약조건(SH.FK_TB_USER_LOG_USER_ID)이 위배되었습니다- 자식 레코드가 발견되었습니다
--현재는 fk참조가 기본옵션이라 pk를 지울수가 없음..

--제품 재고(입출고 내역)을 트리거로 관리해보자.
create table tb_product(
    pcode varchar2(20),
    pname varchar2(50),
    price number,
    stock number default 0, --재고
    constraints pk_tb_product_pcode primary key(pcode),
    constraints ck_tb_product_stock check(stock>=0)
);

create table tb_product_io(
    no number,
    pcode varchar2(20),
    status char(1), --i / o
    cnt number,
    create_at date default sysdate,
    constraints pk_tb_product_io_no primary key(no),
    constraints fk_tb_product_io_pcode foreign key(pcode) references tb_product(pcode),
    constraints ck_tb_product_io_status check(status in ('I','O'))
);

create sequence seq_tb_product_io_no;

insert into tb_product values ('apple_iphone_15','아이폰15',1500000,default);
insert into tb_product values ('samsung_galaxy_23','갤럭시23',1300000,default);

select * from tb_product;
select * from tb_product_io;

---------------------------아이폰------------------------------
insert into
    tb_product_io (no, pcode, status, cnt)
values (
        seq_tb_product_io_no.nextval,
        'apple_iphone_15',
        'I',
        30
        );
        
insert into
    tb_product_io(no, pcode, status, cnt)
values (
        seq_tb_product_io_no.nextval,
        'samsung_galaxy_23',
        'O',
        15
        );        
---------------------갤럭시도--------------------------
insert into
    tb_product_io (no, pcode, status, cnt)
values (
        seq_tb_product_io_no.nextval,
        'samsung_galaxy_23',
        'I',
        18
        );
        
insert into
    tb_product_io(no, pcode, status, cnt)
values (
        seq_tb_product_io_no.nextval,
        'samsung_galaxy_23',
        'O',
        3
        );    

--------------------------------------------------------
--입출고 내역에 따라 재고를 갱신하는 트리거--
create or replace trigger trig_update_product_stock
    after
    insert on tb_product_io
    for each row
begin
    if : new.status = 'I' then
        --입고시 stock + :new.cnt
        update 
            tb_product
        set
            stock = stock + :new.cnt
        where
            pcode = :new.pcode;
    else
        --출고시 stock - :new.cnt
        update 
            tb_product
        set
            stock = stock - :new.cnt
        where
            pcode = :new.pcode;
    end if;
end;
/