데이터베이스 트리거

"오늘의AI위키"는 AI 기술로 일관성 있고 체계적인 최신 지식을 제공하는 혁신 플랫폼입니다.
"오늘의AI위키"의 AI를 통해 더욱 풍부하고 폭넓은 지식 경험을 누리세요.

1. 개요
2. 트리거의 유형
3. 주요 DBMS별 트리거
참조

1. 개요

데이터베이스 트리거는 데이터베이스 시스템에서 특정 이벤트 발생 시 자동으로 실행되는 특별한 종류의 저장 프로시저이다. 트리거는 행 트리거와 문장 트리거로 나뉘며, 각 행에 대해 실행되는 행 트리거와 INSERT, UPDATE, DELETE 문에 대해 한 번만 실행되는 문장 트리거가 있다. 트리거는 BEFORE, AFTER, INSTEAD OF 속성을 가지며, INSERT, UPDATE, DELETE 이벤트에 의해 기동될 수 있다. 각 데이터베이스 관리 시스템(DBMS)마다 트리거를 지원하는 방식과 기능에 차이가 있다. 예를 들어, 오라클은 스키마 레벨 트리거를, 마이크로소프트 SQL 서버는 DDL 트리거와 뷰에 대한 INSTEAD OF 트리거를 지원한다.

더 읽어볼만한 페이지

데이터 모델링 - 빌딩 정보 모델링
빌딩 정보 모델링(BIM)은 건축물의 전 생애주기 동안 발생하는 정보를 디지털 모델로 통합 관리하는 프로세스이다.
데이터 모델링 - 저장 프로시저
저장 프로시저는 데이터베이스 관리 시스템에서 SQL 문들을 미리 컴파일하여 저장하고, 모듈화, 보안성, 성능 향상, 유지보수 용이성과 같은 특징을 가지며, 데이터베이스 시스템마다 구현 방식과 지원하는 언어가 다를 수 있는 코드 묶음이다.
데이터베이스 관리 시스템 - 트랜잭션 처리
트랜잭션 처리는 데이터베이스 시스템에서 데이터의 일관성과 무결성을 보장하기 위한 기술이며, ACID 속성을 통해 데이터 정확성을 유지하고 롤백, 데드락 처리 등의 기술을 활용한다.
데이터베이스 관리 시스템 - 저장 프로시저
저장 프로시저는 데이터베이스 관리 시스템에서 SQL 문들을 미리 컴파일하여 저장하고, 모듈화, 보안성, 성능 향상, 유지보수 용이성과 같은 특징을 가지며, 데이터베이스 시스템마다 구현 방식과 지원하는 언어가 다를 수 있는 코드 묶음이다.
데이터베이스 - 지식 베이스
지식 베이스는 특정 주제 정보를 체계적으로 저장 및 관리하며 규칙 기반 추론으로 새로운 지식 도출에 활용되고, 웹 콘텐츠 관리 및 지식 관리 시스템으로 확장되어 온톨로지를 이용, 인공지능 기술과 결합하여 문제 해결책을 제시하고 경험을 통해 학습하는 시스템이다.
데이터베이스 - 화이트리스트
화이트리스트는 특정 대상만 허용하고 나머지는 차단하는 접근 제어 목록으로, 정보보안, 무역, 금융 등 다양한 분야에서 활용되지만, 목록 선정 기준의 불명확성, 사회적 문제점 등의 위험성으로 투명하고 엄격한 관리가 필요하다.

데이터베이스 트리거

2. 트리거의 유형

트리거는 크게 행 트리거와 문장 트리거 두 종류로 나뉜다.^[7]

행 트리거: 테이블 안의 영향을 받은 행 각각에 대해 실행된다. 변경 전 또는 변경 후의 행은 `OLD`, `NEW`라는 가상 줄 변수를 사용하여 읽을 수 있다.
문장 트리거: INSERT, UPDATE, DELETE 문에 대해 한 번만 실행된다.

또한, 트리거는 다음과 같은 속성을 갖는다.^[7]

BEFORE 또는 AFTER: 트리거가 실행되는 시점을 지정한다.
INSTEAD OF: 트리거를 원래 문장 대신 수행한다.
WHEN: 트리거를 시작하는 조건식을 지정한다.

일반적으로 트리거는 다음 세 가지 경우에 기동된다. 트리거는 SELECT 문에 의한 데이터 검색에 영향을 줄 수 없다.^[1]

INSERT (새로운 행 삽입)^[1]
UPDATE (기존 행 변경) / UPDATE OF (기존 행의 특정 열 변경)^[1]
DELETE (기존 행 삭제)^[1]

2. 1. 행 트리거 (Row Trigger)

테이블 내에서 영향을 받는 각 행에 대해 실행된다. 변경 전 또는 변경 후의 행은 `OLD`, `NEW`라는 가상의 행 변수를 사용하여 읽을 수 있다.^[7] 예를 들어, 특정 행의 값이 변경될 때마다 변경 이력을 기록하는 경우에 사용될 수 있다.

어떤 테이블에 대한 `UPDATE` 시 호출되도록 만들어진 트리거가 있다고 가정할 때, 행 수준 트리거는 `UPDATE`의 영향을 받는 각 행에 대해 한 번씩 실행된다. `UPDATE` 명령어가 영향을 미치는 행이 없으면 트리거 내의 코드는 실행되지 않는다.

`BEFORE` 및 `AFTER` 옵션^[7]을 사용하여 트리거가 호출되는 시점을 결정한다. `BEFORE` 옵션을 사용하는 경우, 트리거 내의 코드는 테이블에 `INSERT`가 발생하기 전에 실행된다. `BEFORE` 트리거의 일반적인 용도는 `INSERT`의 입력 값을 확인하거나 그에 따라 값을 수정하는 것이다. `AFTER`를 사용하는 트리거는 테이블에 `INSERT`가 발생한 후에 트리거 내의 코드가 실행된다. 이러한 트리거는 데이터베이스에 삽입한 사람의 감사 내역을 생성하여 변경 사항을 추적하는 데 사용될 수 있다.

트리거를 생성할 때 문 수준인지 행 수준인지 결정하려면 `FOR EACH ROW` 절을 포함하면 행 수준이고, 절을 생략하면 문 수준이다.

다음은 `FOR EACH ROW`가 영향을 미치는 업데이트 후에 호출되는 행 수준 트리거의 오라클 구문 예시이다.

```sql

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;

```

`phone_book` 테이블에서 `UPDATE`를 호출하면 `phone_number_audit` 테이블이 채워지는 것을 확인할 수 있다.

```sql

UPDATE phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Jones';

```

Audit_ID	Audit_Change	F_Name	L_Name	New_Phone_Number	Old_Phone_Number	Audit_Date
1	Update	Jordan	Jones	111-111-1111	098-765-4321	02-MAY-14
2	Update	Megan	Jones	111-111-1111	111-222-3456	02-MAY-14

데이터베이스에 'Jones'라는 성을 가진 두 개의 항목이 있기 때문에 생성된 트리거가 두 번 호출되었다. 각 수정 후에 한 번씩 호출된 것이다.

다음은 `WHEN` 조건을 사용하여 `INSERT`를 수정하는 `BEFORE EACH ROW` 트리거의 예시이다.

```sql

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;

```

이름이 긴 사람을 삽입하면 다음과 같다.

```sql

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_ID	Last_Name	First_Name	City	State_Abbreviation	Address	Phone_Number	Zip_code	DOB
6	V	Erin	Minneapolis	MN	989 University Drive	123-222-4456	55408	21-NOV-91

트리거는 위 결과와 같이 실행 '''전에''' `INSERT`의 값을 수정하여 작동했다.

2. 2. 문장 트리거 (Statement Trigger)

문장 트리거는 INSERT, UPDATE, DELETE 문에 대해 한 번만 실행된다.^[7] 예를 들어, 특정 테이블에 대한 데이터 변경 작업(INSERT, UPDATE, DELETE)이 발생할 때마다 로그를 기록하는 경우가 이에 해당한다.

UPDATE문의 영향을 받는 행의 수에 관계없이 한 번 호출된다. UPDATE문이 어떤 행에도 영향을 미치지 않더라도 트리거 내의 코드는 여전히 한 번 실행된다는 점에 유의해야 한다.^[7]

다음은 `phone_book` 테이블에 대한 UPDATE 작업 후에 호출되는 오라클(Oracle) 구문 문장 트리거의 예시이다. 트리거가 호출되면 `phone_book_edit_history` 테이블에 삽입을 수행한다.

```sql

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;

```

이제 위 예제와 정확히 동일한 업데이트를 수행하지만, 이번에는 문장 수준 트리거를 사용한다.

```sql

UPDATE phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Jones';

```

Audit_History_ID	Username	Modification	Edit_Date
1	HAUSCHBC	Update	02-MAY-14

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

BEFORE 문 트리거를 사용하는 것은 데이터베이스 제한을 적용할 때 특히 유용하다.^[10]

2. 3. 기타 속성

트리거는 다음과 같은 속성을 갖는다.

'''BEFORE''' 또는 '''AFTER''' : 트리거가 실행되는 시점을 지정한다.
'''INSTEAD OF''' : 트리거를 원래 문장 대신 실행한다. 주로 뷰에 대한 데이터 변경 작업을 처리하는 데 사용된다.
'''WHEN''' : 트리거를 시작하는 조건식을 지정한다.^[9]

2. 4. 트리거의 기동 이벤트

트리거는 일반적으로 다음 세 가지 이벤트에 의해 기동된다. 트리거는 SELECT 문에 의한 데이터 검색에 영향을 미칠 수 없다.^[1]

INSERT (새로운 행 삽입)^[1]
UPDATE (기존 행 변경) / UPDATE OF (기존 행의 특정 열 변경)^[1]
DELETE (기존 행 삭제)^[1]

3. 주요 DBMS별 트리거

대한민국에서 주로 사용되는 DBMS들은 각각 다른 방식으로 트리거를 지원한다.

DBMS	지원 트리거 종류	특징
오라클 데이터베이스	INSERT, UPDATE, DELETE, AFTER CREATE, BEFORE / AFTER ALTER, BEFORE / AFTER DROP, BEFORE LOGOFF / AFTER LOGON	표준 트리거 외에 스키마 변경 및 사용자 로그인/로그오프에 대한 "스키마 레벨 트리거" 지원
Microsoft SQL Server	INSERT, UPDATE, DELETE, DROP TABLE, CREATE TABLE, ALTER TABLE, 로그인	표준 트리거 외에 뷰에 대한 INSTEAD OF 트리거, 데이터 정의 언어(DDL) 트리거 지원
PostgreSQL	INSERT, UPDATE / UPDATE OF, DELETE, TRUNCATE	표준 트리거, TRUNCATE, UPDATE OF, WHEN 절 등 지원. 트리거에서 실행되는 프로세스는 함수로 제공.
MySQL	INSERT, UPDATE, DELETE	표준 트리거 지원

각 DBMS별 트리거의 문법은 하위 섹션을 참고할 수 있다.

3. 1. 오라클 데이터베이스 (Oracle Database)

오라클 데이터베이스는 표준 트리거 외에 Oracle 9i에서 "스키마 레벨 트리거"라는 스키마 변경 및 사용자 로그인/로그오프에 대한 트리거를 추가했다.^[1]

INSERT
UPDATE
DELETE
AFTER CREATE
BEFORE / AFTER ALTER
BEFORE / AFTER DROP
BEFORE LOGOFF / AFTER LOGON

Oracle 10g는 데이터가 수정될 때 작동(및 PL/SQL 코드 실행)하는 트리거 외에도, 스키마 레벨 객체(즉, 테이블)가 수정될 때와 사용자 로그온 또는 로그오프 이벤트가 발생할 때 작동하는 트리거를 지원한다.^[1]

생성 후
변경 전
변경 후
삭제 전
삭제 후
삽입 전

트리거의 네 가지 주요 유형은 다음과 같다.^[1]

# 행 수준 트리거: 이 트리거는 ''행의 모든 열 값''이 변경되기 전이나 후에 실행된다.

# 열 수준 트리거: 이 트리거는 ''지정된 열''이 변경되기 전이나 후에 실행된다.

# 각 행 유형: 이 트리거는 삽입/업데이트/삭제로 영향을 받는 결과 집합의 각 행에 대해 한 번씩 실행된다.

# 각 문 유형: 이 트리거는 전체 결과 집합에 대해 한 번만 실행되지만, 문이 실행될 때마다 실행된다.

오라클 8i부터 데이터베이스 이벤트(로그온, 로그오프, 시작)는 오라클 트리거를 발생시킬 수 있다.^[1]

3. 2. 마이크로소프트 SQL 서버 (Microsoft SQL Server)

마이크로소프트 SQL 서버에서는 표준 트리거 외에 뷰에 INSTEAD OF 트리거를 정의할 수 있다. 또한, 마이크로소프트 SQL 서버 2005에서 데이터 정의 언어(DDL) 트리거가 추가되었다.^[2]

트리거에서 조건부 동작을 수행하거나 수정 후 데이터를 테스트하기 위해 임시 테이블인 ''Inserted'' 및 ''Deleted'' 테이블에 접근할 수 있다.

DDL 트리거에 사용 가능한 실행 이벤트 목록은 다음과 같다.

INSERT
UPDATE
DELETE
DROP TABLE
CREATE TABLE
ALTER TABLE
로그인

3. 3. PostgreSQL

PostgreSQL는 1997년 버전 6.2부터 표준 트리거를 지원한다.^[3] 8.4 버전에서 TRUNCATE 이벤트를, 9.0 버전에서 UPDATE OF, WHEN 절을 지원하기 시작했다.

INSERT
UPDATE / UPDATE OF
DELETE
TRUNCATE

트리거에서 실행되는 프로세스는 함수로 제공된다.^[3]

'''문법''' :

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }

ON table [ FOR [ EACH ] { ROW | STATEMENT } ]

[ WHEN (...) ] EXECUTE PROCEDURE funcname ( arguments )

3. 4. MySQL / MariaDB

MySQL은 버전 5.0.2부터 표준 트리거를 지원한다.^[4]

INSERT
UPDATE
DELETE

주의: MySQL은 각 테이블에 각 형태의 단 하나의 트리거만 허용한다. (즉, 인서트 이전에 하나, 인서트 이후에 하나, 업데이트 이전에 하나, 업데이트 이후에 하나, 삭제 전후에 각각 하나씩)

주의: MySQL은 구문을 외부에서 격발하지 않는다. (즉, API, 외래 키 캐스캐이드)

'''문법''' :

```sql

CREATE TRIGGER salary_trigger

BEFORE UPDATE ON employee_table

REFERENCING NEW ROW AS n, OLD ROW AS o

FOR EACH ROW

IF n.salary <> o.salary THEN

...

END IF;

```

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

3. 5. 파이어버드 (Firebird)

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

파이어버드는 변형 테이블 예외를 발생시키지 않으며, 트리거는 기본적으로 필요에 따라 중첩 및 재귀된다. 파이어버드의 트리거는 NEW 및 OLD 컨텍스트 변수를 사용하고 UPDATING, INSERTING 및 DELETING 플래그를 제공하여 트리거의 현재 사용을 나타낸다.

트리거 생성 문법은 다음과 같다:

```sql

{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

데이터베이스 수준 트리거는 여러 테이블 제약 조건을 적용하거나 materialized view를 에뮬레이션하는 데 도움이 될 수 있다. TRANSACTION COMMIT 트리거에서 예외가 발생하면 지금까지 트리거가 변경한 내용은 롤백되고 클라이언트 애플리케이션에 알림이 전송되지만 트랜잭션은 COMMIT이 요청된 적이 없는 것처럼 활성 상태로 유지된다. 클라이언트 애플리케이션은 계속해서 변경을 하고 COMMIT을 다시 요청할 수 있다.

데이터베이스 트리거의 생성 문법은 다음과 같다.

```sql

{CREATE | RECREATE | CREATE OR ALTER} TRIGGER name

[ACTIVE | INACTIVE] ON

{CONNECT | DISCONNECT | TRANSACTION START | TRANSACTION COMMIT | TRANSACTION ROLLBACK}

[POSITION n] AS

BEGIN

.....

END

3. 6. IBM DB2 LUW

IBM DB2 LUW(LUW는 '''L'''inux, '''U'''nix, '''W'''indows를 의미)는 Before 트리거, After 트리거, Instead of 트리거의 세 가지 유형을 지원한다. 문장 레벨 및 행 레벨 트리거를 모두 지원한다. 동일한 테이블에 대한 동일한 작업에 대해 트리거가 여러 개 있는 경우 실행 순서는 트리거 생성 날짜에 따라 결정된다. 버전 9.7부터 IBM DB2는 자율 트랜잭션을 지원한다.^[6]

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

트리거는 일반적으로 SQL PL 언어로 프로그래밍된다.

3. 7. SQLite

SQLite는 구문 수준 트리거가 아닌 행 수준 트리거만 지원한다.^[1]

SQLite에서 지원하지 않는 업데이트 가능한 뷰는 INSTEAD OF 트리거를 사용하여 에뮬레이션할 수 있다.^[1]

참조

_[1] 서적 Oracle Privacy Security Auditing: Includes Federal Law Compliance with HIPAA, Sarbanes Oxley and the Gramm Leach Bliley Act GLB https://books.google[...] Rampant TechPress 2003-00-00
_[2] 웹사이트 DDL Events - SQL Server https://docs.microso[...] 2023-03-15
_[3] 웹사이트 PostgreSQL: Documentation: 9.0: CREATE TRIGGER https://www.postgres[...] 2015-10-08
_[4] 웹사이트 Triggers. MySQL 5.0 added limited support for triggers https://downloads.my[...] Oracle Corporation 2020-03-04
_[5] 웹사이트 MySQL :: MySQL 8.0 Reference Manual :: 25.3.1 Trigger Syntax and Examples https://dev.mysql.co[...]
_[6] 웹사이트 Autonomous transactions http://www.ibm.com/d[...] 2009-07-30
_[7] 웹사이트 6 Using Triggers https://docs.oracle.[...]
_[8] 웹사이트 Oracle's Documentation on Sequences http://docs.oracle.c[...]
_[9] 웹사이트 Oracle SQL Functions – The Complete List https://www.database[...] 2014-12-26
_[10] 웹사이트 Database PL/SQL Language Reference https://docs.oracle.[...]

본 사이트는 AI가 위키백과와 뉴스 기사,정부 간행물,학술 논문등을 바탕으로 정보를 가공하여 제공하는 백과사전형 서비스입니다.
모든 문서는 AI에 의해 자동 생성되며, CC BY-SA 4.0 라이선스에 따라 이용할 수 있습니다.
하지만, 위키백과나 뉴스 기사 자체에 오류, 부정확한 정보, 또는 가짜 뉴스가 포함될 수 있으며, AI는 이러한 내용을 완벽하게 걸러내지 못할 수 있습니다.
따라서 제공되는 정보에 일부 오류나 편향이 있을 수 있으므로, 중요한 정보는 반드시 다른 출처를 통해 교차 검증하시기 바랍니다.

문의하기 : help@durumis.com