데이터베이스 트리거

Database trigger

데이터베이스 트리거는 데이터베이스의 특정 테이블 또는 뷰에서 특정 이벤트에 응답하여 자동으로 실행되는 절차 코드입니다. 트리거는 대부분 데이터베이스 정보의 무결성을 유지하는 데 사용됩니다. 예를 들어, 새로운 기록(신규 근로자를 나타내는)이 직원 테이블에 추가될 때 세금, 휴가 및 급여의 테이블에도 새로운 기록이 생성되어야 합니다. 트리거는 예를 들어 직원의 이전 급여를 추적하기 위해 기록 데이터를 기록하는 데 사용할 수도 있습니다.

DBMS의 트리거

아래는 일부 인기 있는 DBMS가 트리거를 지원하는 방법에 대한 일련의 설명을 따릅니다.

오라클

Oracle 10g는 데이터가 수정될 때 발생하는 트리거(및 PL/SQL 코드 실행) 외에도 스키마 수준의 개체(즉, 테이블)가 수정될 때 및 사용자 로그온 또는 로그오프 이벤트가 발생할 때 발생하는 트리거를 지원합니다.

스키마 수준 트리거

  • 작성 후
  • 변경 전
  • 애프터 알터
  • 드롭 전
  • 드롭 후
  • 삽입 전

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

  1. 행 레벨 트리거: 행의 열 값이 변경되기 전 또는 변경된 후에 실행됩니다.
  2. 컬럼 레벨 트리거: 지정한 열이 변경되기 전 또는 변경된 후에 실행됩니다.
  3. 각 행 유형에 대해: 삽입/업데이트/삭제의 영향을 받는 결과 집합의 각 행에 대해 이 트리거가 한 번 실행됩니다.
  4. 각 문 유형에 대해: 이 트리거는 전체 결과 집합에 대해 한 번만 실행되지만 문이 실행될 때마다 실행됩니다.

시스템 레벨 트리거

Oracle 8i에서 데이터베이스 이벤트(로그온, 로그오프, 시작)는 Oracle 트리거를 실행할 수 있습니다.[1]

마이크로소프트 SQL 서버

Microsoft SQL Server for DDL 트리거에서 사용 가능한 모든 부팅 이벤트 목록은 Microsoft Docs에서 사용할 수 있습니다.[2]

트리거(또는 수정 후 데이터 테스트)에서 조건부 작업을 수행하는 것은 임시 삽입 및 삭제 테이블에 액세스하여 수행됩니다.

포스트그레SQL

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

  • SQL을 통해 특정 열에 대한 업데이트 시 트리거를 실행할 수 있습니다. Postgre 버전 9.0 기준SQL 이 기능은 Postgre에서도 구현됩니다.SQL.
  • 이 표준은 트리거된 작업으로 CREATE TABLE과 같이 SELECT, INSERT, UPDATE 이외의 여러 SQL 문을 실행할 수 있도록 합니다. 이는 CREATE TABLE을 호출하기 위한 저장 프로시저 또는 함수를 생성하는 것을 통해 수행할 수 있습니다.[3]

개요: 

만들다 트리거 이름. { 전에   끝나고 } { 사건의 [ 오어 ... ] }     ON 테이블 [ 위해서 [ 각각 ] { 배를 젓다   진술 } ]     실행 절차. fun name ( 논쟁들 )

불새

Firebird는 테이블마다 여러 행 수준, BEFORE 또는 AFTER, INSERT, UPDATE, DELETE(또는 이들의 임의의 조합) 트리거를 지원합니다. 여기서 기본 테이블 변경은 항상 "추가"되며, 그렇지 않으면 모호할 수 있는 트리거의 순서를 서로 상대적으로 지정할 수 있습니다(POSITION 절). 트리거는 또한 뷰에 존재할 수 있으며, 기본 업데이트 가능 뷰 로직을 대체하는 트리거 대신 항상 "대신" 트리거입니다. (버전 2.1 이전에는 업데이트 가능으로 간주되는 뷰의 트리거가 기본 로직에 추가하여 실행됩니다.)

Firebird는 (오라클과 같이) 돌연변이 테이블 예외를 발생시키지 않으며, 기본적으로 트리거는 nest와 recursion 모두 필요에 따라 발생합니다(SQL Server는 기본적으로 nesting은 허용하지만 recursion은 허용하지 않습니다). Firebird의 트리거는 NEW 및 OLD 컨텍스트 변수(Inserted 및 Delete table이 아님)를 사용하며 트리거의 현재 사용을 나타내는 UPDATING, INSERTING 및 DELETING 플래그를 제공합니다. 

{만들다   다시 만들기   만들다 오어 바꾸다} 트리거 이름. 위해서 {테이블 이름.   보다 이름.}  [활동적인   활발하지 않은]  {전에   끝나고}  {삽입 [오어 갱신하다] [오어 DELETE]   갱신하다 [오어 삽입] [오어 DELETE]   DELETE [오어 갱신하다] [오어 삽입] }  [위치 n] ~하듯이 시작한다.  .... 끝.

Firebird 버전 2.1에서는 다음과 같은 데이터베이스 수준 트리거를 추가로 지원합니다.

  • CONNECT(여기서 제기된 예외로 인해 연결이 완료되지 않음)
  • 연결 끊기
  • 트랜잭션 시작
  • 트랜잭션 커밋(여기서 제기된 예외로 인해 트랜잭션이 커밋되지 않거나 2단계 커밋이 관련된 경우 준비할 수 없음)
  • 트랜잭션 롤백

데이터베이스 수준 트리거는 다중 테이블 제약 조건을 적용하거나 구체화된 뷰를 에뮬레이션하는 데 도움이 될 수 있습니다. TRANSACTION COMMIT 트리거에서 예외가 발생하면 트리거에 의해 지금까지 변경된 내용이 롤백되고 클라이언트 응용 프로그램에 통지되지만 트랜잭션은 COMMIT 요청이 없었던 것처럼 활성화된 상태로 유지됩니다. 클라이언트 응용 프로그램은 계속 변경하고 COMMIT을 다시 요청할 수 있습니다.

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

{만들다   다시 만들기   만들다 오어 바꾸다} 트리거 이름.  [활동적인   활발하지 않은] ON  {연결하다   연결 끊기   거래. 시작   거래. 저지르다   거래. 롤백}  [위치 n] ~하듯이 시작한다.  ..... 끝.

MySQL/MariaDB

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

버전 8.0부터는 DDL(Data Definition Language) 트리거와 DML(Data Manipulation Language) 트리거를 허용합니다. 또한 DDL 트리거 유형(AFTER 또는 BEFORE) 중 하나를 사용하여 트리거를 정의할 수 있습니다. 이들은 CREATE TRIGHTER 절을 사용하여 생성되고 DROP TRIGHTER 절을 사용하여 삭제됩니다. 이벤트가 발생할 때 호출되는 문은 각 행에 대한 절 뒤에 키워드(SET 또는 BEGIN)가 정의되며, 이는 뒤따르는 것이 각각 식인지 문인지를 나타냅니다.[5]

IBM DB2 LUW

LUW용 DB2(LUW는 Linux, Unix, Windows를 의미함)로 알려진 분산 시스템용 IBM DB2는 다음 세 가지 트리거 유형을 지원합니다. 트리거 전, 트리거 후 및 트리거 대신. 문 수준 및 행 수준 트리거가 모두 지원됩니다. 테이블에 동일한 작업에 대한 트리거가 더 많은 경우에는 트리거 생성 데이터에 의해 점화 순서가 결정됩니다. 버전 9.7 이후 IBM DB2는 자율 트랜잭션을 지원합니다.[6]

트리거 전에 데이터를 확인하고 작동을 허용해야 하는지 여부를 결정합니다. 트리거 이전부터 예외가 발생하면 작업이 중단되고 데이터가 변경되지 않습니다. 트리거가 읽기 전에만 DB2에서 트리거 전에는 데이터를 수정할 수 없습니다. 요청된 변경이 수행된 후 사후 처리를 위해 트리거가 설계된 후. 트리거가 데이터를 테이블에 쓸 수 있고 다른 일부[which?] 데이터베이스와 달리 트리거가 작동하는 테이블을 포함한 모든 테이블에 쓸 수 있습니다. 트리거 대신 보기를 쓸 수 있도록 만들기 위한 것입니다.

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

SQLite

만들다 [TEMP   임시의] 트리거 [한다면 것은 아니다. 존재한다] [database_name .] trigger_name [전에   끝나고   대신 오프] {DELETE   삽입   갱신하다 [오프 columnn_name [, columnn_name]...]}  ON {table_name   view_name}    [위해서 각각 배를 젓다] [언제 조건. 아. 의무적인 ] 시작한다.    ... 끝.

SQLite는 행 수준 트리거만 지원하고 문 수준 트리거는 지원하지 않습니다.

SQLite에서 지원되지 않는 업데이트 가능 보기는 트리거 대신 에뮬레이션할 수 있습니다.

XML 데이터베이스

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

Sedna의 트리거는 데이터베이스에 저장된 XML 문서의 모든 노드에 설정됩니다. 이러한 노드가 업데이트되면 트리거는 자동으로 XQuery 쿼리를 실행하고 본체에 지정된 업데이트를 실행합니다. 예를 들어, 다음 트리거는 이 사용자가 참조한 공개 경매가 있는 경우 사용자 노드 삭제를 취소합니다. 

만들다 트리거 "트리거 3"     전에 DELETE     ON 의사()"auction")/위치//사람인     위해서 각각 노드     두     {         한다면 (존재한다($어디에//오픈_/응찰자/인감/@사람=$늙은/@id))         그리고나서 ( )         또 다른 $늙은;     }

행 및 문 수준 트리거

트리거 동작의 작동 방식을 이해하려면 Row 및 Statement 레벨 트리거라는 두 가지 주요 유형의 트리거를 알고 있어야 합니다. 양자의 구별은 트리거 내의 코드가 몇 번 실행되는지, 몇 시에 실행되는지입니다.

특정 테이블에 대한 UPDATE에서 호출되는 트리거가 있다고 가정해 보겠습니다. 행 수준 트리거는 UPDATE의 영향을 받는 각 행에 대해 한 번 실행됩니다. UPDATE 명령의 영향을 받는 행이 없을 경우 트리거가 트리거 내에서 코드를 실행하지 않는 것을 염두에 두어야 합니다. 문 수준 트리거는 UPDATE의 영향을 받는 행 수에 관계없이 한 번 호출됩니다. 여기서 중요한 것은 UPDATE 명령이 행에 영향을 주지 않았더라도 트리거 내의 코드는 한 번 실행된다는 것입니다.

BEFORE 및 AFTER 옵션을[7] 사용하면 트리거 호출 시점이 결정됩니다. 특정 테이블에 INSERT에서 호출되는 트리거가 있다고 가정합니다. 트리거가 BEFORE 옵션을 사용하는 경우 테이블에 삽입하기 전에 트리거 내의 코드가 실행됩니다. BEFORE 트리거의 일반적인 용도는 INSERT의 입력 값을 확인하거나 그에 따라 값을 수정하는 것입니다. 이제 AFT를 사용하는 트리거가 있다고 가정해 보겠습니다. 테이블에 INSERT가 발생한 후 트리거 내의 코드가 실행됩니다. 이 트리거의 예로는 데이터베이스에 삽입한 사용자에 대한 감사 기록을 작성하고 변경 사항을 추적하는 것이 있습니다. 이러한 옵션을 사용할 때 몇 가지 사항을 염두에 두어야 합니다. BEFORE 옵션을 사용하면 테이블을 수정할 수 없으므로 입력 유효성 검사가 실용적으로 사용됩니다. AFT 트리거를 사용하면 감사 내역 테이블에 삽입하는 것과 같은 테이블을 수정할 수 있습니다.

문인지 행 수준인지 확인하기 위해 트리거를 작성할 때 행 수준에 대한 FOR EVERY ROW 절을 포함하거나 문 수준에 대한 절을 생략합니다. 트리거 재귀가 가능하여 원하지 않는 동작을 유발할 수 있으므로 트리거 내에서 추가 INSERT/UPDATE/DELETE 명령을 사용하지 않도록 주의해야 합니다. 아래 예에서는 각 트리거가 다른 테이블을 수정하고 있으며 수정되는 항목을 살펴봄으로써 다양한 트리거 유형이 사용되는 경우의 일반적인 응용 프로그램을 확인할 수 있습니다.

다음은 영향을 받는 각 행에 대한 업데이트 후에 호출되는 행 수준 트리거의 Oracle 구문 예입니다. 이 트리거는 전화번호부 데이터베이스로 업데이트할 때 호출됩니다. 트리거가 호출되면 pone_book_audit이라는 이름의 별도 테이블에 항목을 추가합니다. 또한 이 예제에서 audit_id_sequence.nexVal은 phone_book_audit 테이블에서 고유한 기본 키를 생성하는 데 사용됩니다.[8] 

만들다 오어 교체하다 트리거 폰_북_   끝나고 갱신하다 ON 폰_북 위해서 각각 배를 젓다 시작한다.   삽입 안으로 폰_북_      (audit_id,감사_변경, audit_l_name, audit_f_name, audit_old_phone_number, audit_new_phone_number, 감사_날짜)      가치     (audit_id_sequence.넥스트발,'업데이트', :늙은.last_name, :늙은.first_name, :늙은.phone_number, :신규.phone_number, SYSTATE); 끝.;

이제 성이 '존스'인 사람들을 위해 전화_북 테이블에서 업데이트를 호출합니다. 

갱신하다 폰_북 세트 phone_number = '111-111-1111' 어디에 last_name = '존스';
감사_ID 감사_변경 F_Name L_Name 신규_폰_번호 구_전화_번호 감사_날짜
1 갱신하다 조던 존스 111-111-1111 098-765-4321 02-5월14일
2 갱신하다 메간 존스 111-111-1111 111-222-3456 02-5월14일


phone_number_audit 테이블이 이제 두 개의 항목으로 채워집니다. 데이터베이스에 '존스'라는 성을 가진 두 개의 항목이 있기 때문입니다. 업데이트를 통해 두 개의 개별 행 값이 수정되었기 때문에 생성된 트리거는 각 수정 후 한 번씩 두 번 호출되었습니다.

After-statement-level 트리거

phone_book 테이블에 대한 UPDATE 후에 호출되는 Oracle 구문 문 트리거입니다. 트리거가 호출되면 phone_book_edit_history 테이블에 삽입됩니다. 

만들다 오어 교체하다 트리거 폰_북_히스토리   끝나고 갱신하다 ON 폰_북 시작한다.   삽입 안으로 폰_북_편집_히스토리      (audit_history_id, 사용자 이름, 수정., 편집_날짜)      가치     (audit_history_id_sequence.넥스트발, 사용자,'업데이트', SYSTATE); 끝.;

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

갱신하다 폰_북 세트 phone_number = '111-111-1111' 어디에 last_name = '존스';
감사_이력_ID 사용자 이름 수정. 편집_날짜
1 HAUSCHBC 갱신하다 02-5월14일

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

각 행 수준 트리거 이전

이 예에서는 WINE 조건을 사용하여 INSERT를 수정하는 BEFORE EVER ROW 트리거를 보여줍니다. 성이 10자보다 크면 SUCTR 함수를[9] 사용하여 last_name 열 값을 축약형으로 변경합니다. 

만들다 오어 교체하다 트리거 폰_북_삽입   전에 삽입 ON 폰_북 위해서 각각 배를 젓다   언제 (길이(신규.last_name) > 10) 시작한다.     :신규.last_name := 서브스트레이트(:신규.last_name,0,1); 끝.;

이제 큰 이름을 가진 사람의 INSERT를 수행합니다. 

삽입 안으로 폰_북 가치 (6, 'VeryVeryLong Last Name', '에린', '미니애폴리스', 'MN', '989 유니버시티 드라이브', '123-222-4456', 55408, TO_DATE('11/21/1991', 'MM/DD/YYY'));
개인_ID 성_이름 First_Name 도시 상태_약어 주소. 전화번호 Zip_code DOB
6 V Erin입니다. 미니애폴리스 엠엔 989 유니버시티 드라이브 123-222-4456 55408 21-11월-91일

트리거는 실행되기 전에 INSERT의 값을 수정하면서 위의 결과와 같이 작동했습니다.

문장 전 수준 트리거

BEFORE 문 트리거를 사용하는 것은 데이터베이스 제한을 적용할 때 특히 유용합니다.[10] 이 예제에서는 테이블 폰_book에서 "SOMEUSER"라는 이름의 사용자에게 제한을 적용하는 방법을 보여줍니다. 

만들다 오어 교체하다 트리거 hauschbc    전에 삽입 ON 일부 사용자.폰_북 시작한다.     RASE_APPLATION_ERROR (          눔 => -20050,          msg => '오류 메시지가 여기로 갑니다.'); 끝.;

이제 삽입을 시도한 후 "SOMEUSER"가 로그인되면 다음 오류 메시지가 표시됩니다. 

SQL 오류: ORA-20050: 오류 메시지가 여기로 갑니다.

이와 같은 사용자 정의 오류는 num 변수를 정의할 수 있는 제한이 있습니다. 다른 수많은 미리 정의된 오류 때문에 이 변수는 -20,000에서 -20999 사이의 범위에 있어야 합니다.

참고문헌

  1. ^ Nanda, Arup; Burleson, Donald K. (2003). "9". In Burleson, Donald K. (ed.). Oracle Privacy Security Auditing: Includes Federal Law Compliance with HIPAA, Sarbanes Oxley and the Gramm Leach Bliley Act GLB. Oracle in-focus series. Vol. 47. Kittrell, North Carolina: Rampant TechPress. p. 511. ISBN 9780972751391. Retrieved 2018-04-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".
  3. ^ "PostgreSQL: Documentation: 9.0: CREATE TRIGGER". www.postgresql.org. 8 October 2015.
  4. ^ MySQL 5.0 참조 설명서. "트리거즈. MySQL 5.0은 트리거에 대한 제한된 지원을 추가했습니다.", 오라클 Corporation, 2020년 3월 4일에 검색되었습니다.
  5. ^ "MySQL :: MySQL 8.0 Reference Manual :: 25.3.1 Trigger Syntax and Examples".
  6. ^ "Autonomous transactions". www.ibm.com. July 30, 2009.
  7. ^ "6 Using Triggers". docs.oracle.com.
  8. ^ "Oracle's Documentation on Sequences". Archived from the original on 2011-12-01.
  9. ^ "Oracle SQL Functions – The Complete List". December 26, 2014.
  10. ^ "Database PL/SQL Language Reference". docs.oracle.com.

외부 링크