Oracle Trigger

1. Oracle 트리거란

트리거는 이름이 PL/SQL 블록으로 Oracle 데이터베이스에 저장되며 트리거 이벤트가 발생할 때 자동으로 실행된다. 이벤트는 다음 중 하나일 수 있다.

• INSERT, UPDATE 또는 DELETE와 같이 테이블에 대해 실행되는 데이터 조작 언어(DML) 명령문이다. 예를 들어, customers 테이블의 INSERT 명령문 전에 발생하는 트리거를 정의하는 경우, 트리거는 새 행을 customers 테이블에 삽입하기 전에 한 번 발생합니다.

• 데이터 정의 언어 (DDL) 문은 CREATE 또는 ALTER 문을 실행한다. 이러한 트리거는 종종 스키마의 변경 사항을 기록하는 감사 목적으로 사용된다.

• Oracle 데이터베이스의 시작 또는 종료와 같은 시스템 이벤트이다.

• 로그인 또는 로그아웃과 같은 사용자 이벤트이다.

트리거를 실행하는 동작을 firing a trigger라고 한다. 우리는 방아쇠가 발사되었다고 말한다.

2. 트리거 사용

Oracle 트리거는 다음과 같은 많은 경우에 유용하다.

• UNIQUE, NOT NULL 및 CHECK와 같은 무결성 제약 조건을 사용하여 설정할 수 없는 복잡한 비즈니스 규칙 적용

• 잘못된 트랜잭션 방지

• 테이블 액세스에 대한 통계 정보 수집

• 파생된 열에 대해 자동으로 값 생성

• 민감한 데이터 감사

3. 트리거 만드는 방법

Oracle에서 새 트리거를 생성하려면 다음과 같은 CREATE 트리거 문을 사용한다.

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER } triggering_event ON table_name
[FOR EACH ROW]
[FOLLOWS | PRECEDES another_trigger]
[ENABLE / DISABLE ]
[WHEN condition]
DECLARE
    declaration statements
BEGIN
    executable statements
EXCEPTION
    exception_handling statements
END;

CREATE 트리거 문의 구문을 자세히 살펴보겠다.

트리거에는 헤더와 본문의 두 가지 주요 부분이 있다.

다음은 트리거 헤더를 보여준다.

CREATE [OR REPLACE] TRIGGER trigger_name
    {BEFORE | AFTER } triggering_event ON table_name
    [FOR EACH ROW]
    [FOLLOWS | PRECEDES another_trigger]
    [ENABLE / DISABLE ]
    [WHEN condition]

그리고 이것은 트리거 본문이다.

DECLARE
    declaration statements
BEGIN
    executable statements
EXCEPTION
    exception_handling statements
END;

보이는 것처럼 트리거 본체의 구조는 anonymous PL/SQL block 블록과 동일하다.

1) CREATE OR REPLACE

CREATE 키워드는 새 트리거를 생성 중임을 지정한다. OR REPLACE 키워드는 선택 사항이다. 기존 트리거를 수정하는 데 사용된다.

OR REPLACE 키워드는 선택사항이지만 대부분의 경우 CREATE 키워드와 함께 나타난다.

trigger_example이라는 새 트리거를 정의한 경우 이다.

CREATE TRIGGER trigger_example
    ...

이 트리거를 수정하기로 결정한다.

OR REPLACE 키워드를 포함하지 않으면 트리거 이름이 이미 다른 개체에서 사용되고 있음을 나타내는 오류 메시지가 표시된다.

CREATE TRIGGER trigger_example
    ...

따라서 CREATE OR REPLACE 키워드는 기존 트리거가 있는 경우 기존 트리거를 대체하고 트리거가 없는 경우 새 트리거를 생성한다.

CREATE OR REPLACE trigger_example
    ....

2) Trigger name

CREATE OR REPLACE 키워드 뒤에 생성할 트리거 이름을 지정한다.

3) BEFORE or AFTER

BEFORE 또는 AFTER 옵션은 트리거 이벤트 INSERT, UPDATE 또는 DELETE 전후에 트리거가 실행하는 시기를 지정한다.

4) ON table_name

table_name은 트리거와 연결된 테이블의 이름이다.

5) FOR EACH ROW

FOR EACH ROW는 트리거가 row-level 트리거임을 지정한다. 삽입, 업데이트 또는 삭제된 각 행에 대해 row-level 트리거가 한 번 실행한다.

row-level 트리거 외에도 statement-level 트리거가 있다. statement 트리거는 트리거 이벤트의 영향을 받는 행 수에 관계없이 한 번 실행한다. FOR EACH ROW 절을 생략하면 CREATE TRIGGER 문이 statement-level 트리거를 생성한다.

6) ENABLE or DISABLE

ENABLE / DISABLE 옵션은 트리거가 enabled 또는 disabled 상태로 생성되는지 여부를 지정한다. 트리거가 비활성화된 경우 트리거 이벤트가 발생할 때 실행되지 않는다.

기본적으로 ENABLE / DISABLE 절을 지정하지 않으면 트리거가 활성화 상태로 생성된다.

7) FOLLOWS or PRECEDES another_trigger

INSERT, UPDATE 또는 DELETE와 같은 각 트리거 이벤트에 대해 여러 트리거를 정의하여 실행할 수 있다.

이 경우 FOLLOWS 또는 PRECEDES 옵션을 사용하여 실행 순서를 지정해야 한다.

3. 트리거 예제

고객이 업데이트되거나 삭제될 때마다 customers 테이블에 대한 작업을 기록하려고 한다.

UPDATE 및 DELETE 이벤트를 기록하기 위한 새 테이블을 만든다.

CREATE TABLE audits (
    audit_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    table_name VARCHAR2(255),
    transaction_name VARCHAR2(10),
    by_user VARCHAR2(30),
    transaction_date DATE
);

고객 테이블과 관련된 새 트리거를 생성한다.

CREATE OR REPLACE TRIGGER customers_audit_trg
    AFTER
    UPDATE OR DELETE
    ON customers
    FOR EACH ROW
DECLARE
    l_transaction VARCHAR2(10);
BEGIN
    -- determine the transaction type
    l_transaction := CASE
        WHEN UPDATING THEN 'UPDATE'
        WHEN DELETING THEN 'DELETE'
    END;

    -- insert a row into the audit table
    INSERT INTO audits (table_name, transaction_name, by_user, transaction_date)
    VALUES('CUSTOMERS', l_transaction, USER, SYSDATE);
END;

다음 조항은

AFTER UPDATE OR DELETE ON customers

테이블의 행을 업데이트하거나 삭제한 후 트리거를 실행한다.

트리거 내에서 UPDATE 아니면 DELETE 현재 작업을 결정하고 audits 테이블에 행을 삽입한다.

다음 문은 고객의 신용 한도를 10에서 2000으로 갱신한다.

UPDATE
    customers
SET
    credit_limit = 2000
WHERE
    customer_id =10;

테이블 audits의 내용을 확인하여 트리거가 실행되었는지 확인한다.

SELECT * FROM audits;

결과는 다음과 같다.

출력에서 명확하게 볼 수 있듯이, 트리거 customers_audit_trg가 실행되어 audits 테이블에 새 행을 삽입했다.

이 DELETE 문은 customers 테이블에서 행을 삭제합니다.

DELETE FROM customers
WHERE customer_id = 10;

그리고 audits 테이블의 데이터를 확인한다.

결과에 새 행이 삽입되었음을 보여준다. DELETE 액션이 트리거 customer_audit_trg를 실행시켰음을 의미한다.

[출처 및 참고]

• https://www.oracletutorial.com/plsql-tutorial/oracle-trigger/