Oracle PL/SQL 트리거 튜토리얼: 복합 대신 [예]

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

트리거 다음에 의해 실행되는 저장된 프로그램입니다. Oracle 테이블에서 insert, update, delete와 같은 DML 문이 실행되거나 일부 이벤트가 발생하면 엔진이 자동으로 실행됩니다. 트리거 발생 시 실행될 코드는 요구 사항에 따라 정의할 수 있습니다. 트리거가 실행되어야 하는 이벤트와 실행 타이밍을 선택할 수 있습니다. 트리거의 목적은 데이터베이스의 정보 무결성을 유지하는 것입니다.

트리거의 이점

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

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

트리거 유형 Oracle

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

에 따른 분류 타이밍
BEFORE 트리거: 지정된 이벤트가 발생하기 전에 실행됩니다.
AFTER 트리거: 지정된 이벤트가 발생한 후에 실행됩니다.
INSTEAD OF Trigger: 특별한 유형입니다. 추가 주제에 대해 자세히 알아보게 됩니다. (DML에만 해당)
에 따른 분류 수평
STATEMENT 수준 트리거: 지정된 이벤트 문에 대해 한 번 실행됩니다.
ROW 수준 트리거: 지정된 이벤트에서 영향을 받은 각 레코드에 대해 실행됩니다. (DML에만 해당)
에 따른 분류 이벤트
DML 트리거: DML 이벤트가 지정되면 실행됩니다(INSERT/UPDATE/DELETE).
DDL 트리거: DDL 이벤트가 지정될 때 발생합니다(CREATE/ALTER).
DATABASE 트리거: 데이터베이스 이벤트가 지정되면 실행됩니다(LOGON/LOGOFF/STARTUP/SHUTDOWN).

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

트리거를 만드는 방법

다음은 트리거를 생성하는 구문입니다.

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 Trigger의 경우 DML 이벤트가 발생할 수 있는 테이블 이름이 됩니다.
"FOR EACH ROW" 명령은 ROW 레벨 트리거를 지정합니다.
WHEN 절은 트리거가 실행되어야 하는 추가 조건을 지정합니다.
선언부분, 실행부분, 예외처리 부분은 나머지 부분과 동일합니다. PL/SQL 블록. 선언 부분과 예외 처리 부분은 선택 사항입니다.

:NEW 및 :OLD 절

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

Oracle 이러한 값을 보유하기 위해 RECORD 수준 트리거에 두 개의 절을 제공했습니다. 이러한 절을 사용하여 트리거 본문 내부의 이전 값과 새 값을 참조할 수 있습니다.

:NEW – 트리거 실행 중에 기본 테이블/뷰의 열에 대한 새 값을 보유합니다.
:OLD – 트리거 실행 중 기본 테이블/뷰 열의 이전 값을 보유합니다.

이 절은 DML 이벤트를 기준으로 사용해야 합니다. 아래 표에서는 어떤 DML 문(INSERT/UPDATE/DELETE)에 어떤 절이 유효한지 지정합니다.

	INSERT	UPDATE	삭제
:새로운	유효한	유효한	유효하지 않은. 삭제 케이스에는 새로운 값이 없습니다.
:오래된	유효하지 않은. 삽입 케이스에 이전 값이 없습니다.	유효한	유효한

트리거 대신

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

3개의 기본 테이블에서 뷰가 생성되는 예를 생각해 보세요. 이 뷰에 대해 DML 이벤트가 발생하면 데이터가 3개의 다른 테이블에서 가져오기 때문에 유효하지 않게 됩니다. 따라서 이 INSTEAD OF 트리거가 사용됩니다. INSTEAD OF 트리거는 지정된 이벤트에 대한 뷰를 수정하는 대신 기본 테이블을 직접 수정하는 데 사용됩니다.

예제 1: 이 예제에서는 두 개의 기본 테이블에서 복잡한 뷰를 만들어 보겠습니다.

Table_1은 빈 테이블이고

Table_2는 부서 테이블입니다.

그런 다음 INSTEAD OF 트리거를 사용하여 이 복잡한 뷰에서 위치 세부 정보 문을 UPDATE하는 방법을 살펴보겠습니다. 또한 :NEW 및 :OLD가 트리거에서 어떻게 유용한지 살펴보겠습니다.

1단계: 적절한 열을 사용하여 'emp' 및 'dept' 테이블 만들기
2단계: 샘플 값으로 테이블 채우기
3단계: 위에서 생성된 테이블에 대한 뷰 생성
4단계: 대신 트리거 이전의 뷰 업데이트
5단계: 대체 트리거 생성
6단계: 트리거 대신 보기 업데이트

단계 1) 적절한 열을 사용하여 'emp' 및 'dept' 테이블 만들기

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));
/

코드 설명

코드 라인 1-7: 테이블 'emp' 생성.
코드 라인 8-12: 테이블 'dept' 생성.

산출

테이블이 생성되었습니다.

단계 2) 이제 테이블을 만들었으므로 이 테이블을 위 테이블에 대한 샘플 값과 뷰 생성으로 채웁니다.

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,'XXX5,15000,'AAA',30);
INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ;
INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10); 
COMMIT;
END;
/

코드 설명

코드 라인 13-19: 'dept' 테이블에 데이터를 삽입합니다.
코드 라인 20-26: 'emp' 테이블에 데이터를 삽입합니다.

산출

PL/SQL 프로시저 완료

단계 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;

코드 설명

코드 라인 27-32: 'guru99_emp_view' 뷰 생성.

코드 라인 33: guru99_emp_view를 쿼리하는 중입니다.

산출

뷰가 생성되었습니다.

EMPLOYEE_NAME	DEPT_NAME	주소
Zzz	HR	한국
YYY	매상	UK
트리플 엑스	금융	일본

단계 4) 트리거 대신 보기 전의 업데이트입니다.

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

코드 설명

코드 라인 34-38: 'XXX'의 위치를 'FRANCE'로 업데이트하세요. 예외가 발생했습니다. DML 문 복잡한 뷰에서는 허용되지 않습니다.

산출

ORA-01779: 키 보존되지 않은 테이블에 매핑하는 열을 수정할 수 없습니다

ORA-06512: 라인 2에

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

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;
/

코드 설명

코드 라인 39: ROW 수준의 'guru99_emp_view' 뷰에서 'UPDATE' 이벤트에 대한 INSTEAD OF 트리거를 생성합니다. 여기에는 기본 테이블 'dept'의 위치를 업데이트하는 업데이트 문이 포함되어 있습니다.
코드 라인 44: 업데이트 문은 ':NEW' 및 ':OLD'를 사용하여 업데이트 전후의 열 값을 찾습니다.

산출

트리거가 생성되었습니다.

단계 6) 트리거 대신 뷰 업데이트. 이제 "트리거 대신"이 이 복잡한 뷰의 업데이트 작업을 처리하기 때문에 오류가 발생하지 않습니다. 그리고 코드가 실행되면 직원 XXX의 위치가 "일본"에서 "프랑스"로 업데이트됩니다.

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

SELECT * FROM guru99_emp_view;

코드 설명 :

코드 라인 49-53: 'XXX'의 위치를 'FRANCE'로 업데이트했습니다. 'INSTEAD OF' 트리거가 뷰의 실제 업데이트 문을 중지하고 기본 테이블 업데이트를 수행했기 때문에 성공한 것입니다.
코드 라인 55: 업데이트된 기록을 확인하는 중입니다.

출력:

PL/SQL 프로시저가 성공적으로 완료되었습니다.

EMPLOYEE_NAME	DEPT_NAME	주소
Zzz	HR	한국
YYY	매상	UK
트리플 엑스	금융	프랑스

복합 트리거

복합 트리거는 단일 트리거 본체의 XNUMX개 타이밍 지점 각각에 대한 작업을 지정할 수 있는 트리거입니다. 지원하는 네 가지 타이밍 지점은 다음과 같습니다.

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

이는 서로 다른 타이밍에 대한 작업을 동일한 트리거로 결합하는 기능을 제공합니다.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 
FOR
[INSERT | UPDATE | DELET.......]
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;

구문 설명:

위 구문은 'COMPOUND' 트리거 생성을 보여줍니다.
선언 섹션은 트리거 본문의 모든 실행 블록에 공통됩니다.
이 4개의 타이밍 블록은 어떤 순서로든 있을 수 있습니다. 이 4개의 타이밍 블록을 모두 보유하는 것이 필수는 아닙니다. 필요한 타이밍에 대해서만 COMPOUND 트리거를 생성할 수 있습니다.

예제 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;

코드 설명 :

코드 라인 2-10: 복합 트리거 생성. 급여를 기본값 5000으로 채우기 위해 BEFORE ROW 수준의 타이밍에 대해 생성됩니다. 이렇게 하면 레코드를 테이블에 삽입하기 전에 급여가 기본값 '5000'으로 변경됩니다.
코드 라인 11-14: 'emp' 테이블에 레코드를 삽입합니다.
코드 라인 16: 삽입된 레코드를 확인하는 중입니다.

출력:

트리거가 생성되었습니다.

PL/SQL 프로시저가 성공적으로 완료되었습니다.

EMP_NAME	EMP_NO	봉급	MANAGER	DEPT_NO
CCC	1004	5000	AAA	30

트리거 활성화 및 비활성화

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

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

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

구문 설명:

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

제품 개요

이 장에서는 PL/SQL 트리거와 그 장점에 대해 배웠습니다. 또한 다양한 분류에 대해 알아보고 INSTEAD OF 트리거와 COMPOUND 트리거에 대해 논의했습니다.

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

트리거의 이점

트리거 유형 Oracle

트리거를 만드는 방법

관련 기사

:NEW 및 :OLD 절

트리거 대신

복합 트리거

제품 개요

뉴스레터 신청하기