오라클에서 트리거 사용

활용

 1. 데이터 무결성의 강화

   - 전체 데이터베이스에 걸쳐 영향 받은 테이블을 연속적으로 변경함으로서 데이터 무결성을 강화하는데 사용될 수 있다.

   - 중복되었거나 파생된 데이터에 대해 트리거를 사용하는 것이 좋다.

2. 참조 무결성의 강화

   - 연속적인 삭제 / 갱신이 일어날 필요가 있을 때 적절한 작업을 하도록 보장하는데 유용하다.

   - FOREIGN KEY, REFERENCE 제약을 사용할 수도 있다.

   - 자신의 오류 메시지를 정의할 수 있다.

3. 업무 규칙의 설정

   - 제약에 의해 표현될 수 있는 것보다 복잡한 제약을 표현할 수 있다.

4. 감사기능의 확장

   - 상세한 변경 내역을 추적하려면, 트리거를 적용하거나 Log Miner를 이용해 아카이브 로그를 뒤지는 수 밖에 없음

   - 감사 목적으로 적용되는 트리거는 성능적으로 심각한 부담을 줍니다

고려사항

        - 트리거는 각 테이블에 최대 3개까지 존재할 수 있다.

          UPDATE, INSERT, DELETE 트리거가 각각 하나씩 존재할 수 있다.

          이미 트리거가 정의된 작업에 대해 다른 트리거를 정의하면 기존의 것을 대체한다.

        - 테이블의 소유자만이 트리거를 생성하거나 삭제할 수 있다.

        - 뷰나 임시 테이블을 참조할 수 있으나 뷰나 임시 테이블에 대해 생성할 수 는 없다.

        - 데이터 무결성이나 업무 규칙 처리에만 사용하는 것이 좋다.

        - 트리거의 동작은 이를 삭제하지 않는 한 계속된다.

        - 테이블의 삭제는 관련 트리거의 삭제를 유발한다

트리거에서 사용할 수 없는 SQL 명령들

        - CREATE... /DROP ...

        - ALTER TABLE, ALTER DATABASE

        - TRUNCATE TABLE

        - GRANT, REVOKE

        - UPDATE STATISTICS

        - RECONFIGURE

        - LOAD DATABASE, LOAD TRANSACTION

        - DISK ...

        - SELECT INTO

트리거의 성능

   - 오버헤드는 적다.

   - 트리거를 실행하는데 걸리는 대부분의 시간은 다른 테이블을 참조 하는데 사용된다.

   - 트리거에 의해 참조되는 다른 테이블의 위치가 작업 시간을 결정 짓는다.

   - inserted 테이블과 deleted 테이블은 논리적 테이블이고 매우 작으며 항상 메모리에 있다.

트리거의 제한

- 트리거는 트랜잭션 제어문(COMMIT, ROLLBACK, SAVEPOINT)을 사용할 수 없습니다.

- 트리거는 트리거링 문장의 실행부분으로써 실행되고 트리거링 문장과 같은 트랜잭션에 있습니다.

- 트리거링 문장이 COMMIT, ROLLBACK될때 트리거의 작업도 COMMIT, ROLLBACK 됩니다.

트리거의 종류

  - 문장 트리거 ; 전체 transaction 작업에 대해 1번발생되는 트리거 ,default

  - 행 트리거 ; 각 행에 대해서 트리거가 발생된다. (for each row)

 

형식

- 트리거 생성

create[or replace]   trigger 트리거명  before  insert

                                                 after    delete

                                                           update [of 컬럼….] on 테이블명

 

                [for each row]행트리거    

 [when] 조건    

 begin    

    트리거의 내용   

end;    

 

- 트리거의 삭제 : drop trigger 트리거명

- 트리거의 활동을 enabled/disabled

- alter trigger 트리거명 {enable/disable}

- BEFORE : INSERT, UPDATE, DELETE문이 실행되기 전에 트리거가 실행됩니다.

- AFTER : INSERT, UPDATE, DELETE문이 실행된 후 트리거가 실행됩니다.

- trigger_event : INSERT, UPDATE, DELETE 중에서 한 개 이상 올 수 있습니다.

- FOR EACH ROW : 이 옵션이 있으면 행 트리거가 됩니다.

행 trigger 　

 · 테이블이 Triggering 문장에 의해 영향을 받을 때마다 실행

 · 영향을 받는 각 행에 대해 한번씩 실행

 · Trigger 작업의 코드가 Triggering 명령문이나 영향을 받는 행에 의해 제공되는 데이터에 따라 다를

  경우에 유용 

명령문 trigger 　

 · 테이블에서 Triggering 명령문에 의해 영향을 받는 행 수에 (영향을 받는 행이 없다 해도) 관계 없이 트리거링 명령문 대신 한 번 실행

 · 트리거 작업의 코드가 Triggering 명령문이나 영향을 받은 행에 의해 제공되는 데이터에 종속되지 않을 때 유용 

Before trigger Vs. After trigger 　

· Before trigger : triggering문 앞에 trigger 작업 실행

      ▶ trigger 작업이 triggering 문장의 종료 여부를 결정할 수 있을 때

          → trigger 작업에서 발생된 예외 경우에서의 우발적인 롤백과 불필요한 처리제거

      ▶ triggering 삽입 혹은 수정문을 종료하기 전에 명확한 칼럼값들을 조정하기 위해 사용

· After trigger : 실행된 triggering문 앞에 trigger 작업 실행

      ▶ trigger작업실행 이전에 triggering문을 종료하기를 원할 때

      ▶ Before trigger가 미리 제시되면 같은 triggering문에서 다른 작업 수행 

결합에 의한 네가지 타입의 Trigger 　

· Before 명령문 trigger : triggering문의 실행전에 trigger 작업 실행

· Before 행 trigger :

      ▶ triggering문에 의해 영향 받는 각 행을 수정하기 전에

      ▶ 해당 무결성 제약 조건을 검사하기 전에

        → trigger 제한 사항에 위배되지 않으면 실행

· After 명령문 trigger : triggering문을 실행하여 파생된 무결성 제약 조건을 적용한 후 트리거 작업이 수행됨.

· After 행 trigger :

      ▶ triggering문에 의해 영향을 받는 각 행을 수정하고 가능한 해당 무결성 제약 조건을 적용한 후 트리거 제한 사항에 위배되지 않는 현재 행에 대해 수행됨.

      ▶ Before 행 trigger와 달리 행을 잠금.

 

 실행 모델과 무결성 제약 조건 　

 - 제약 조건 검사와 복합 trigger들의 적절한 실행 순서를 유지 관리하기 위해 

  · 실행 모델 Ⅰ

      (1) 문장에 알맞은 모든 Before문장 trigger 실행

      (2) SQL문에 의해 영향받는 각 행을 위한 루프

          1)문장에 알맞은 모든 Before 행 trigger 실행

          2)행을 lock한 후 바꾸고 무결성 제한 채킹을 실행(lock은 트랜젝션이 완료될 때까지 해제되지

            않음)

          3)문장에 알맞은 모든 After 행 trigger를 실행

      (3) 유예된 무결성 제한 점검 완료

      (4) 문장에 알맞은 모든 After 문장 trigger를 실행

 · 실행 모델 Ⅱ

      (1) 오라클 SQL 문을 지시

      (2) BEFORE 행 트리거를 실시

      (3) AFTER 문장 트리거는 BEFORE 행 트리거에서 update함으로써 실시됨

      (4) AFTER 문장 트리거의 문장들을 실행

      (5) 테이블상의 무결성 제한은 체크된 AFTER문장 트리거에 의해 바뀜

      (6) BEFORE 행 트리거의 문장들을 실행

      (7) 테이블상의 무결성 제한은 체크된 BEFORE 행 트리거에 의해 바뀜

      (8) SQL문 실행

      (9) SQL문으로부터 무결성 제한을 체크

예제 1

Aborting Triggers with Error

Triggers can often be used to enforce contraints. The WHEN clause or body of the trigger can check for the violation of certain conditions and signal an error accordingly using the Oracle built-in function RAISE_APPLICATION_ERROR. The action that activated the trigger (insert, update, or delete) would be aborted. For example, the following trigger enforces the constraint Person.age >= 0:

create table Person (age int);

CREATE TRIGGER PersonCheckAge

AFTER INSERT OR UPDATE OF age ON Person

FOR EACH ROW

BEGIN

    IF (:new.age < 0) THEN

        RAISE_APPLICATION_ERROR(-20000, 'no negative age allowed');

    END IF;

END;

.

RUN;

If we attempted to execute the insertion:

insert into Person values (-3);

we would get the error message:

ERROR at line 1:

ORA-20000: no negative age allowed

ORA-06512: at "MYNAME.PERSONCHECKAGE", line 3

ORA-04088: error during execution of trigger 'MYNAME.PERSONCHECKAGE'

and nothing would be inserted. In general, the effects of both the trigger and the triggering statement are rolled back.

예제 2

The following template shows how to raise an error when a DML statement violates a business rule:

CREATE OR REPLACE TRIGGER secure_del_trigger

   BEFORE DELETE

   ON emp

   FOR EACH ROW

DECLARE

   unauthorized_deletion   EXCEPTION;

BEGIN

   IF <your business rule is violated> THEN

     RAISE unauthorized_deletion;

   END IF;

EXCEPTION

   WHEN unauthorized_deletion

   THEN

      raise_application_error (-20500,

        'This record cannot be deleted');

END;

/

참고 1

1. MUTATING ERROR란 무엇인가?

어느 TABLE에 DML(INSERT, UPDATE, DELETE 등)이 실행될 때마다 프로그램에

구애받지 않고 특정 작업을 수행하려할 때 database trigger를 사용한다.

예)EMP table에 data insert, update, delete 시 부서별 평균 급여 table에

updating 하는 경우.

이 경우 trigger를 사용하지 않고 같은 작업을 하려면 평균 급여를 구하는

PL/SQL program을 개발하여 EMP 테이블에 action이 발생 시마다 call하여 사용

하든가, 아니면 각 action 발생 후 동일한 routine을 반복 수행시켜야 한다.

이 때 만일 user가 EMP table에 update 시마다 EMP table에 어떤 처리를 수행

하는 trigger를 만든다면 원치 않는 결과를 일으킬 수 있고 OS memory가 소진

될 때까지 trigger가 trigger를 recursive하게 fire시켜 마치 looping

program과 같은 상황을 초래할 수도 있다.

이러한 trigger를 recursive trigger라 부르며 이런 불상사를 막기 위해

ORACLE은 EMP table에 row trigger를 만들어 원천적으로 trigger 내에서

EMP table을 아예 access 할 수 없도록 하고 있고, 이와 같은 원칙에 위배될

경우 발생되는 error를 mutating error 라고 부른다.

이 경우 user가 trigger를 만든 후 DML(insert, update, delete)을 수행 시

"ORA-4091:table SCOTT.EMP is mutating, trigger/function may not see

it." 와 같은 error를 만나게 된다.

2. ERROR가 발생하는 조건.

TRIGGER에는 다음과 같은 두 종류가 있다.

*row trigger - 프로그램에서 한 row 단위로 처리 시 처리할 때마다

fire되는 trigger.

*statement trigger - 프로그램 당 한번만 fire되는 trigger.

위와 같으므로 만일 application에서 한 row만 처리한다면 두 type에는 차이가

없고 여러 row를 처리할 경우 두 type 간의 차이가 발생한다.

Statement trigger는 일부 제한은 있으나 원칙적으로 mutating error를 발생

시키지 않는다.

Row trigger는 하나의 row 처리 후 해당 table에 대한 계속된 row 처리가 있을

수 있으므로 작업이 완료되기까지 해당 table을 access하는 것이 금지되지만

statement trigger는 일단 하나의 statement가 완료되었다는 보장을 할 수

있으므로 mutating의 기본 속성인 "현재 변화되고 있는 table" 이라는 범위에

들지 않는다.

따라서, mutating error는 row trigger에서의 제한 사항이라 해도 무리가 없다.

3. 해결 방법.

위에서 보았듯 mutating error를 피해 나가려면 statement trigger를 사용하면

어려움이 없으나 statement trigger에서는 row trigger에서와 같이 row 단위로

변경 전 후 column data를 handling할 수 없다는 단점이 있다.

즉 :new.column, :old.column을 사용하지 못한다.

이와 같은 문제로 인하여 row trigger를 사용 시는 temp table 이나 PL/SQL

table을 이용하여 피해갈 수가 있다.

참고 2

INSTEAD OF Trigger

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

1. 개념

INSTEAD OF trigger는 Oracle8에서 새로이 소개된 방법으로, DML문장에 의해

직접 변경할 수 없는 view를 변경하기 위해 사용된다. 즉, base table이 fire

하는 trigger를 생성하는 것이 아니고 view를 대상으로 trigger를 생성하여

view에 대한 DML문장을 수행시 대신 trigger가 fire되어 base table을 직접

변경하게 되는 것이다.

기본적으로 DML이 불가능한 view는 다음 사항들을 포함하고 있는 경우이

다. 이러한 사항을 포함한 view들에 대해서 instead of trigger를 생성하면 DML을

수행할 수 있게 된다.

(1) DISTINCT operator

(2) group functions: AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE등

(3) set operations: UNION, MINUS 등

(4) GROUP BY, CONNECT BY, START WITH clauses

(5) ROWNUM pseudocolumn

(6) join (updatable join view인 경우는 제한적으로 DML수행가능 <Bul:11642>참

조)