PL/SQL (3) 트리거

본 포스팅은 Oracle 11g를 이용하고있으며, Tool은 Toad for Oracle 13.4 를 이용했습니다.

 

트리거

🔎 insert, update, delete의 DML문이나 DDL문의 실행을 데이터베이스에서는 특정 이벤트가 발생되었다고 하는데, 이런 이벤트가 발생하면 자동으로 정해진 동작 을 실행하는 데이터베이스 객체를 트리거라고 함 

 

• 서브 프로그램 단위의 하나인 트리거는 테이블, 뷰, 스키마 또는 데이터베이스에 관련된 PL/SQL 블록(또는 프로시저)으로 관련된 특정 사건(event)이 발생될 때마다 자동으로 해당 PL/SQL 블록이 실행 된다.
• 사전적인 의미로 보자면 방아쇠 를 뜻하는데, 방아쇠를 당기는 이벤트가 발생하면 총알이 발사된다는 의미처럼 오라클에서도 어떤 이벤트가 발생할 경우 연관된 다른 작업이 자동으로 수행된다는 뜻이다.
• 예) 입고 테이블에 상품이 입고되면 (insert 이벤트 발생) 재고 테이블에 자동으로 재고가 증가하게 만드는 것이다.
• 트리거는 데이터베이스 내에 오브젝트로서 저장되어 관리된다.
• 사용자가 지정해서 실행할 수 없으며, 오직 트리거 생성시 정의한 특정 사건(event)에 의해서만 묵시적으로 자동실행이 이루어진다.

❗ 주의하자
1) 트리거는 잘 사용하면 아주 편리한 기능이지만, 잘못 생성 하거나 너무 많이 생성하게 되면 관련 오브젝트끼리 복잡한 종속 관계가 되어 성능 저하가 생길 수도 있다.

2) 데이터베이스 전체의 트리거 조작은 administer database trigger 시스템 권한이 필요하다.
( 트리거 생성, 수정, 삭제 시 create trigger, alter trigger, drop trigger의 권한이 필요)

🔎 트리거 관련 권한

스키마에서 트리거를 생성, 변경 및 삭제할 수 있는 권한

grant create trigger to 유저;

grant alter any trigger to 유저;

grant drop any trigger to 유저;

 

데이터베이스에서 트리거를 생성할 수 있는 권한

grant administer database trigger to 유저;

 

주요 트리거 유형

✔ DML 트리거

1) 문장 트리거 : 영향을 받는 행이 전혀 없더라도 트리거가 한번은 실행 된다.

 

2) 행 트리거 : 테이블이 트리거 이벤트의 영향을 받을때마다 실행되고, 트리거 이벤트의 영향을 받는 행이 없을 경우에는 실행되지 않는다.

• 행 트리거로 생성하려면 For Each Row 라는 구절을 사용하면 된다.
• 행 레벨 트리거가 실행될 때 PL/SQL 런타임 엔진은 두 개의 데이터 구조를 생성하고 채운다.

OLD : 트리거가 처리한 레코드의 원래 값을 저장

NEW  : 새 값을 포함

→ 위 두 가지 값을 사용하여 변경 전과 변경 후의 데이터를 조작할 수 있다.

 

• 사용할 때는 반드시 위 값 앞에 콜론 ( : )  을 붙여서 사용해야 한다. 예) :OLD.value, :NEW.no 등
• 행 레벨 트리거에서만 사용 가능 하다.

 

DML이 아닌 트리거

1) DDL 이벤트 트리거 : DML 트리거와 거의 동일하지만 이와는 다른 점이 바로 트리거를 활용하여 DDL 작업을 한다.

2) 데이터베이스 이벤트 트리거 : 데이터베이스 내에서 생기는 일들을 관리하기 위해서 생성하는 트리거

 

트리거 구조

create or replace trigger 트리거 이름
트리거 실행시점 [before/after]
이벤트 [insert | update | delete]
on{테이블이름 | 뷰이름 | 스키마 | 데이터베이스}
[for each row] -- 생략하면 문장 레벨 트리거
begin
트리거 몸체
end;

 

👌 사용자가 만든 트리거 정보 조회

select * from user_triggers;

 

예제

문장 레벨 트리거

1) 부서 테이블(dept)에 insert문 실행 후 메세지를 출력하는 트리거

create or replace trigger tr_dept_insert
after insert on dept
begin
dbms_output.put_line('정상적으로 입력되었습니다.');
end;

 

dept 테이블에 insert 이벤트 발생 후 tr_dept_insert 트리거 실행된 모습

 

 

2) 테이블에 데이터를 입력할 수 있는 시간 지정하기

 

[1] 테스트용으로 t_order 테이블을 하나 만들자.

create table t_order(
no number,
ord_code varchar2(10),
ord_date date
);

[2] 입력시간이 12:30 ~ 12:50 일 경우만 입력을 허용하고, 그 외 시간일 경우는 에러를 발생시키는 트리거 작성

create or replace trigger tr_check_time
before insert on t_order
begin
if to_char(sysdate, 'HH24:mi') not between '12:30' and '12:50' then
raise_application_error(-20009, '12:30 ~ 12:50 일 경우만 입력가능');
end if;
end;

 

행 레벨 트리거

1) 테이블에 입력될 데이터 값을 지정하고, 그 값 외에는 에러를 발생시키는 트리거

 

[1] 위에서 생성했던 tr_check_time 트리거를 삭제해보자. (drop trigger 을 사용해보고자 .. )

drop trigger tr_check_time;

 

[2] 제품 코드가 'C100'인 제품이 입력될 경우 입력을 허용하고,

나머지 제품은 모두 에러를 발생시키는 트리거 작성

create or replace trigger tr_code_check
before insert on t_order
for each row    -- (1)
begin
if :new.ord_code !='C100' then --(2)
raise_application_error(-20010, '제품코드가 C100인 제품만 입력 가능!');
end if;
end;

(1)  행 레벨 트리거 : (예를들어 10번 업데이트했다 , 그럼 10번을 수행함) 즉 , 행의 갯수만큼 영향 받은 행의 갯수만큼 진행한다

(2)  새로 입력하는 ord_code가 C100이 아니면 error가 뜨게 한다.

 

 

 

트리거 수행

 

2) 기존 테이블(t_test1)에 데이터가 업데이트 될 때 기존 내용을 백업테이블(t_test1_bak)로 이동시키는 트리거

 

[1] t_test1 테이블을 만들어보고, t_test1과 똑같은 테이블 t_test1_bak 테이블도 생성시키자.

create table t_test1(
no  number,
name    varchar2(10)
);

create table t_test1_bak
as
select * from t_test1;

 

[2] 트리거 실행을 위해 미리 t_test1에 임의로 데이터들을 집어넣자.

insert into t_test1 values(1, 'AAA');
insert into t_test1 values(2, 'BBB');

commit;

 

[3] 기존에 있었던 정보를 업데이트 후에 원래 있었던 정보를 backup 테이블에 두는 트리거 작성

create or replace trigger tr_backup_t_test1
after update on t_test1 --(1)
for each row
begin
insert into t_test1_bak(no, name)
values(:old.no, :old.name); --(2)
end;

(1) 이벤트가 발생하는 테이블

(2) 기존 정보들(이벤트가 일어나는 기존 테이블)

 

[4] 결과 확인

update t_test1
set name='CCC'
where no=1;

update t_test1
set name='DDD'; --(1)

(1) 기존에 있던 행이 2개 이므로행 레벨 트리거가 2번 실행 되면서 2번 bak 테이블 에 insert 된다.

 

 

t_test1 테이블은 업데이트가 되었으며 기존에 있었던 데이터 값들은 t_test1_bak 테이블에 입력 되었다.

 

 

3) 기존 테이블(t_test2)의 데이터가 삭제될 때 기존 내용을 백업 테이블(t_test2_bak)로 이동시키며 이때 백업 테이블에 삭제한 시간, 삭제 전 데이터를 모두 기록하는 트리거 작성

 

 

[1] t_test2 테이블과 t_test2_bak 테이블 생성

create table t_test2(
no number,
name varchar2(10)
);

create table t_test2_bak(
no  number,
name    varchar2(10),
regdate date    default sysdate
);

 

[2] 트리거 실행을 위해 미리 t_test2에 임의로 데이터들을 집어넣자.

insert into t_test2 values (1, 'AAA');
insert into t_test2 values (2, 'BBB');
insert into t_test2 values (3, 'CCC');
insert into t_test2 values (4, 'DDD');

commit;

 

[3] t_test2 테이블의 데이터를 삭제했을때 t_test2_bak 테이블에 insert가 되는 트리거 작성

create or replace trigger tr_backup_t_test2
after delete on t_test2
for each row
begin
insert into t_test2_bak
values(:old.no, :old.name, sysdate);
end;

 

[4] 결과 확인

delete from t_test2
where no!=4;

t_test2 테이블에서 삭제했던 값들이 t_test2_bak 테이블에 입력 되었다.

 

 

4) 기존 테이블(t_test3)의 추가, 변경, 삭제된 내용을 별도의 로그 테이블을 생성하여 기록을 남기도록 트리거 작성

 

[1] t_test3 테이블과, t_test3_history 테이블 생성

create table t_test3(
no number,
name varchar2(10)
);

create table t_test3_history(
o_no    number,     --변경전이나 삭제된 데이터를 저장하는 컬럼은 o로 시작
o_name  varchar2(10),
n_no    number,     --변경 후나 추가된 데이터를 저장하는 컬럼은 시작은 n으로 정의
n_name  varchar2(10),
who     varchar2(30),   --어떤 사용자가 어떤 작업을 언제 했는지 정보를 저장
regdate date    default sysdate,
chk     char
);

 

[2] 미리 t_test3 테이블에 데이터 입력

insert into t_test3 values(1, 'AAA');
insert into t_test3 values(2, 'BBB');

commit;

 

[3] 트리거 작성 - 수정, 입력, 삭제 순

create or replace trigger tr_log_t_test3
after update on t_test3
for each row
begin
insert into t_test3_history(o_no, o_name, n_no, n_name, who, chk)
values(:old.no, :old.name, :new.no, :new.name, user, 'U'); --(1)
end;

(1) --U = update , who엔 user만 적어줘도 됨

 

create or replace trigger tr_insert_t_test3
after insert on  t_test3
for each row
begin
insert into t_test3_history(o_no, o_name, n_no, n_name, who, chk)
values(:old.no, :old.name, :new.no, :new.name, user, 'I');
end;

create or replace trigger tr_delete_t_test3
after delete on t_test3
for each row
begin
insert into t_test3_history(o_no,o_name,n_no,n_name,who,chk)
values(:old.no, :old.name,:new.no,:new.name,user,'D');
end;

 

 

실습

[1] 상품이 입고되면 재고 테이블에서 자동으로 해당 상품의 재고 수량과 금액이 증가되는 트리거 작성 하기

create or replace trigger tr_prt_pd_in
after insert on prt_pd_in
for each row
begin
update prt_pd_stock
set pd_qt = (pd_qt+:new.pd_qt), money = (money+:new.money)
where pd_code=:new.pd_code;
end;

 

[2] 상품이 판매되면 재고 테이블에서 자동으로 해당 상품의 재고 수량과 금액이 감소되는 트리거 작성 하기

create or replace trigger tr_prt_pd_sell
after insert on prt_pd_sell
for each row
begin
update prt_pd_stock
set pd_qt = (pd_qt-:new.pd_qt), money = (money-:new.money)
where pd_code=:new.pd_code;
end;

 

 

---

트리거를 다시 보니까 뭔가 새롭당 😃

다음은 트랜잭션에 대해 포스팅 해보도록 할것이다.

원래는 커서와 같이 트리거를 포스팅하려고 했는데, 내용상 커서는 프로시저와 함께 포스팅 하는게 좋을것 같아서 프로시저 포스팅을 다시 수정해서 올렸다.