*44일차 (2) - 트리거(trigger)

-- 사용자에게 부서명을 입력받고 해당부서원을 모두 조회하는 프로시저 proc_print_emp_by_dept 작성.
-- 익명블럭에서 호출 proc_print_emp_by_dept('총무부');
-- 사번 사원명 부서명

-- 프로시져 선언
create or replace procedure proc_print_emp_by_dept(
    p_dept_title department.dept_title%type
)
is
    cursor cs_emp_by_dept(pc_dept_title department.dept_title%type)
    is
    select
        e.emp_id, e.emp_name, d.dept_title
    from
        employee e left join department d
            on e.dept_code = d.dept_id
    where
        d.dept_title = pc_dept_title;
begin

    for erow in cs_emp_by_dept(p_dept_title) loop
        dbms_output.put_line(erow.emp_id || '   ' || erow.emp_name || '     ' || erow.dept_title);
    end loop;

end;
/

-- 익명블럭에서 호출
begin
    proc_print_emp_by_dept('총무부');
end;
/

 

 

@TRIGGER

• 연쇄반응
• 특정이벤트, DDL, DML등이 실행되었을때 자동적으로 어떤 처리가 일어나도록 하는 객체

*종류

1. Logon / Logoff trigger
2. DDL Trigger
3. DML Trigger - insert/update/delete 구문이 실행되었을 때 트리거에 작성된 내용 실행

 

--회원탈퇴 시 탈퇴회원 테이블에 자동으로 추가

--프로필 변경시 변경내역을 로그테이블에 추가

 

/*

    create [or replace] trigger 트리거명

        before | after

        insert or update or delete on 테이블명

        [for each row]

    declare        

        -- 지역변수 선언

    begin

        -- 실행코드

    end;

    /

    - before | after : 원 DML 실행전/후 트리거 설정

    - for each row

        - 생략시 문장레벨트리거 : 원DML문당 1번 실행

        - 작성시 행레벨트리거 : 처리되는 행마다 실행

    - 행레벨 트리거시 의사레코드(pseudo record)

   

                dml실행전   dml실행후

    --------------------------------------------------------

    insert        null       :new.컬럼명

    update       :old.컬럼명  :new.컬럼명

    delete       :old.컬럼명   null

*/

select * from tb_user;

create table tb_user(
    no number,
    name varchar2(100) not null,
    constraint pk_tb_user_no primary key(no)
);
create sequence seq_tb_user_no;

create table tb_user_log(
    no number,
    log varchar2(4000) not null,
    log_date date default sysdate,
    constraint pk_tb_user_log_no primary key(no)
);
create sequence seq_tb_user_log_no;


-- trigger
create or replace trigger trig_tb_user_log
    before
    insert or update or delete on tb_user
    for each row
begin
    -- 상태를 나타내는 boolean형 키워드
    -- inserting - insert시 true
    -- updating('컬럼명') - 특정컬럼을 수정하면 true
    -- deleting - delete시 true
    
    if inserting then
        insert into 
            tb_user_log(no, log)
        values(
            seq_tb_user_log_no.nextval,
            :new.no || '번 ' || :new.name || '님이 회원가입했습니다.'
        );
    elsif updating then
        insert into 
            tb_user_log(no, log)
        values(
            seq_tb_user_log_no.nextval,
            :new.no || '번 회원이 ' || :old.name || '에서 ' || :new.name || '으로 이름변경했습니다.'
        );
    
    elsif deleting then
        -- 1번회원이 탈퇴했습니다.
        insert into 
            tb_user_log(no, log)
        values(
            seq_tb_user_log_no.nextval,
            :old.no || '번 회원이 탈퇴했습니다.'
        );
    end if;
    
    -- 트랜잭션처리 하지 않는다.
    -- 트리거의 트랜잭션은 원DML문에 따라 commit 또는 rollback된다.
end;
/

 

--원 dml 실행

insert into
    tb_user
values(
    seq_tb_user_no.nextval,
    '홍길동'
);

update 
    tb_user
set
    name = '강길동'
where
    no = 2;

delete from
    tb_user
where
    no = 2;

commit;
rollback;

select * from tb_user;
select * from tb_user_log;

 

ex)

--트리거를 이용한 재고관리

--상품테이블(재고)

--입출고테이블

create table tb_product(
    pcode varchar2(20),
    pname varchar2(50),
    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),
    status char(1), -- 입고 I, 출고 O
    amount number,
    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)
);

create sequence seq_tb_product_io_no;

-- 상품데이터
insert into 
    tb_product
values (
    'apple_iphone_x', '아이폰X', 1000000, default
);
insert into 
    tb_product
values (
    'samsung_galaxy_20', '갤럭시20', 1500000, default
);

-- 입출고 데이터
insert into tb_product_io values(
    seq_tb_product_io_no.nextval, 'apple_iphone_x', 'I', 10, default
);
insert into tb_product_io values(
    seq_tb_product_io_no.nextval, 'apple_iphone_x', 'O', 3, default
);

select * from tb_product;
select * from tb_product_io;

--트리거 생성

create or replace trigger trig_tb_product_stock
    before
    insert on tb_product_io
    for each row
begin
    if :new.status = 'I' then
        -- 입고시 + amount
        update
            tb_product
        set
            stock = stock + :new.amount
        where
            pcode = :new.pcode;
    else 
        -- 출고시 - amount
        update
            tb_product
        set
            stock = stock - :new.amount
        where
            pcode = :new.pcode;
    end if;

end;
/