본문으로 이동

데이터베이스 트리거

위키백과, 우리 모두의 백과사전.

데이터베이스 트리거(database trigger)는 데이터베이스의 특정 테이블이나 뷰에 특정 이벤트가 발생할 때마다 자동으로 실행되는 절차적 코드이다. 트리거는 주로 데이터베이스 내 정보의 무결성을 유지하기 위해 사용된다. 예를 들어, 직원 테이블에 새로운 레코드(새로운 노동자)가 추가될 때, 세금, 휴가, 급여 테이블에도 새로운 레코드가 생성되어야 한다. 트리거는 기록 데이터를 로그로 남기는 데에도 사용될 수 있으며, 예를 들어 직원의 이전 급여 정보를 추적하는 데 활용된다.

DBMS별 트리거

[편집]

다음은 몇 가지 일반적인 DBMS에서 트리거를 지원하는 방식에 대한 설명이다.

오라클

[편집]

데이터가 수정될 때 실행되는(그리고 PL/SQL 코드를 실행하는) 트리거 외에도, 오라클 10g는 스키마 수준의 객체(즉, 테이블)가 수정되거나 사용자 로그인 또는 로그아웃 이벤트가 발생할 때 실행되는 트리거를 지원한다.

스키마 수준 트리거

[편집]
  • After Creation (생성 후)
  • Before Alter (수정 전)
  • After Alter (수정 후)
  • Before Drop (삭제 전)
  • After Drop (삭제 후)
  • Before Insert (삽입 전)

트리거의 네 가지 주요 유형은 다음과 같다:

  1. 행 수준 트리거(Row-level trigger): 행의 컬럼 값이 변경되기 전이나 후에 실행된다.
  2. 컬럼 수준 트리거(Column-level trigger): 지정된 컬럼이 변경되기 전이나 후에 실행된다.
  3. 각 행마다(For each row) 유형: insert/update/delete의 영향을 받는 결과 집합의 각 행에 대해 한 번씩 실행된다.
  4. 각 문장마다(For each statement) 유형: 전체 결과 집합에 대해 한 번만 실행되지만, 문장이 실행될 때마다 호출된다.

시스템 수준 트리거

[편집]

오라클 8i부터는 로그인, 로그아웃, 시작과 같은 데이터베이스 이벤트가 오라클 트리거를 실행할 수 있게 되었다.[1]

마이크로소프트 SQL 서버

[편집]

MS SQL 서버는 DML 및 DDL 문에 대한 트리거와 특수 트리거인 "logon"을 지원한다.

DDL 트리거의 범위는 데이터베이스(CREATE TRIGGER name ON DATABASE ...) 또는 전체 SQL 서버 인스턴스(CREATE TRIGGER name ON ALL SERVER)가 될 수 있다. 전체 인스턴스를 사용하는 경우, SQL 인스턴스 내의 데이터베이스 수준 범위의 명령뿐만 아니라 서버 수준 범위의 명령에서 실행되는 모든 이벤트를 캡처할 수 있다.

마이크로소프트 SQL 서버의 DDL 트리거에서 사용 가능한 모든 실행 이벤트 목록은 마이크로소프트 독스에서 확인할 수 있다.[2]

DML 트리거에서 조건부 작업을 수행하거나 수정 후 데이터를 테스트하는 것은 Inserted 및 Deleted 테이블이라는 가상 테이블(임시 테이블)에 액세스하여 수행된다. DML 트리거는 항상 "문장마다(FOR EACH STATEMENT)" 실행되지만, inserted/deleted 가상 테이블에서 커서를 사용하여 행 이벤트("FOR EACH ROW")로 코딩할 수 있다.

PostgreSQL

[편집]

1997년에 트리거 지원을 도입했다. SQL:2003의 다음 기능은 이전의 PostgreSQL에서 구현되지 않았었다:

  • SQL은 특정 컬럼의 업데이트에 대해 트리거가 실행되는 것을 허용한다. PostgreSQL 9.0 버전부터 이 기능이 구현되었다.
  • 표준은 트리거된 동작으로 SELECT, INSERT, UPDATE 이외의 CREATE TABLE과 같은 여러 SQL 문을 실행하는 것을 허용한다. 이는 CREATE TABLE을 호출하는 저장 프로시저나 함수를 생성하여 수행할 수 있다.[3]

개요:

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
    ON TABLE [ FOR [ EACH ] { ROW | STATEMENT } ]
    EXECUTE PROCEDURE funcname ( arguments )

파이어버드

[편집]

파이어버드는 테이블당 다수의 행 수준, BEFORE 또는 AFTER, INSERT, UPDATE, DELETE(또는 이들의 조합) 트리거를 지원한다. 이들은 항상 기본 테이블 변경에 "추가되어" 실행되며, 트리거 간의 순서가 모호할 경우 순서를 지정할 수 있다(POSITION 절). 트리거는 뷰에도 존재할 수 있으며, 이 경우 항상 "대신하여(instead of)" 트리거로 작동하여 기본 업데이트 가능 뷰 로직을 대체한다. (2.1 버전 이전에는 업데이트 가능하다고 간주되는 뷰의 트리거가 기본 로직에 추가되어 실행되었다.)

파이어버드는 (오라클과 달리) 변이 테이블(mutating table) 예외를 발생시키지 않으며, 트리거는 기본적으로 필요에 따라 중첩 및 재귀를 수행한다. (SQL 서버는 기본적으로 중첩은 허용하지만 재귀는 허용하지 않는다.) 파이어버드의 트리거는 (Inserted 및 Deleted 테이블이 아닌) NEW 및 OLD 컨텍스트 변수를 사용하며, 트리거의 현재 사용 상태를 나타내는 UPDATING, INSERTING, DELETING 플래그를 제공한다.

{CREATE | RECREATE | CREATE OR ALTER} TRIGGER name FOR {table name | view name}
 [ACTIVE | INACTIVE]
 {BEFORE | AFTER}
 {INSERT [OR UPDATE] [OR DELETE] | UPDATE [OR INSERT] [OR DELETE] | DELETE [OR UPDATE] [OR INSERT] }
 [POSITION n] AS
BEGIN
 ....
END

2.1 버전부터 파이어버드는 다음과 같은 데이터베이스 수준 트리거를 추가로 지원한다:

  • CONNECT (여기서 발생한 예외는 연결 완료를 방지함)
  • DISCONNECT
  • TRANSACTION START
  • TRANSACTION COMMIT (여기서 발생한 예외는 트랜잭션 커밋을 방지하거나, 2단계 커밋의 경우 준비를 방지함)
  • TRANSACTION ROLLBACK

데이터베이스 수준 트리거는 다중 테이블 제약 조건을 강제하거나 구체화 뷰를 모방하는 데 도움이 될 수 있다. TRANSACTION COMMIT 트리거에서 예외가 발생하면 지금까지 트리거에 의해 수행된 변경 사항은 롤백되고 클라이언트 애플리케이션에 알림이 가지만, 트랜잭션은 COMMIT이 요청되지 않은 것처럼 활성 상태로 유지된다. 클라이언트 애플리케이션은 계속해서 변경 작업을 수행하고 COMMIT을 다시 요청할 수 있다.

데이터베이스 트리거 구문:

{CREATE | RECREATE | CREATE OR ALTER} TRIGGER name
 [ACTIVE | INACTIVE] ON
 {CONNECT | DISCONNECT | TRANSACTION START | TRANSACTION COMMIT | TRANSACTION ROLLBACK}
 [POSITION n] AS
BEGIN
 .....
END

MySQL/MariaDB

[편집]

MySQL/MariaDB DBMS에서의 제한적인 트리거 지원은 2005년에 출시된 MySQL 5.0 버전에 추가되었다.[4]

8.0 버전부터는 DDL (데이터 정의 언어) 트리거와 DML (데이터 조작 언어) 트리거를 허용한다. 또한 두 가지 유형의 DDL 트리거(AFTER 또는 BEFORE)를 모두 사용하여 트리거를 정의할 수 있다. CREATE TRIGGER 절을 사용하여 생성하고 DROP TRIGGER 절을 사용하여 삭제한다. 이벤트 발생 시 호출되는 문장은 FOR EACH ROW 절 뒤에 정의되며, 그 뒤에 나오는 것이 표현식인지 문장인지를 나타내는 키워드(SET 또는 BEGIN)가 뒤따른다.[5]

IBM DB2 LUW

[편집]

DB2 for LUW(LUW는 Linux, Unix, Windows를 의미)로 알려진 분산 시스템용 IBM DB2는 Before 트리거, After 트리거, Instead of 트리거의 세 가지 트리거 유형을 지원한다. 문장 수준과 행 수준 트리거가 모두 지원된다. 테이블의 동일한 작업에 대해 트리거가 여러 개 있는 경우 실행 순서는 트리거 생성 날짜에 의해 결정된다. 9.7 버전부터 IBM DB2는 자율 트랜잭션(autonomous transactions)을 지원한다.[6]

Before 트리거는 데이터를 확인하고 작업 허용 여부를 결정하는 용도이다. Before 트리거에서 예외가 발생하면 작업이 중단되고 데이터가 변경되지 않는다. DB2에서 Before 트리거는 읽기 전용이므로 Before 트리거 내에서 데이터를 수정할 수 없다. After 트리거는 요청된 변경이 수행된 후의 후처리를 위해 설계되었다. After 트리거는 테이블에 데이터를 쓸 수 있으며, 다른 일부 데이터베이스와 달리 트리거가 작동하는 테이블을 포함하여 모든 테이블에 쓸 수 있다. Instead of 트리거는 뷰를 쓰기 가능하게 만드는 용도이다.

트리거는 보통 SQL PL 언어로 프로그래밍된다.

SQLite

[편집]
CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] [database_name .] trigger_name
[BEFORE | AFTER | INSTEAD OF] {DELETE | INSERT | UPDATE [OF column_name [, column_name]...]}
ON {table_name | view_name}
   [FOR EACH ROW] [WHEN condition is mandatory ]
BEGIN
   ...
END

SQLite는 행 수준 트리거만 지원하며 문장 수준 트리거는 지원하지 않는다.

SQLite에서 지원되지 않는 업데이트 가능 뷰는 INSTEAD OF 트리거로 모방할 수 있다.

XML 데이터베이스

[편집]

비관계형 데이터베이스에서 트리거를 구현한 예로 XQuery 기반의 트리거 지원을 제공하는 세드나(Sedna)가 있다. 세드나의 트리거는 SQL:2003 트리거와 유사하게 설계되었지만, 기본적으로 XML 질의 및 업데이트 언어(XPath, XQuery 및 XML 업데이트 언어)를 기반으로 한다.

세드나의 트리거는 데이터베이스에 저장된 XML 문서의 모든 노드에 설정된다. 이러한 노드가 업데이트될 때, 트리거는 본문에 지정된 XQuery 질의 및 업데이트를 자동으로 실행한다. 예를 들어, 다음 트리거는 해당 인물에 의해 참조되는 열린 경매가 있는 경우 person 노드 삭제를 취소한다.

CREATE TRIGGER "trigger3"
    BEFORE DELETE
    ON doc("auction")/site//person
    FOR EACH NODE
    DO
    {
        if (exists($WHERE//open_auction/bidder/personref/@person=$OLD/@id))
        then ( )
        else $OLD;
    }

행 및 문장 수준 트리거

[편집]

트리거의 동작 방식을 이해하려면 행(Row) 수준과 문장(Statement) 수준이라는 두 가지 주요 트리거 유형을 알아야 한다. 둘 사이의 차이점은 트리거 내의 코드가 실행되는 횟수와 시점이다.

특정 테이블의 UPDATE 시 호출되도록 만들어진 트리거가 있다고 가정해 보자. 행 수준 트리거는 UPDATE의 영향을 받는 각 행에 대해 한 번씩 실행된다. UPDATE 명령에 의해 영향을 받는 행이 없으면 트리거 내의 어떠한 코드도 실행되지 않는다. 문장 수준 트리거는 UPDATE에 의해 영향을 받는 행의 수에 관계없이 한 번 호출된다. UPDATE 명령이 어떠한 행에도 영향을 주지 않았더라도 트리거 내의 코드는 여전히 한 번 실행된다.

BEFORE 및 AFTER 옵션[7]을 사용하는 것은 트리거가 호출되는 시점을 결정한다. 특정 테이블의 INSERT 시 호출되는 트리거가 있다고 가정해 보자. 트리거가 BEFORE 옵션을 사용하는 경우, 테이블에 INSERT가 발생하기 전에 트리거 내의 코드가 실행된다. BEFORE 트리거의 일반적인 용도는 INSERT의 입력 값을 검증하거나 그에 맞게 값을 수정하는 것이다. 이제 대신 AFTER를 사용하는 트리거가 있다고 하자. 트리거 내의 코드는 테이블에 INSERT가 발생한 후에 실행된다. 이 트리거의 예시 용도는 누가 데이터베이스에 삽입을 했는지 감시 기록(audit history)을 생성하여 변경 사항을 추적하는 것이다. 이러한 옵션을 사용할 때 몇 가지 염두에 두어야 할 사항이 있다. BEFORE 옵션은 테이블 수정을 허용하지 않으므로 입력 유효성 검사가 실용적인 용도가 된다. AFTER 트리거를 사용하면 감시 기록 테이블에 삽입하는 것과 같이 테이블을 수정할 수 있다.

트리거를 생성할 때 문장 수준인지 행 수준인지를 결정하려면 행 수준의 경우 FOR EACH ROW 절을 포함하고, 문장 수준의 경우 이 절을 생략하면 된다. 트리거 내에서 추가적인 INSERT/UPDATE/DELETE 명령을 사용하는 것에 주의해야 한다. 트리거 재귀가 발생하여 원치 않는 동작을 초래할 수 있기 때문이다. 아래 예제에서 각 트리거는 서로 다른 테이블을 수정하고 있으며, 무엇이 수정되는지를 살펴봄으로써 서로 다른 트리거 유형이 사용되는 몇 가지 일반적인 응용 사례를 볼 수 있다.

다음은 영향을 받는 각 행마다(FOR EACH ROW) 업데이트 후에(AFTER) 호출되는 행 수준 트리거의 오라클 구문 예시이다. 이 트리거는 전화번호부 데이터베이스의 업데이트 시 호출된다. 트리거가 호출되면 phone_book_audit이라는 별도의 테이블에 항목을 추가한다. 또한 트리거가 시퀀스[8]와 같은 스키마 객체를 활용할 수 있음에 주목하라. 이 예제에서는 phone_book_audit 테이블에서 고유한 기본 키를 생성하기 위해 audit_id_sequence.nextVal이 사용된다.

CREATE OR REPLACE TRIGGER phone_book_audit
  AFTER UPDATE ON phone_book FOR EACH ROW
BEGIN
  INSERT INTO phone_book_audit
    (audit_id,audit_change, audit_l_name, audit_f_name, audit_old_phone_number, audit_new_phone_number, audit_date)
    VALUES
    (audit_id_sequence.nextVal,'Update', :OLD.last_name, :OLD.first_name, :OLD.phone_number, :NEW.phone_number, SYSDATE);
END;

이제 성이 'Jones'인 사람들에 대해 phone_book 테이블에 UPDATE를 호출한다.

UPDATE phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Jones';
Audit_IDAudit_ChangeF_NameL_NameNew_Phone_NumberOld_Phone_NumberAudit_Date
1UpdateJordanJones111-111-1111098-765-432102-MAY-14
2UpdateMeganJones111-111-1111111-222-345602-MAY-14

phone_number_audit 테이블에 두 개의 항목이 채워진 것을 알 수 있다. 이는 데이터베이스에 성이 'Jones'인 항목이 두 개 있었기 때문이다. 업데이트가 두 개의 개별 행 값을 수정했으므로, 생성된 트리거가 각 수정 후에 한 번씩, 총 두 번 호출되었다.

After - 문장 수준 트리거

[편집]

phone_book 테이블 업데이트 후에 호출되는 오라클 구문 문장 트리거이다. 트리거가 호출되면 phone_book_edit_history 테이블에 삽입을 수행한다.

CREATE OR REPLACE TRIGGER phone_book_history
  AFTER UPDATE ON phone_book
BEGIN
  INSERT INTO phone_book_edit_history
    (audit_history_id, username, modification, edit_date)
    VALUES
    (audit_history_id_sequence.nextVal, USER,'Update', SYSDATE);
END;

이제 위의 예시와 똑같은 업데이트를 수행하되, 이번에는 문장 수준 트리거를 사용한다. 

UPDATE phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Jones';
Audit_History_IDUsernameModificationEdit_Date
1HAUSCHBCUpdate02-MAY-14

결과는 업데이트가 두 개의 행을 변경했음에도 불구하고 트리거가 한 번만 호출되었음을 보여준다.

Before each - 행 수준 트리거

[편집]

이 예제는 WHEN 조건을 사용하여 INSERT를 수정하는 BEFORE EACH ROW 트리거를 보여준다. 성(last name)이 10자보다 길면 SUBSTR 함수[9]를 사용하여 last_name 컬럼 값을 약어로 변경한다.

CREATE OR REPLACE TRIGGER phone_book_insert
  BEFORE INSERT ON phone_book FOR EACH ROW
  WHEN (LENGTH(new.last_name) > 10)
BEGIN
    :new.last_name := SUBSTR(:new.last_name,0,1);
END;

이제 이름이 긴 사람의 INSERT를 수행한다.

INSERT INTO phone_book VALUES
(6, 'VeryVeryLongLastName', 'Erin', 'Minneapolis', 'MN', '989 University Drive', '123-222-4456', 55408, TO_DATE('11/21/1991', 'MM/DD/YYYY'));
Person_IDLast_NameFirst_NameCityState_AbbreviationAddressPhone_NumberZip_codeDOB
6VErinMinneapolisMN989 University Drive123-222-44565540821-NOV-91

위의 결과에서 보듯이 트리거가 작동하여 INSERT가 실행되기 전에 그 값을 수정했다.

Before - 문장 수준 트리거

[편집]

BEFORE 문장 트리거를 사용하는 것은 데이터베이스 제한 사항을 강제할 때 특히 유용하다.[10] 이 예제는 phone_book 테이블에서 "SOMEUSER"라는 이름을 가진 사람에 대한 제한을 강제하는 방법을 보여준다.

CREATE OR REPLACE TRIGGER hauschbc
  BEFORE INSERT ON SOMEUSER.phone_book
BEGIN
    RAISE_APPLICATION_ERROR (
         num => -20050,
         msg => 'Error message goes here.');
END;

이제 "SOMEUSER"가 로그인한 후 INSERT를 시도하면 다음 오류 메시지가 표시된다:

SQL Error: ORA-20050: Error message goes here.

이와 같은 사용자 정의 오류는 num 변수로 정의할 수 있는 값에 제한이 있다. 이미 정의된 수많은 다른 오류들 때문에 이 변수는 -20000에서 -20999 사이의 범위에 있어야 한다.

각주

[편집]
  1. ↑ Nanda, Arup; Burleson, Donald K. (2003). 〈9〉. Burleson, Donald K. (편집). 《Oracle Privacy Security Auditing: Includes Federal Law Compliance with HIPAA, Sarbanes Oxley and the Gramm Leach Bliley Act GLB》. Oracle in-focus series 47. Kittrell, North Carolina: Rampant TechPress. 511쪽. ISBN 9780972751391. 2018년 4월 17일에 확인함. [...] system-level triggers [...] were introduced in Oracle8i. [...] system-level triggers are fired at specific system events such as logon, logoff, database startup, DDL execution, and servererror [...].
  2. ↑ “DDL Events - SQL Server”. 2023년 3월 15일.
  3. ↑ “PostgreSQL: Documentation: 9.0: CREATE TRIGGER”. 《www.postgresql.org》. 2015년 10월 8일.
  4. ↑ “MySQL 5.0 Reference Manual” (PDF). 2016년 5월 11일.
  5. ↑ “MySQL :: MySQL 8.0 Reference Manual :: 25.3.1 Trigger Syntax and Examples”.
  6. ↑ “Autonomous transactions”. 《www.ibm.com》. 2009년 7월 30일.
  7. ↑ “6 Using Triggers”. 《docs.oracle.com》.
  8. ↑ “Oracle's Documentation on Sequences”. 2011년 12월 1일에 원본 문서에서 보존된 문서.
  9. ↑ “Oracle SQL Functions – The Complete List”. 2014년 12월 26일.
  10. ↑ “Database PL/SQL Language Reference”. 《docs.oracle.com》.

외부 링크

[편집]
원본 주소 "https://ko.wikipedia.org/w/index.php?title=데이터베이스_트리거&oldid=41505096"