이거 코드블럭 어떻게 삭제하니 하 나참 티스토리 어려워
■■ TRIGGER(트리거) ■■■■■■■■ -- DBA 가 많이 쓰지 개발자가 많이 쓰지는 않는다. 일단 사전적인 의미 촉발제 방아쇠
-- 쥐덫 부비트랩
-- 사전적 의미 : 방아쇠, 촉발시키다, 야기하다, 유발하다
-- 1. TRIGGER(트리거)란 , DML 작업, 즉, INSERT, UPDATE, DELETE 와 같은 작업이
-- 일어날 때, 자동적으로 실행되는(유발되는, 촉발되는) 객체로
-- 이와 같은 특징을 강조하여(부각시켜) DML TRIGGER라고 부르기도 한다.
-- TRIGGER 는데이터 무결정 뿐 아니라 다음과 같은 작업에도 널리 사용된다.
-- ▶ 자동으로 파생된 열 값 생성 A테이블에인서트또는제거하면 B에다가 A업데이트또는제거했대요 적어두는것 트리거걸어두면자동으로기록해둔다 부비트랩 누가 건드리면 뭔가 작성이된다 철컥,,,
-- ▶ 잘못된 트랜젝션 방지 입고상품출고테이블 프로시저호출했음 프로시저호출이아니라 출고인서트구문을 ㅗ하면 저절로 상품.재고가 변경되는 것이다. 이게 잘 못된 트랜젝션 방지에용
-- ▶ 복잡한 보안 권한 강제 수행 특정한테이블만들어놓고 몇시부터몇시까지만접근가능해 서울경기인천만접속가능해 이런것들 예를 들어 회사에서 주식사이트 특정시간에 막는것
-- ▶ 분산 데이터베이스 노드 상에서 참조 무결성 강제 수행 부산테이블 서울테이블 10번부서 폐쇄된부서라서 입 력이 불가하다이런것들?
-- ▶ 복잡한 업무 규칙 강제 적용 복잡한 보안권한강제수행이랑 같은말이다
-- ▶ 투명한 이벤트 로깅 제공 분명히 이 테이블에는 아무것도 인서트되어있지 않은데 여기에 어떤 인서트 업데이트 딜리트 이런것들을 다 기록해둔것 이런 각각의 이벤트들의 로그기록을 제3테이블에 남기는것
-- ▶ 복잡한 감사 제공 예를들어 회계장부에서 어떻게 집행했다 이런것들도 트리거에 의해 내역들이 남도록 처리할 수 있다
-- ▶ 동기 테이블 복제 유지관리 테이블 두 개 만들었다 이 테이블현역 저 테이블은 똑같은데 여기에 인서트하면 저기에도 인서트되도록 하는것 업데이트도 마찬가지 이런것들 처리가 가능하다
-- ▶ 테이블 액세스 통계 수진 누가 ,언제 , 무엇을 했는지 그런것들을 기록에 남도록 해주는 것이 트리거다
--2. TRIGGER 내에서는 COMMIT, ROLLBACK 문을 사용할 수 없다.
그 이유 UPDATE INSERT DELETE구문 자체에넣는것이므로 ?
-- 3. 특징 및 종류 앞에서 터지게 할것인가 뒤에서 할 것인가
-- 예를 들어 주식사이트 근무시간에 막아놓는 것 이거는 사전에 처리해야함
-- 사전에 처리할 것인지 아니면 사후에 처리해야하는지
-- STATEMENT는 한 줄만 한번 , ROW는 행 각각의 행을 모두 확인해야 하느냐 아니냐 그 차이이다
-- 레코드들마다 체크해야하나 아니면 문장하나만/ 처리하면되나
-- 경우의 수는 총 4가지가 되겠죵
-- ◈ BEFORE STATEMENT TRIGGER : SQL 구문이 실행되기 전에 그 문장에 대해 한 번 실행
-- ◈ BEFORE ROW TRIGGER : SQL 구문이 실행되기 전에(DML작업을 수행하기 전에) 각 행(ROW) 에 대해 한 번 씩 실행
-- ◈ AFTER STATEMENT TRIGGER : SQL 구문이 실행된 후 그 문장에 대해 한 번 실행
-- ◈ AFTER ROW TRIGGER : SQL 구문이 실행된 후에(DML작업을 수행한 후에) 각 행(ROW)에 대해 한 번 씩 실행
-- 4. 형식 및 구조
/*
CREATE [OR REPLACE] TRIGGER 트리거명
[BEFORE] | [AFTER]
이벤트1 [OR 이벤트2 [OR 이벤트3]] ON 테이블명
[FOR EACH ROW [WHEN TRIGGER 조건]]
[DECLARE]
-- 선언 구문;
BEGIN
-- 실행 구문;
END;
*/
우선 보자
첫 번째로 애프터 스테이트먼트 트리거
SQL 실행 후 문장에 한 번 실행된다...는 건데
-- ■■■ AFTER STATEMENT TRIGGER 상황 실습 ■■■
-- ※ DML 작업에 대한 이벤트 기록
-- ● TRIGGER (트리거) 생성(TRG_EVENTOG)
CREATE OR REPLACE TRIGGER TRG_EVENTLOG
AFTER
INSERT OR UPDATE OR DELETE ON TBL_TEST1
DECLARE
BEGIN
-- 이벤트 종류 구문(조건문을 통한 분기_
-- 구분에 대한 키워드 CHECK!!!!!
IF (INSERTING)
THEN INSERT INTO TBL_EVENTLOG(MEMO)
VALUES ('INSERT 쿼리문이 수행되었습니다.');
ELSIF (UPDATING)
THEN INSERT INTO TBL_EVENTLOG(MEMO)
VALUES ('UPDATE 쿼리문이 수행되었습니다.');
ELSIF (DELETING)
THEN INSERT INTO TBL_EVENTLOG(MEMO)
VALUES ('DELETE 쿼리문이 수행되었습니다. ');
END IF;
-- COMMIT; 트리거내에서는 커밋사용금지
END;
--==>>Trigger TRG_EVENTLOG이(가) 컴파일되었습니다.CREATE OR REPLACE TRIGGER 트리거명 써주고
애프터니까 AFTER 써주고
INSERT OR UPDATE OR DELETE ON TBL_TEST1
--> 테스트1 테이블에 인서트 업데이트 딜리트가 일어나면
TBL_EVENTLOG 라는 테이블의 MEMO 컬럼에 각각 INSERT, UPDATE, DELETE 쿼리문이 수행되었다는 걸
집어넣도록 한다.
우선 애프터이다. 구문이 실행된 후에 이벤트로그 테이블에 데이터를 집어넣는 것이니까
그런데 왜 스테이트먼트인가 ? 그 차이는 나중에 알아보도록 하자
INSERT INTO TBL_TEST1(ID,NAME,TEL)
VALUES(1,'김가가','010-1111-1111');
INSERT INTO TBL_TEST1(ID,NAME,TEL)
VALUES(2,'김서서','010-1221-2211');
INSERT INTO TBL_TEST1(ID,NAME,TEL)
VALUES(3,'이유유','010-3331-1331');
UPDATE TBL_TEST1
SET NAME = '김나나'
WHERE ID='1';
UPDATE TBL_TEST1
SET NAME = '김다다'
WHERE ID='1';
UPDATE TBL_TEST1
SET NAME = '김동현'
WHERE ID=2;
DELETE
FROM TBL_TEST1
WHERE ID = 3;
DELETE
FROM TBL_TEST1
WHERE ID = 2;
DELETE
FROM TBL_TEST1
WHERE ID = 1;
SELECT *
FROM TBL_TEST1;
--==>> 조회결과없음
SELECT *
FROM TBL_EVENTLOG;
/*
MEMO ILJA
INSERT 쿼리문이 수행되었습니다. 2021-04-12 15:23:39
INSERT 쿼리문이 수행되었습니다. 2021-04-12 15:23:39
INSERT 쿼리문이 수행되었습니다. 2021-04-12 15:23:39
UPDATE 쿼리문이 수행되었습니다. 2021-04-12 15:23:41
UPDATE 쿼리문이 수행되었습니다. 2021-04-12 15:23:54
UPDATE 쿼리문이 수행되었습니다. 2021-04-12 15:24:42
DELETE 쿼리문이 수행되었습니다. 2021-04-12 15:25:10
DELETE 쿼리문이 수행되었습니다. 2021-04-12 15:25:20
DELETE 쿼리문이 수행되었습니다. 2021-04-12 15:25:47
*/
ㅇ이렇듯 트리거를 CREATE 한 결과 TBL_TEST1에 어떤 데이터들을 넣고 수정하고 삭제하고 그것들이 모두 삭제되고,,, 뭐 이렇게 되더라도 EVENT_LOG 테이블에는 INSERT, DELETE, UPDATE 한 모든 기록들이 저절로 담긴다. ILJA는 DEFAULT 값으로 SYSDATE 를 줬기 때문에 저절로 시각이 입력된다.
그 다음에 실습해 볼 것은 애프터가 아니라 비포다
그래서 SQL 구문 실행하기 이전에 브레이크를 밟는다.
아까는 구문 실행하고 나서 그것을 기록하는 데 쓰였고
이건 구문이 실행되기 이전에 되냐 안되냐를 판단하는데 쓰인다
아래서 해볼것은 사용자가 INSERT, UPDATE , DELETE 하려고 할 때 구문 실행 이전에
시간이 8시 이전이거나 6시 이후인지 확인하고 그렇다면 못하게 하는 트리거이다.
----------------------------------------------------------------------
-- -- ■■■ BEFORE STATEMENT TRIGGER 상황 실습 ■■■
-- ※ DML 작업 수행 전에 작업 가능여부 확인(보안 정책 적용 / 업무 규칙 적용 등에 쓰이는 것이다)
-- ● TRIGGER 작성 ( TRG_TEST1_DML )
CREATE OR REPLACE TRIGGER TRG_TEST1_DML
BEFORE
INSERT OR UPDATE OR DELETE ON TBL_TEST1
BEGIN
IF (시간이 오전 8 시 이전이거나... 오후 6 시 이후라면....)
THEN 작업을 하지 못하도록 처리하겠다.= 예외발생시키겠다.
END IF;
END;
-----------------------------
CREATE OR REPLACE TRIGGER TRG_TEST1_DML
BEFORE
INSERT OR UPDATE OR DELETE ON TBL_TEST1
BEGIN
IF (TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) < 8
OR TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) >=18 )
THEN RAISE_APPLICATION_ERROR(-20003, '작업은 8:00 ~ 18:00 까지만 가능합니다.');
END IF;
END;
--==>>Trigger TRG_TEST1_DML이(가) 컴파일되었습니다.
------------------------------------------------------------------------------그래서 7시 59분 이전까지는 불가하고 오후 6시부터는 또 작업이 불가하도록 IF 문으로 처리를 해주었다.
TO_CHAR() 로 SYSDATE 에서 시간을 뽑아오고 그것을 숫자타입으로 변환하고 크기를 비교한다.
만약에 시간이 작업이 불가한 시간대라면 THEN RAISE_APPLICATION_ERROR() 함수로 에러코드와 에러구문을 넣어준다.
INSERT INTO TBL_TEST1(ID,NAME,TEL)
VALUES(4,'박민민','010-0009-9999');
--==>>1 행 이(가) 삽입되었습니다.
UPDATE TBL_TEST1
SET TEL = '010-2020-3232'
WHERE ID=4;
--==>>1 행 이(가) 업데이트되었습니다.
DELETE
FROM TBL_TEST1
WHERE ID=4;
--==>>1 행 이(가) 삭제되었습니다.
-- 만약 오라클 서버 시간이 18시 이후라면 에러가 발생하게 된다.
/*오류 보고 -
ORA-20003: 작업은 8:00 ~ 18:00 까지만 가능합니다.*/제한시간 외에는 DML 구문이 잘 실행되지만 제한시간에는 오류가 뜬다. 오라클이 설치된 환경의 시간 기준이다.
이번에는 비포 로우이다.
여기서는 :OLD :NEW 콜론올드 콜론뉴 라는 새로운 개념이 나온다.
아래는 TBL_TEST2 라는 테이블에서 삭제를 하려고 하면 그 이전에(BEFORE) TBL_TEST3 의 레코드들을 삭제하는 것이다.
갑자기 TEST3 테이블을 삭제를 하느냐 면
지금 3은 2를 코드를 FOREIGN KEY 로 하는 자식테이블이기 때문이다
그래서 3을 삭제하지 않으면 2도 삭제를 할 수 없게 된다(참조중인 자식 테이블이 있어서 삭제 못한다는 에러뜸)
그래서 DELETE 구문 이전에 테스트3에 있는 데이터들을 삭제해줘야 하기 때문에 비포이고
그런데 왜 스테이트먼트가 아니라 로우지..
이거 왜 아직도 모르겠지
-- ■■■ BEFORE ROW TRIGGER 상황 실습 ■■■
-- ※ 참조 관계가 설정된 데이터(자식) 삭제를 먼저 수행하는 모델
-- ● TRIGGER (트리거) 작성(TRG_TEST2_DELETE)
CREATE OR REPLACE TRIGGER TRG_TEST2_DELETE
BEFORE
DELETE ON TBL_TEST2
FOR EACH ROW
DECLARE
BEGIN
DELETE
FROM TBL_TEST3
WHERE CODE = :OLD.CODE;
END;
-- ● :OLD 콜론올드
-- 참조 전 열의 값
-- (INSERT : 입력하기 이전 자료, DELETE : 삭제하기 이전 자료 즉,삭제할 자료)
--사실,,,오라클에는 업데이트라는게없다,,,,,그러면 지금까지 써운거는 뭐다 얘는 UPDATE DELETE + INSERT 이다, 엄밀히 따지면 바꾸는게아니라 삭제하고 새로운값넣는것이다.
-- ※ UPDATE ▶ DELETE 그리고 INSERT 가 결합된 형태 이 과정에서 UPDATE 하기 이전의 자료는 :OLD
-- 이 과정에서 UPDATE 한 후의 자료는 :NEW
--==>>Trigger TRG_TEST2_DELETE이(가) 컴파일되었습니다.
SELECT C.SID, P.CODE, P.NAME, C.SU
FROM TBL_TEST2 P JOIN TBL_TEST3 C
ON P.CODE = C.CODE;
/*
SID CODE NAME SU
1 1 냉장고 20
2 1 냉장고 20
3 1 냉장고 40
4 2 세탁기 20
5 2 세탁기 30
6 2 냉장고 40
7 1 냉장고 20
8 2 세탁기 20
*/
DELETE
FROM TBL_TEST2
WHERE CODE=2;
/*
오류 보고 -
ORA-02292: integrity constraint (SCOTT.TEST3_CODE_FK) violated - child record found*/
DELETE
FROM TBL_TEST2
WHERE CODE=3;
--==>>1 행 이(가) 삭제되었습니다.
COMMIT;
ㅌ 트리거 생성 이전에는 테스트2와 테스트3 테이블을 CODE 를 기준으로 조인한 결과 테이블을 보면
음,, 저렇다!ㅋㅋ
냉장고와 세탁기들이 있다. 코드는 각각 1,2이다.
그런데 테스트2에서 코드 2 , 즉 세탁기들을 다 없앤다고 하면 에러가 발생한다. CONSTRAINT를 어겼다. CHILD RECORD 가 있다.
그런데 코드 3번 건조기가 TEST1에는 있고 TEST2에는 없다. 그래서 CODE 가 3인 레코드를 삭제하겠다고 하면 삭제가 된다! 왜냐 참조하고 있는 게 없으니까
-- TRIGGER 생성 이후 다시 시도
DELETE
FROM TBL_TEST2
WHERE CODE=2;
--==>>1 행 이(가) 삭제되었습니다.
SELECT *
FROM TBL_TEST2;
/*
CODE NAME
1 냉장고
*/
SELECT *
FROM TBL_TEST3;
/*
SID CODE SU
1 1 20
2 1 20
3 1 40
7 1 20
*/그런데 위처럼 비포 로우 트리거를 만든 후에는 DELETE 이전에 행들을 확인한 후에 삭제가 이루어지도록 한다.
여기서는 지금 코드 2번을 위와 같이 삭제를 하려고 하는데 트리거 이전에는 자식 있다고 삭제가 안됐는데
트리거를 통해서 삭제하려는 코드가 포함된 레코드들을 모두 테스트3에서 제거한 후에 테스트2에서 지우도록 해준다.
결국 테스트3에 남은 것은 1번 냉장고뿐,,,
아 졸리다 벌서 열두시가 넘었으어
이번에는 애프터 로우 이다
만약 입고 테이블에 INSERT 를 하려고 한다면 상품테이블에 입고된 수량만큼 수량을 더해주는 트리거이다.
:NEW.입고수량
은 입고테이블에 인서트 하려는 레코드의 입고수량이란 뜻이다.
-- ■■■ AFTER ROW TRIGGER 상황 실습 ■■■
-- ※ 참조 테이블 관련 트랜잭션 처리
-- TBL_상품, TBL_입고, TBL_출고
-- ● TBL_입고 테이블의 데이터 입력 시(입고 이벤트 발생 시)
-- TBL_상품 테이블의 재고 수량 변동 트리거 작성
CREATE OR REPLACE TRIGGER TRG_IBGO
AFTER
INSERT ON TBL_입고
FOR EACH ROW
BEGIN
IF(INSERTING)
THEN UPDATE TBL_상품
SET 재고수량 = 재고수량 + 새로입고되는수량
WHERE 상품코드 = 새로입고되는상품코드;
END IF;
END;
----------------------------------------
CREATE OR REPLACE TRIGGER TRG_IBGO
AFTER
INSERT ON TBL_입고
FOR EACH ROW
BEGIN
IF(INSERTING)
THEN UPDATE TBL_상품
SET 재고수량 = 재고수량 + :NEW.입고수량
WHERE 상품코드 = :NEW.상품코드;
END IF;
END;
--===>>Trigger TRG_IBGO이(가) 컴파일되었습니다.
-----------------------------------------
아래는 위의 코드에 좀 더 기능을 추가한 것이다.
위에서는 인서트만 했다면 아래는 업데이트와 삭제가 추가됐다.
-- TBL_상품, 입고, 출고의 관계에서
-- 입고수량, 재고수량의 트랜잭션 처리가 이루어질 수 있도록
-- TRG_IBGO 트리거를 수정한다.
CREATE OR REPLACE TRIGGER TRG_IBGO
AFTER
INSERT OR UPDATE OR DELETE ON TBL_입고
FOR EACH ROW
BEGIN
IF(INSERTING)
THEN UPDATE TBL_상품
SET 재고수량 = 재고수량 + :NEW.입고수량
WHERE 상품코드 = :NEW.상품코드;
ELSIF (UPDATING)
THEN UPDATE TBL_상품
SET 재고수량 = 재고수량 + (:NEW.입고수량-:OLD.입고수량)
WHERE 상품코드 = :NEW.상품코드;
ELSIF (DELETING)
THEN UPDATE TBL_상품
SET 재고수량 = 재고수량 - :OLD.입고수량
WHERE 상품코드 = :OLD.상품코드;
END IF;
END;
--==>>Trigger TRG_IBGO이(가) 컴파일되었습니다.
-- TBL_상품, 입고, 출고의 관계에서
-- 출고수량, 재고수량의 트랜잭션 처리가 이루어질 수 있도록
-- TRG_CHULGO 트리거를 작성한다.
CREATE OR REPLACE TRIGGER TRG_CHULGO
AFTER
INSERT OR UPDATE OR DELETE ON TBL_출고
FOR EACH ROW
BEGIN
IF (INSERTING)
THEN UPDATE TBL_상품
SET 재고수량 = 재고수량 - :NEW.출고수량
WHERE 상품코드 = :NEW.상품코드;
ELSIF (UPDATING)
THEN UPDATE TBL_상품
SET 재고수량 = 재고수량+:OLD.출고수량-:NEW.출고수량
WHERE 상품코드 = :NEW.상품코드;
ELSIF (DELETING)
THEN UPDATE TBL_상품
SET 재고수량 = 재고수량 + :OLD.출고수량
WHERE 상품코드 = :OLD.상품코드;
END IF;
END;
--==>>Trigger TRG_CHULGO이(가) 컴파일되었습니다.SET 재고수량 = 재고수량+:OLD.출고수량-:NEW.출고수량
이게 뜻하는 바는 테이블의 재고수량에
업데이트 하기 이전의 출고수량을 더해준다음에 다시
새롭게 업데이트될 출고수량을 빼주라는 뜻이다.
하... 정리다했다 자자
'📚Study Note > ORACLE' 카테고리의 다른 글
| [ ORACLE ] 오라클 세미 중입니다 (0) | 2021.04.15 |
|---|---|
| [ ORACLE ] PACKAGE(패키지) (0) | 2021.04.15 |
| [ ORACLE ] CURSOR 커서란,,,마츼 깜짝상자 같은 것💥🎁 (0) | 2021.04.14 |
| [ ORACLE ] PROCEDURE 내에서 예외처리 (0) | 2021.04.12 |
| [ ORACLE ] DML │ DDL │ DCL │ TCL │ 정적/동적PLSQL (0) | 2021.04.09 |
