트리거 Trigger

트리거 Trigger

 

트리거는 데이터베이스 시스템에서 데이터의 입력, 갱신, 삭제 등의 이벤트가 발생하 때마다 

자동적으로 수행되는 사용자 정의 프로시저이다.

트리거는 TABLE과는 별도로 DATABASE에 저장된다.

트리거는 VIEW에 대해서가 아니라 TABLE에 관해서만 정의될 수 있다.

트리거는 SQL의 제약조건 방법을 통해 명시할 수 없는 무결성 제약조건을 구현하고, 

관련 테이블의 데이터를 일치시킬 때 주로 사용된다.

제약조건과 함께 데이터 무결성을 지키는 하나의 방법으로써 특정 이벤트에 대해서 

연속적으로 자동 동작하는 특수한 형태의 저장 프로시저라고 볼 수 있다.

일반적으로 사용처는 많지만 예를 들어보자면 "입고"테이블에 새로운 제품이 들어왔을 때

그 수량을 "재고"테이블에 자동으로 반영되게 하는 경우가 있다.

 

트리거란 특정 테이블의 데이터에 변경이 가해졌을 때 자동으로 수행되는
[저장 프로시저]라고 할 수 있다.
앞서 배운 저장 프로시저는 필요할 때마다 사용자가 직접
 EXECUTE 명령어로 호출해야 했다.
하지만 트리거는 이와 달리 테이블의
데이터가 INSERT, UPDATE, DELETE 문에 의해 변경되어질 때
[ 자동으로 수행되므로 이 기능을 이용하며 여러 가지 작업 ] 을 할 수 있다.
이런 이유로 트리거를 사용자가 직접 실행시킬 수는 없다.

 

트리거의 종류

TRIGGER의 종류로는 문장 트리거와 행 트리거로 나눌 수 있다.

 

 

문장 트리거

트리거가 설정된 테이블에 트리거 이벤트가 발생하면 많은 행에 대해 변경 작업이 발생하더라도 

오직 한 번만 트리거를 발생시키는 방법이다. 

(컬럼값이 변화가 생길 때마다 스스로 알아서 실행된다. - FOR EACH ROW 옵션은 사용하지 않는다.)

 

행 트리거

조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러 번 수행하는 방법으로 

[FOR EACH ROW WHEN 조건] 절로 정의된다.
컬럼의 데이터 행이 변화가 오면 실행되며, 변경 후의 행은 OLD와 NEW를 통해 가져올 수 있다

 

구문

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} triggering_event [OF column1, . . .] ON table_name
[FOR EACH ROW [WHEN trigger_condition]
trigger_body;

BEFORE | AFTER  ->  

DML 문장이 실행되기 전에 TRIGGER 를 실행할 것인지 실행된후에 TRIGGER 를 실행할 것인지를 정의

BEFORE -> 테이블에서 DML 실행되기 전에 트리거가 동작
AFTER ->   테이블에서 DML 실행후에 트리거 동작

 

trigger_name -> TRIGGER 의 식별자

triggering_event ->  TRIGGER 를 실행하는 DML(INSERT,UPDATE,DELETE)문을 기술한다.

OF column -> TRIGGER 가 실행되는 테이블에서 COLUMN 명을 기술한다.

table_name  ->  TRIGGER가 실행되는 테이블 이름

FOR EACH ROW  ->

이 옵션을 사용하면 행 레벨 트리거가 되어 triggering 문장에 의해 영향받은 행에 대해 각각 한번씩 실행

사용하지 않으면 문장 레벨 트리거가 되어 DML 문장 당 한번만 실행된다.

 

 

구문2

CREATE [ OR REPLACE ] TRIGGER 트리거명
BEFORE | AFTER
[ 동작(INSERT, UPDATE, DELETE) ] ON 테이블명 
[ REFERENCING  NEW | OLD  TABLE AS 테이블명 ]
[ FOR EACH ROW ]
[ WHEN 조건식 ]
트리거 BODY문

 

OR REPLACE:  생성할 트리거와 같은 이름을 가지고 있어도 무시하고 새로운 것으로 갱신하는 것이므로

사용할 때 주의해야 한다.

* AFTER: 테이블이 변경된 후에 트리거가 실행되는 옵션이다.

* BEFORE: 테이블이 변경되기 전에 트리거가 실행되는 옵션이다.

* 동작옵션: 각각 INSERT, UPDATE, DELETE가 실행될 때 트리거를 실행시키는 것이다.

* NEW: 새로 추가되거나 변경된 후의 값에 트리거가 적용된다. (INSERT : 입력할 값, UPDATE: 수정할 값)

* OLD: 변경 전의 값에 트리거가 적용된다. (UPDATE: 수정 전 값, DELETE: 삭제할 값)

* WHEN: 트리거가 실행되면서 지켜야 할 조건을 지정한다. (조건에 맞는 데이터만 트리거 실행)

* 트리거 BODY문 : 트리거의 본문 코드를 입력하는 부분이다.
- BEGIN으로 시작해서 END로 끝나는데, 적어도 하나 이상의 SQL문이 있어야 한다. 그렇지 않으면 오류가 발생한다.
- 변수에 값을 치환할 때는 예약어 SET을 사용한다.

 

 

 TRIGGER 에서 OLD 와 NEW

--> 행 레벨 TRIGGER 에서만 사용할 수 있는 예약어로 트리거 내에서 현재 처리되고 있는 행을 액세스할 수 있다.

 

 

 

트리거 사용 예시

create or replace trigger tri_01
after insert on tri_emp
BEGIN -- 자동 동작할 내용
    DBMS_OUTPUT.PUT_LINE('신입사원 입사');
END;

 

 

create or replace trigger tri_02
after update on tri_emp
BEGIN
  DBMS_OUTPUT.PUT_LINE('신입사원 수정');
END;

 

 

create or replace trigger tri_03
after delete on tri_emp
BEGIN
  DBMS_OUTPUT.PUT_LINE('신입사원 삭제');
END;

datagrip에서는 해당 테이블안에서 트리거 확인가능.

oracle sql developer에서는 스키마안의 폴더에서 확인가능.

 

 

old,new 사용예시

CREATE OR REPLACE TRIGGER UPDATE_NAME 
AFTER UPDATE 
OF NAME

ON SUMMONER_TB 
REFERENCING NEW AS  N OLD AS  O
FOR EACH ROW
BEGIN
UPDATE GAME_TB SET NAME =: N.NAME WHERE NAME =: O.NAME;
END;

summoner_tb라는 테이블에서 name이라는 컬럼 변경시 해당 트리거 동작

 

새로 추가된 값에 대한 접근을 N으로 , 이전 값에 대한 접근을 O 로 한다.

 

summoner_tb라는 테이블에서 name이라는 컬럼 변경시  game_tb라는 테이블에서 이전이름을 새로운 이름으로 바꾼다.

 

 

old,new 사용예시2

 

따로 설정없이 :old, :new로 사용가능

UPDATE 는 :OLD 와 :NEW 를 모두 정의한다.

:OLD는 INSERT 문에 의해 정의되지 않고  (새로 생성할건데 이전거는 당연히 없으니까)

:NEW 는 DELETE 에 대해 정의되지 않는다. (삭제하는건데 새로운건 당연히 없으니까)

create or replace trigger emp_audit_tr
 after insert or update or delete on emp2
 for each row
begin
 if inserting then
      insert into emp_audit
      values(emp_audit_tr.nextval, user, 'inserting', sysdate, :old.deptno, :new.deptno);
 elsif updating then
    insert into emp_audit
    values(emp_audit_tr.nextval, user, 'updating', sysdate, :old.deptno, :new.deptno);
 elsif deleting then
    insert into emp_audit
    values(emp_audit_tr.nextval, user, 'deleting', sysdate, :old.deptno, :new.deptno);
 end if;
end;

 

create or replace trigger tri_order2
before insert on tri_order
for each row
BEGIN
  IF(:NEW.ord_code) not in('desktop') THEN
     RAISE_APPLICATION_ERROR(-20002, '제품코드 오류');
  END IF;
END;

 

--입고 데이터 들어오면 같은 데이터를 재고 입력
create or replace trigger insert_t_01
after insert on t_01
for each row
BEGIN
  insert into t_02(no, pname)
  values(:NEW.no ,:NEW.pname);
END;

 

-- 입고 제품이 변경 (재고 변경)
create or replace trigger update_t_01
after update on t_01
for each row
BEGIN
  update t_02
  set pname = :NEW.pname
  where no = :OLD.no;
END;

 

--입고 데이터 delete from t_01 where no =1 삭제 되면 재고 삭제
create or replace trigger delete_tri_01
after delete on t_01
for each row
BEGIN
  delete from t_02
  where no=:OLD.no;
END;

 

before 사용

--before 트리거의 동작시점이 실제 tri_order 테이블 insert 되기 전에
--트리거 먼저 동작 그 이후 insert 작업
create or replace trigger trigger_order
before insert on tri_order
BEGIN
  IF(to_char(sysdate,'HH24:MM') not between '11:00' and '16:00') THEN
     RAISE_APPLICATION_ERROR(-20002, '허용시간 오류 쉬세요');
  END IF;
END;