PL/SQL 트리거, 시퀀스, 시노님

지난 글에서 패키지의 구조와 오버로딩을 다뤘다. 이번에는 PL/SQL의 세 가지 보조 객체, 트리거·시퀀스·시노님을 함께 살펴본다.

트리거

트리거는 테이블이나 뷰에 DML이 발생했을 때, 또는 DDL·데이터베이스 이벤트가 발생했을 때 자동으로 실행되는 PL/SQL 블록이다.

트리거 실행 시점 분류

시점	레벨	:NEW/:OLD	주요 용도
BEFORE + 행	각 행	:NEW 수정 가능	값 보정, PK 자동 채움
AFTER + 행	각 행	읽기만	감사 로그, 연쇄 DML
BEFORE/AFTER + 문장	1회	없음	집계 초기화, 로깅
INSTEAD OF (뷰)	각 행	읽기만	복잡 뷰 DML 대체

INSERTING, UPDATING, DELETING 조건부 서술어로 이벤트 유형을 구분한다.

CREATE OR REPLACE TRIGGER trg_audit_emp
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    :NEW.created_at := SYSDATE;
    :NEW.created_by := USER;
  END IF;
  :NEW.updated_at := SYSDATE;
  :NEW.updated_by := USER;
END trg_audit_emp;
/

---

INSTEAD OF 트리거

조인 뷰나 집계 뷰처럼 직접 DML이 불가능한 뷰에 적용한다. 뷰에 대한 DML을 대신(INSTEAD OF) 실행한다.

-- 두 테이블 조인 뷰
CREATE OR REPLACE VIEW v_emp_dept AS
  SELECT e.employee_id, e.last_name, d.department_name
  FROM   employees e JOIN departments d
         ON e.department_id = d.department_id;

-- INSTEAD OF 트리거로 뷰에 UPDATE 허용
CREATE OR REPLACE TRIGGER trg_upd_v_emp_dept
INSTEAD OF UPDATE ON v_emp_dept
FOR EACH ROW
BEGIN
  UPDATE employees
  SET    last_name = :NEW.last_name
  WHERE  employee_id = :OLD.employee_id;
END;
/

---

트리거 주의사항

돌연변이 테이블(Mutating Table): 행 레벨 트리거 안에서 트리거를 발생시킨 같은 테이블을 쿼리하거나 DML하면 ORA-04091 오류가 발생한다.

-- ORA-04091 발생 예
CREATE OR REPLACE TRIGGER trg_check_dept_count
BEFORE INSERT ON employees FOR EACH ROW
DECLARE v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt        -- 같은 테이블 조회: 오류!
  FROM   employees
  WHERE  department_id = :NEW.department_id;
END;

해결책은 COMPOUND 트리거로 통계를 AFTER STATEMENT 단계에서 처리하거나, 별도 패키지 전역 변수에 정보를 모아 AFTER 문장 트리거에서 일괄 처리하는 것이다.

대량 DML 성능: 수백만 행 DML 시 트리거가 행마다 실행되면 성능이 저하된다. ALTER TRIGGER trg_name DISABLE; → 대량 DML → ENABLE; 패턴을 고려한다.

---

시퀀스

시퀀스는 유일한 정수를 순서대로 생성하는 데이터베이스 객체다.

CREATE SEQUENCE seq_order_id
  START WITH 1000
  INCREMENT BY 1
  CACHE 20        -- 메모리에 20개 미리 생성 (성능 개선)
  NOCYCLE
  NOORDER;        -- RAC 순서 보장 불필요 시

-- 사용
INSERT INTO orders (order_id, customer_id, amount)
VALUES (seq_order_id.NEXTVAL, 100, 9900);

-- 현재 값 확인 (세션에서 NEXTVAL 1회 이상 호출 후)
SELECT seq_order_id.CURRVAL FROM dual;

CACHE 옵션은 성능상 중요하다. Oracle RAC 환경에서 NOORDER를 유지하면 각 인스턴스가 캐시에서 독립적으로 값을 소비해 성능이 높지만 순서는 보장되지 않는다. 순서가 필요하면 ORDER 옵션을 추가한다.

Oracle 12c+ IDENTITY 컬럼

Oracle 12c부터는 트리거 없이 테이블 정의에서 직접 자동 증가 컬럼을 선언할 수 있다.

CREATE TABLE products (
  product_id   NUMBER GENERATED ALWAYS AS IDENTITY
               (START WITH 1 INCREMENT BY 1 CACHE 20),
  product_name VARCHAR2(100) NOT NULL,
  CONSTRAINT pk_products PRIMARY KEY (product_id)
);

-- INSERT 시 product_id 생략 (ALWAYS는 직접 값 삽입 금지)
INSERT INTO products (product_name) VALUES ('노트북');

GENERATED ALWAYS는 직접 값 입력을 막고, GENERATED BY DEFAULT는 값을 명시하지 않을 때만 자동 생성한다.

---

시노님

시노님은 다른 스키마 객체에 대한 **별칭(Alias)**이다. 다른 스키마 객체를 스키마 이름 없이 접근하거나, 긴 이름을 짧게 줄이는 데 사용한다.

-- 프라이빗 시노님 (현재 사용자만)
CREATE SYNONYM emp FOR hr.employees;
SELECT * FROM emp WHERE department_id = 60;

-- 퍼블릭 시노님 (모든 사용자, DBA 권한 필요)
CREATE PUBLIC SYNONYM employees FOR hr.employees;

-- 시노님 삭제
DROP SYNONYM emp;
DROP PUBLIC SYNONYM employees;

-- 현재 시노님 목록
SELECT synonym_name, table_owner, table_name
FROM   user_synonyms
UNION ALL
SELECT synonym_name, table_owner, table_name
FROM   all_synonyms
WHERE  owner = 'PUBLIC';

Oracle은 이름 해석 순서를 로컬 객체 → 프라이빗 시노님 → 퍼블릭 시노님 순으로 처리한다. 시노님은 객체가 아니라 이름 포인터이므로, 가리키는 객체가 없어도 생성은 가능하다.

---

지난 글: PL/SQL 패키지

다음 글: PL/SQL 컬렉션

읽어주셔서 감사합니다. 😊