PL/SQL (20) - 트리거 (Trigger)

​

트리거란?

- 트리거도 Table, View 와 같은 하나의 DB Object 이다.

- 조준을 하고 있는 상태에서 이때다 하고 방아쇠를 당기는 순간 총알이 나가게 될 때 그 방아쇠를 트리거로 생각하면 된다.

trigger

1 (총포의) 방아쇠 2 (기계의) 제동기[장치], 제륜기(制輪機) 3 (분쟁 등의) 계기, 유인, 자극 4 【전자】 트리거 5 《속어》 =TRIGGERMAN

The trigger of a gun is a small lever which you pull to fire it. 트리거의 예를 들면, 데이터베이스 내에서 참조의 무결성을 유지하기 위해 어떤 프로시저를 자동으로 호출하는 것과 같은 행동이다. 트리거는 사용자가 데이터를 삽입하거나 삭제하는 등과 같은, 데이터 변경에 관한 시도를 했을 때 효력을 나타낸다. 트리거는 지정된 어떤 변경이 시도되면, 일련의 행동들을 취하도록 시스템에게 알릴 수 있다. 트리거는 부정확하고, 허가 받지 않았으며, 일관성이 없는 데이터 변경을 방지함으로써, 데이터베이스의 무결성을 유지하는데 도움을 준다.

 

예를 들어보겠다.

Employee 테이블에 대해 DDL 권한이 있는 사용자 개똥이가 있다고 치자. 평소에는 DDL 권한이 있으므로, Employee 테이블에 대해 DROP, CREATE, ALTER 등 구조적으로 추가/삭제/수정을 할 수 있을 것이다. 그런데 이 DDL 권한을 개똥이가 마구 남발하는 바람에 테이블 구조가 엉망이 되었다. 그래서 관리자는 개똥이가 가진 DDL 권한을 빼려고 했으나, 해당 테이블은 업무에 필요한 경우 개똥이가 담당하는 테이블이기 때문에 아예 DDL 권한을 빼는 것은 불가능 하였다.

​

고심한 끝에 관리자는 관리자의 승인이 있을 경우에만 DDL 권한을 사용할 수 있도록 트리거를 달아놓는다. 트리거의 내용에는 Employee 테이블에 대해 DDL 문장이 실행되면(이벤트 발생), 이를 실행하지 않고 에러를 출력해주도록 짜여있다 (조건에 맞는 이벤트가 발생하였기 때문에 트리거동작).

​

다음날, 개똥이는 DDL 작업을 하려고 하였으나, 갑자기 "DDL 작업을 할 수 없습니다. 관리자에게 문의하세요." 라는 메시지를 받게 된다. 관리자에게 문의한 결과 트리거를 달아놓았으니 승인을 받은 뒤 작업하라는 지시를 받게된다. 이후 관리자는 작업이 있을 경우, 트리거를 비활성화(Disable) 시켰다가, 작업이 끝나면 다시 활성화(Enable) 시킨다.

 

- 트리거는 특정 이벤트에 대한 반응으로 일어나는 절차적 로직을 작성하는데 사용한다.

- 트리거를 사용해서 복잡한 업무 규칙을 실행할 수 있다.

- 트리거를 생성하려면 CREATE TRIGGER 권한이 필요하다.

 

 

트리거의 목적

- 업무 규칙 실행

- 참조 무결성 유지

- 보안

- 변경 내용 기록 관리

- 컬럼값 생성

- 데이터 복제

 

 

트리거의 종류

- DML 트리거

- DDL 트리거

- Instead-of 트리거

- 이벤트 트리거

 

 

트리거 정의

 

(1) DML문에 대한 트리거 (INSERT, DELETE, UPDATE)

CREATE [OR REPLACE] TRIGGER [스키마.]트리거이름     {BEFORE | AFTER} DML이벤트 [OR DML이벤트]     ON [스키마.]테이블이름     [[REFERENCING 상호관계이름] FOR EACH ROW     [WHEN (조건)]] DECLARE     ... BEGIN     ... END; CALL procudure_name;

 

- DML이벤트

트리거를 동작하게 하는 SQL문을 말한다. INSERT, UPDATE, DELETE 가 들어가며 OR로 중복할 수 있다.

{INSERT|DELETE|UPDATE [OF 컬럼]} [OR 동사목록]

ex) INSERT OR DELETE OF emp_id (테이블의 모든 컬럼에 대해 INSERT가 발생하거나, emp_id 컬럼에 대해 DELETE가 발생 시)

 

- REFERENCING 절

{OLD AS 이전 별칭 | NEW AS 새 별칭[상호관계이름]}

상호관계이름은 :NEW :OLD

 

- 조건

Row-level 트리거에서만 사용할 수 있으며, 트리거의 실행을 제어한다.

 

- CALL

DECLARE ~ END 까지는 별도의 프로시저를 작성 후 CALL 로 대체할 수 있다.

 

 

(2) DDL문에 대한 트리거 (CREATE, ALTER, DROP)

CREATE [OR REPLACE] TRIGGER [스키마.]트리거이름     {BEFORE | AFTER} {DDL이벤트 [OR DDL이벤트] | 데이터베이스이벤트 [OR 데이터베이스이벤트]}     ON {DATABASE | [스키마.]SCHEMA}     [WHEN (조건)] DECLARE ... BEGIN ... END; CALL procudure_name;

- {CREATE|ALTER|DROP [OF 컬럼]} [OR 동사목록]

- DDL에 대한 트리거는 스키마 레벨에서 정의된다.

- ON 절에 SCOTT.SCHEMA 처럼 입력하면, Scott 유저의 모든 오브젝트에 대해 트리거가 동작한다.

- REFERENCING 절이 없다.

 

 

DML트리거

- INSERT, DELETE, UPDATE 에 대한 이벤트에 반응하는 트리거이다.

 

아래 예제는 employee 테이블에 dept_name 컬럼에 대해 Insert 나 Update 가 일어날 경우 부서명을 대문자로 변경시키는 트리거이다.

CREATE OR REPLACE TRIGGER employee_insert_update -- 트리거 이름     BEFORE INSERT OR UPDATE ON employee -- employee table을 INSERT,UPDATE 하기 전에 트리거가 동작한다.     FOR EACH ROW -- 각각의 row에 대해 실행된다. DECLARE     BEGIN         :NEW.dept_name := UPPER(:NEW.dept_name); -- :NEW는 새로운 값에 대한 기본 별칭. :OLD는 이전 값.     END; /

 

트리거 작성 후, 소문자로 된 부서를 INSERT하고 확인해보면, 대문자로 변경되어 저장된 것을 볼 수 있다.

 

SQL> insert into employee(dept_name) values ('financial');

SQL> select dept_name from employee;

 

DEPT_NAME

-------------------

FINANCIAL

 

 

트리거 실행 시점

- BEFORE : SQL문이 실행되기 전에 트리거 동작.

- AFTER : SQL문이 실행된 뒤 트리거 동작.

 

 

트리거 레벨

- Row-level : SQL문에 의해 영향을 받는 각 행에 대해 한번씩 실행.

- Statement-level : SQL문에 의해 영향을 받는 모든 행에 대해 한번만 실행.

 

 

- 데이터무결성 유지 예제

- 히스토리 관리 예제

 

 

트리거 관리

- ALL_TRIGGERS 뷰를 통해 자신이 소유하고 있는 트리거를 확인할 수 있다.

- 트리거에 대한 PL/SQL 코드를 보려면, USER_TRIGGERS 뷰의 TRIGGER_BODY 컬럼을 확인하면 된다.

- 트리거 정의를 테이블에서 추출하려면 더 번거롭기 때문에, 트리거 생성문을 .sql 파일로 만들어 놓고, 필요시 파일에서 수정해서 재적용하는 방법을 사용한다.

 

 

트리거 DISABLE / ENABLE

- 트리거를 임시적으로 동작하지 않게 하려면 ALTER 문을 사용해서 DISABLE 시킨다.

- 한꺼번에 많은 양의 작업이 있을 경우, 트리거가 적용되어 있으면 부하를 유발시킬 수 있기 때문에 DISABLE 시키고 작업을 한다.

ALTER TRIGGER trigger_name DISABLE; ALTER TRIGGER trigger_name ENABLE;

 

그 밖의 트리거

- 데이터베이스 시작/종료 시 발생되는 트리거 (데이터베이스 레벨에서 정의된다. ADMINISTER DATABASE TRIGGER 권한 필요.)

- 서버 오류 발생 시 발생되는 트리거 (데이터베이스 레벨)

- 로그온/오프 시 발생되는 트리거 (데이터베이스 레벨 / 스키마 레벨)

- 오브젝트 CREATE, ALTER, DROP 시 발생되는 트리거 (스키마 레벨에서 정의된다. REFERENCING 절이 없다)

 

 

이벤트 속성

- 오라클에서 제공하는 속성값을 이용하여 트리거를 작성하면 편리하다.

- (예) logon_username := sys.login_user; -- sys.login_user 값을 참조해서 사용자 이름을 찾아 login_username 변수에 넣는다.

 

 

데이터베이스 이벤트에 대한 트리거

- 부팅 시 오라클이 startup / shutdown 될 때 자동으로 로그를 남기도록 dbstart 스크립트에 명시되어 있으나, 스크립트를 사용하지 않고 매뉴얼로 작업 할 경우, startup / shutdown 에 대한 기록이 남지 않는다. 이 때 기록을 남기는 트리거를 생성할 수 있다.

CREATE TABLE uptime_log (     database_name VARCHAR2(20),     event_name VARCHAR2(20),     event_time DATE,     trigger_user VARCHAR2(30) );   CREATE OR REPLACE TRIGGER startup_log_trg AFTER STARTUP ON DATABASE BEGIN     INSERT INTO uptime_log VALUES (sys.database_name, sys.sysevent, sysdate, sys.login_user); -- 속성값 이용     COMMIT; END; /   CREATE OR REPLACE TRIGGER shutdown_log_trg AFTER SHUTDOWN ON DATABASE BEGIN     INSERT INTO uptime_log VALUES (sys.database_name, sys.sysevent, sysdate, sys.login_user);     COMMIT; END; /