Oracle PL/SQL 트리거: 대신 및 복합 유형

PL/SQL의 트리거란 무엇입니까?

트리거가 저장됩니다 PL / SQL 프로그램들은 다음과 같은 것에 의해 실행됩니다. Oracle 엔진이 자동으로 작동할 때 DML 문 테이블에 삽입, 업데이트, 삭제와 같은 작업이 수행될 때 또는 특정 이벤트가 발생할 때 트리거가 실행됩니다. 트리거가 실행될 코드는 필요에 따라 정의할 수 있습니다. 트리거가 실행될 이벤트와 실행 시점을 선택할 수 있습니다. 트리거의 목적은 데이터베이스 정보의 무결성을 유지하는 것입니다.

트리거의 이점

트리거의 이점은 다음과 같습니다.

• 일부 파생 열 값을 자동으로 생성
• 참조 무결성 강화
• 테이블 접근에 대한 이벤트 로깅 및 정보 저장
• 감사
• Sync테이블의 동시 복제
• 보안 인증 부과
• 잘못된 거래 방지

트리거 유형 Oracle

트리거는 다음 매개변수를 기준으로 분류할 수 있습니다.

시기에 따른 분류

• 트리거 전: 지정된 이벤트가 발생하기 전에 실행됩니다.
• 트리거 이후: 지정된 이벤트가 발생한 후에 실행됩니다.
• 트리거 대신: 특수한 유형입니다. 다음 주제에서 더 자세히 알아보겠습니다. (DML에만 해당)

수준에 따른 분류

• STATEMENT 레벨 트리거: 지정된 이벤트 구문에 대해 한 번 실행됩니다.
• 행 수준 트리거: 지정된 이벤트에서 영향을 받은 각 레코드에 대해 실행됩니다. (DML 작업에만 해당)

이벤트에 따른 분류

• DML 트리거: 이 이벤트는 DML 이벤트(INSERT/UPDATE/DELETE)가 지정될 때 발생합니다.
• DDL 트리거: 이 이벤트는 DDL 이벤트(CREATE/ALTER)가 지정될 때 발생합니다.
• 데이터베이스 트리거: 이 이벤트는 지정된 데이터베이스 이벤트(로그온/로그오프/시작/종료)가 발생할 때 실행됩니다.

따라서 각 트리거는 위의 매개변수들의 조합입니다.

트리거를 만드는 방법

다음은 트리거를 생성하는 구문입니다. 아래 스크린샷은 해당 트리거 생성 구문을 보여줍니다. Oracle.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

구문 설명:

• 위 구문은 트리거 생성에 존재하는 다양한 선택적 문을 보여줍니다.
• BEFORE/AFTER는 이벤트 시간을 지정합니다.
• 삽입/업데이트/로그온/만들기/등. 트리거가 실행되어야 하는 이벤트를 지정합니다.
• ON 절은 위에서 언급한 이벤트가 유효한 객체를 지정합니다. 예를 들어, DML 트리거의 경우 DML 이벤트가 발생할 수 있는 테이블 이름이 됩니다.
• "각 행에 대해" 명령은 행 수준 트리거를 지정합니다.
• WHEN 절은 트리거가 실행되어야 하는 추가 조건을 지정합니다.
• 선언부, 실행부, 예외 처리부는 다른 것들과 동일합니다. PL/SQL 블록선언 부분과 예외 처리 일부 항목은 선택 사항입니다.

:NEW 및 :OLD 절

행 수준 트리거에서는 관련된 각 행에 대해 트리거가 실행됩니다. 그리고 때로는 DML 문 전후의 값을 알아야 할 때도 있습니다.

Oracle 행 수준 트리거에 이러한 값을 저장하기 위한 두 개의 절이 제공되었습니다. 이러한 절을 사용하여 트리거 본문 내에서 이전 값과 새 값을 참조할 수 있습니다.

• :새로운 - 트리거 실행 중에 기본 테이블/뷰의 열에 대한 새 값을 저장합니다.
• :오래된 – 트리거 실행 중에 기본 테이블/뷰 열의 이전 값을 유지합니다.

이 절은 DML 이벤트에 따라 사용해야 합니다. 아래 표는 각 절이 어떤 DML 문(INSERT/UPDATE/DELETE)에 유효한지 명시합니다.

트리거 대신

"INSTEAD OF 트리거"는 특별한 유형의 트리거입니다. DML 트리거에서만 사용되며, 복잡한 뷰에서 DML 이벤트가 발생할 때 사용됩니다.

세 개의 기본 테이블로 구성된 뷰를 예로 들어 보겠습니다. 이 뷰에 대해 DML 이벤트가 발생하면, 데이터가 서로 다른 세 테이블에서 가져오기 때문에 뷰가 무효화됩니다. 따라서 이러한 경우에는 INSTEAD OF 트리거를 사용합니다. INSTEAD OF 트리거는 특정 이벤트에 대해 뷰를 수정하는 대신 기본 테이블을 직접 수정하는 데 사용됩니다.

예 1 : 이 예제에서는 두 개의 기본 테이블(Table_1은 직원 테이블이고 Table_2는 부서 테이블)을 사용하여 복합 뷰를 생성할 것입니다.

다음으로, INSTEAD OF 트리거를 사용하여 이 복잡한 뷰의 위치 세부 정보를 업데이트하는 방법을 살펴보겠습니다. 또한 트리거에서 :NEW 및 :OLD가 어떻게 유용하게 사용되는지도 알아보겠습니다. 예제는 다음 단계에 따라 진행됩니다.

• 1단계: 적절한 열을 포함하는 'emp' 및 'dept' 테이블 생성
• 2단계: 표에 샘플 값 채우기
• 3단계: 위에서 생성한 테이블에 대한 뷰 생성
• 4단계: INSTEAD OF 트리거 실행 전 뷰 업데이트
• 5단계: INSTEAD OF 트리거 생성
• 6단계: INSTEAD OF 트리거 실행 후 뷰 업데이트

1단계) 적절한 열을 가진 'emp' 및 'dept' 테이블을 생성합니다.

아래 스크린샷은 'emp' 및 'dept' 기본 테이블이 생성되는 과정을 보여줍니다. Oracle.

CREATE TABLE emp(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager VARCHAR2(50),
dept_no NUMBER);
/

CREATE TABLE dept(
Dept_no NUMBER,
Dept_name VARCHAR2(50),
LOCATION VARCHAR2(50));
/

Code 설명

• Code 1-7행: 'emp' 테이블 생성.
• Code 8-12행: '부서' 테이블 생성.

출력:

Table Created

단계 2) 이제 테이블을 만들었으니 샘플 값으로 채워 넣겠습니다.

아래 스크린샷은 'dept' 및 'emp' 테이블에 삽입되는 샘플 행을 보여줍니다.

BEGIN
INSERT INTO DEPT VALUES(10,'HR','USA');
INSERT INTO DEPT VALUES(20,'SALES','UK');
INSERT INTO DEPT VALUES(30,'FINANCIAL','JAPAN');
COMMIT;
END;
/

BEGIN
INSERT INTO EMP VALUES(1000,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code 설명

• Code 13-19행: '부서' 테이블에 데이터를 삽입합니다.
• Code 20-26행: 'emp' 테이블에 데이터를 삽입합니다.

출력:

PL/SQL procedure completed

단계 3) 위에서 생성한 테이블에 대한 뷰를 생성합니다.

아래 스크린샷은 복합 뷰가 생성된 후 쿼리되는 과정을 보여줍니다.

CREATE VIEW guru99_emp_view(
Employee_name,dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM guru99_emp_view;

Code 설명

• Code 27-32행: 'guru99_emp_view' 뷰를 생성합니다.
• Code 33행: guru99_emp_view를 쿼리하는 중입니다.

출력:

View created

단계 4) INSTEAD OF 트리거 발생 전의 뷰 업데이트.

아래 스크린샷은 복합 뷰에 대한 업데이트 시도와 그로 인해 발생한 오류를 보여줍니다.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

Code 설명

• Code 34-38행: "XXX"의 위치를 ​​'FRANCE'로 업데이트하세요. 복합 뷰에 대해 DML 문을 직접 실행할 수 없기 때문에 예외가 발생했습니다.

출력:

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

단계 5) 이전 단계에서 뷰를 업데이트하는 동안 발생한 오류를 방지하기 위해 이번 단계에서는 "INSTEAD OF 트리거"를 사용하겠습니다.

아래 스크린샷은 INSTEAD OF 트리거 생성 과정을 보여줍니다.

CREATE TRIGGER guru99_view_modify_trg
INSTEAD OF UPDATE
ON guru99_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

Code 설명

• Code 39행: 'guru99_emp_view' 뷰의 행 레벨에서 'UPDATE' 이벤트에 대한 INSTEAD OF 트리거를 생성합니다. 이 트리거에는 기본 테이블 'dept'의 위치 정보를 업데이트하는 업데이트 문이 포함되어 있습니다.
• Code 44행: 업데이트 문은 ':NEW'와 ':OLD'를 사용하여 업데이트 전후의 열 값을 찾습니다.

출력:

Trigger Created

단계 6) INSTEAD OF 트리거 실행 후 뷰가 업데이트됩니다. 이제 "INSTEAD OF 트리거"가 이 복잡한 뷰의 업데이트 작업을 처리하므로 오류가 발생하지 않습니다. 코드가 실행되면 직원 XXX의 근무지가 "일본"에서 "프랑스"로 업데이트됩니다.

아래 스크린샷은 INSTEAD OF 트리거를 통해 업데이트가 성공적으로 완료되고 화면이 새로 고쳐진 것을 보여줍니다.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

Code 설명 :

• Code 49-53행: "XXX"의 위치를 ​​'FRANCE'로 업데이트했습니다. 'INSTEAD OF' 트리거가 뷰에 대한 실제 업데이트 문을 중지하고 기본 테이블 업데이트를 수행했기 때문에 성공적으로 완료되었습니다.
• Code 55행: 업데이트된 기록을 확인하는 중입니다.

출력:

PL/SQL procedure successfully completed

복합 트리거

복합 트리거는 하나의 트리거 본문에서 네 개의 타이밍 지점 각각에 대해 동작을 지정할 수 있는 트리거입니다. 지원하는 네 가지 타이밍 지점은 다음과 같습니다.

• 진술 전 – 수준
• 행 앞 - 수준
• 행 이후 – 수준
• AFTER STATEMENT – 레벨

이 기능은 서로 다른 시점에 실행되는 동작들을 하나의 트리거로 결합할 수 있는 기능을 제공합니다.

아래 스크린샷은 네 개의 타이밍 섹션으로 구성된 복합 트리거 구문을 보여줍니다.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
ON <name of underlying object>
<Declarative part>
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

구문 설명:

• 위 구문은 '복합' 트리거를 생성하는 방법을 보여줍니다.
• 선언 부분은 트리거 본문의 모든 실행 블록에 공통입니다.
• 이 네 개의 타이밍 블록은 어떤 순서로든 배치할 수 있습니다. 네 개의 타이밍 블록을 모두 사용할 필요는 없으며, 필요한 타이밍만 포함하는 복합 트리거를 생성할 수도 있습니다.

예 1 : 이 예제에서는 급여 열에 기본값인 5000을 자동으로 채우는 트리거를 생성할 것입니다.

아래 스크린샷은 복합 트리거 예시와 그 출력 결과를 보여줍니다.

CREATE TRIGGER emp_trig
FOR INSERT
ON emp
COMPOUND TRIGGER
BEFORE EACH ROW IS
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,'CCC',15000,'AAA',30);
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

Code 설명 :

• Code 2-10행: 복합 트리거를 생성합니다. 이 트리거는 '행 삽입 전' 시점에 실행되어 급여 필드에 기본값 '5000'을 입력합니다. 즉, 레코드를 테이블에 삽입하기 전에 급여 필드를 기본값 '5000'으로 변경합니다.
• Code 11-14행: 'emp' 테이블에 레코드를 삽입합니다.
• Code 16행: 삽입된 레코드를 확인합니다.

출력:

Trigger created

PL/SQL procedure successfully completed.

트리거 활성화 및 비활성화

트리거는 활성화 또는 비활성화할 수 있습니다. 트리거를 활성화 또는 비활성화하려면 해당 트리거에 대한 ALTER(DDL) 문을 작성해야 합니다.

다음은 트리거를 활성화/비활성화하는 구문입니다.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

구문 설명:

• 첫 번째 구문은 단일 트리거를 활성화/비활성화하는 방법을 보여줍니다.
• 두 번째 문은 특정 테이블의 모든 트리거를 활성화/비활성화하는 방법을 보여줍니다.

자주 묻는 질문