관계형 DB의 Assertions, Trigger 정리

무결성 제약조건

무결성 제약조건 정리에서 우리는 테이블을 정의할 때 무결성 제약조건을 지정해서 원하는 조건을 명시할 수 있다는 것을 배웠습니다.

하지만 테이블 정의와 별도로 데이터베이스의 무결성을 시행하는 메커니즘인 Assertion, Trigger도 있습니다.

Assertions

프로그래밍 언어의 assert는 조건이 일치하지 않으면 프로그램을 중단하는 개념이었습니다.

이와 유사하게 데이터베이스의 ASSERTION은 지정한 제약조건이 위배되는 연산을 수행하지 못하게 합니다. 즉, 항상 만족해야할 조건을 지정하는 데 사용합니다.

• 도메인 무결성과 참조 무결성의 상위 개념

SQL 표준에 포함되어 있으나, 대부분의 상용 DBMS에서 아직 제공하지 않습니다.

Assertions, CHECK 비교

Trigger

명시된 이벤트가 발생시 DBMS가 자동 수행하는 프로시져로 무결성을 위한 도구입니다.

• 테이블 정의 시 표현할 수 없는 기업 비즈니스 규칙 시행하는 역할
• 이벤트-조건-동작(ECA) 규칙 이라고도 부름
• SQL3 표준에 포함, 대부분의 상용 DBMS에서 제공하는 구문

Trigger 정의 요건

1. Trigger가 발생하는 조건
2. Trigger가 실행될 때, 수행하는 명령

Triggers in Oracle

특정 DML (DELETE / INSERT / UPDATE)이 발생하기 전후(BEFORE / AFTER)와 (Row / Statement) 조건으로 동록합니다. 그리고 나중에 조건이 실행되는 순간, 해당 trigger를 실행합니다.

• 총 12가지 유형 : 위의 3 * 2 * 2 가지
• SQL을 확장한 언어인 PL/SQL을 이용하여 절차적 명령 프로그래밍

CREATE [OR REPLACE] TRIGGER [트리거 명]
[BEFORE | AFTER] [INSERT, DELETE, UPDATE] -- 전/후, 이벤트
ON [릴레이션 명]
[REFERENCING OLD [ROW | TABLE] AS {변경전 값을 참조하는 변수명} NEW [ROW | TABLE] AS {변경 후 값을 참조하는 변수명}]
[FOR EACH [ROW | STATEMENT]]
WHEN [(Condition)] THEN -- 조건
DEClARE
    -- 변수 선언
BEGIN
    -- 이벤트 발생시 실행할 PL/SQL문들
EXCEPTION
    -- 예외 상황시 실행할 PL/SQL문들 ( 에러 발생 등등 )
END;

예시

UPDATE 연산의 결과로 잔고(balance)가 음이 될 경우, 대출(loan) 테이블에 새로운 계좌를 개설하고, 예금(account) 테이블의 잔고는 0으로 두는 trigger

CREATE trigger overdraft_trigger
AFTER UPDATE
ON account
REFERENCING NEW ROW AS nrow
FOR EACH ROW
WHEN nrow.balance < 0               -- Trigger가 실행되는 조건
BEGIN ATOMIC                        -- 트랜잭션 시작
    INSERT INTO borrower            -- 대출인 테이블에 신규 등록
        (SELECT customer_name, account_number
        FROM depositor
        WHERE nrow.account_number = depositor.account_number);
    INSERT INTO loan VALUES         -- 대출 계좌번호 = 예금 계좌번호
        (nrow.account_number, nrow.branch_name, -nrow.balance);
    UPDATE account SET balance = 0  -- 예금 테이블의 잔고는 0
        WHERE account.account_number = nrow.account_number;
END

문장 단위의 Trigger

동기

• SQL 명령에 의해 여러 레코들이 영향을 받을 때
• 각 레코드마다 별도의 명령을 실행하지 않고, 단일 명령을 모든 레코드에 실행할 때 사용

실행 방법

• FOR EACH STATEMENT를 사용해서 FOR EACH ROW 대체
• 실행 전/후의 테이블을 참조하기 위해서 REFERENCING OLD TABLE과 REFERENCING NEW TABLE을 사용

즉, SQL 결과에 영향을 받는 레코드(row)들마다 실행하는 ROW 방식과 달리 STATEMENT 방식은 실행한 SQL 명령(statement)마다 실행하는 Trigger입니다.

그래서 전/후의 하나의 ROW만 비교하면 되는 ROW 방식과 달리, STATEMENT 방식은 테이블 전체를 전/후로 비교해야 합니다.