SQL 트리거(BEFORE, AFTRE) 및 백업 & 복원

⏰ 2024.11.05 (D+19)

1. 트리거(Trigger)란?

🔖 정의 ]

- 자동으로 실행되는 프로시저의 한 종류. 직접(exec) 실행 불가
- 하나의 테이블에 최대 3개까지 트리거 적용 가능
- 단, 트리거 많을 수록 성능 저하 초래 가능성 있다.
- 트리거 몸체(PL/SQL블락)안에는 COMMIT;ROLLBACK불가
:NEW(변경후), :OLD(변경전) 임시 테이블은 행단위 트리거에서만 사용 가능

❗ 트리거 몸체에서 :NEW 및 :OLD의 의미 ]

타켓 테이블에
INSERT 실행시 :OLD는 NULL이고 :NEW은 새로운 입력값이다
UPDATE 실행시 :OLD는 업데이트 전의 값이고 :NEW은 새로운 변경 값이다
DELETE 실행시 :OLD는 삭제 전의 값이고 :NEW은 NULL이다

즉, :OLD는 DELETE 혹은 UPDATE 하기 전의 데이타(변경전)이고
:NEW는 INSERT 혹은 UPDATE하려는 데이타(변경후)이다

트리거(Trigger) 구문 ⭐

트리거 생성

CREATE TRIGGER 트리거명
타이밍 [BEFORE|AFTER] 이벤트 [INSERT [OR] | UPDATE [OR] DELETE]
ON 트리거를 걸 테이블명
[FOR EACH ROW] –생략시 문장단위 트리거
[WHEN 트리거 조건]
DECALRE
변수 선언
BEGIN
END;
/

트리거 삭제
DROP TRIGGER 트리거명

특정 트리거 활성화/비활성화
ALTER TRIGGER 트리거명 [ENABLE/DISABLE]

테이블에 건 모든 트리거 활성화 비활성화
ALTER TABLE 테이블명 ENABLE/DISABLE ALL TRIGGERS

※ INSERTING /DELETING/UPDATING 키워드 PL/SQL블락 안에서 사용하는 상수
예) INSERT이벤트가 발생하면 INSERTING 이 TRUE

※ BEFORE트리거 걸때 주로 사용하는 함수
RAISE_APPLICATION_ERROR(에러번호,‘에러메시지’);
에러번호는 -20000 ~-20999사이의 임의의 숫자 지정

트리러를 걸 테이블 생성

CREATE TABLE TARGET_TBL(
    NO NUMBER PRIMARY KEY,
    MSG NVARCHAR2(10)
);

❓위 테이블에 I:입력/D:삭제/U:수정(이벤트)가 발생하면 아래 테이블에 자동으로 어떤 작업(이벤트)가 발생했는지 입력하자

CREATE TABLE AUTO_TBL(
    EVENT NVARCHAR2(10),
    POSTDATE DATE DEFAULT SYSDATE
);

AFTRE 트리거 활성화 및 생성 작업

CREATE TRIGGER AFTER_TRG_TARGET_TBL
AFTER INSERT OR DELETE OR UPDATE
ON TARGET_TBL
FOR EACH ROW
DECLARE
BEGIN
    IF INSERTING THEN
        INSERT INTO AUTO_TBL VALUES ('INSERT', DEFAULT);
    ELSIF DELETING THEN
        INSERT INTO AUTO_TBL VALUES ('DELETE', DEFAULT);
    ELSIF UPDATING THEN
        INSERT INTO AUTO_TBL VALUES ('UPDATE', DEFAULT);
    END IF;

END;
/

SELECT * FROM TARGET_TBL;
SELECT * FROM AUTO_TBL;

INSERT INTO TARGET_TBL VALUES (1, 'FIRST');
INSERT INTO TARGET_TBL VALUES (2, 'SECOND');
UPDATE TARGET_TBL SET MSG='두번째' WHERE NO=2;

BEFORE 트리거 활성화 및 생성 작업

CREATE TRIGGER BEFORE_TRG_TARGET_TBL
AFTER INSERT OR DELETE OR UPDATE
ON TARGET_TBL
FOR EACH ROW
DECLARE
BEGIN
    IF TO_CHAR(SYSDATE,'DY')='화' OR TRIM(TO_CHAR(SYSDATE,'HH24'))=18 THEN
        RAISE_APPLICATION_ERROR(-20001,'수요일 혹은 18시 이후에는 입력불가');
    END IF;

END;
/

상품, 입고, 판매 테이블을 이용한 응용 예시 📖]

**➕ 상품테이블 생성**
CREATE TABLE PRODUCT(
    P_CODE CHAR(4) PRIMARY KEY,
    P_NAME NVARCHAR2(10) NOT NULL,
    P_PRICE NUMBER,
    P_QTY NUMBER DEFAULT 0);--재고수량

**➕ 입고테이블 생성**
CREATE TABLE INP(
    I_NO NUMBER PRIMARY KEY,
    P_CODE CHAR(4) REFERENCES PRODUCT(P_CODE),
    I_DATE DATE DEFAULT SYSDATE,
    I_QTY NUMBER,
    I_PRICE NUMBER
);

**➕ 판매테이블 생성**
CREATE TABLE SALES(
    S_NO NUMBER PRIMARY KEY,
    P_CODE CHAR(4) REFERENCES PRODUCT(P_CODE),
    S_DATE DATE DEFAULT SYSDATE,
    S_QTY NUMBER,
    S_PRICE NUMBER
);

✔️ PRODUCT테이블에 데이터 생성
INSERT INTO PRODUCT(P_CODE,P_NAME,P_PRICE) VALUES('B001','자바',2500);
INSERT INTO PRODUCT(P_CODE,P_NAME,P_PRICE) VALUES('B002','스프링',3000);

✔️ PRODUCT테이블에 생성된 데이터 확인
SELECT * FROM PRODUCT;

UPDATE PRODUCT SET P_QTY=10 WHERE P_CODE='B002';
SELECT FROM INP;
SELECT FROM SALES;

❗INSERT

CREATE TRIGGER AFTER_TRG_INP_INSERT
AFTER INSERT
ON INP
FOR EACH ROW
DECLARE
BEGIN
    UPDATE PRODUCT SET P_QTY = P_QTY+ :NEW.I_QTY
    WHERE P_CODE = :NEW.P_CODE;
END;
/

✔️ INP테이블에 데이터 생성
INSERT INTO INP VALUES(1,'BOO2',SYSDATE,5,2500);
INSERT INTO INP VALUES(2,'BOO2',SYSDATE,10,2500);

❗UPDATE

UPDATE INP SET I_QTY=8 WHERE I_NO='2';

**⭐ AFTRE 트리거 활성화 및 생성 작업**

```java
CREATE TRIGGER AFTER_TRG_INP_UPDATE
AFTER UPDATE
ON INP
FOR EACH ROW
DECLARE
BEGIN
    UPDATE PRODUCT SET P_QTY = P_QTY - :OLD.I_QTY + :NEW.I_OTY
    WHERE P_CODE = :NEW.P_CODE;
END;
/

⭐ BEFORE 트리거 활성화 및 생성 작업

CREATE TRIGGER BEFORE_TRG_SALES_INSERT
AFTER INSERT
ON SALES
FOR EACH ROW
DECLARE
    QTY NUMBER;--상품의 재고수량 저장
BEGIN
   --재고 수량 파악
   SELECT P_qty INTO qty FROM PRODUCT
   WHERE P_CODE = :NEW.P_CODE;
   --재고수량과 판매 수량 비교
   IF QTY < :NEW.S_QTY THEN
        RAISE_APPLICATION_ERROR(-20002,'재고가 부족해요. 수량이' || QTY || '밖에 남지 않았어요');
    ELSE --제고수량이 충분
        UPDATE PRODUCT SET p_QTY = P_QTY - :NEW.S_QTY
        WHERE P_CODE = :NEW.P_CODE;
   END IF;
  
END;
/

SELECT * FROM SALES;
INSERT INTO SALES VALUES(1,'B002',SYSDATE,1,1500);

2. 백업 및 복원

⭐ 백업 및 복원 중요사항]
백업 및 복원 실행 파일 위치 : [오라클설치된 디렉토리]\product\10.2.0\db_1\BIN
백업:EXP.EXE
복원:IMP.EXE

📁 백업(Export)

데이타와 구조를 바이너리 파일로 저장
dos>exp userid=아이디/비밀번호@전역데이타베이스명 file=저장경로

system계정으로 전체 백업
dos>exp userid=system/비밀번호@전역데이타베이스명 full=y file=c:\dump.dmp

system 계정으로 scott 계정에 있는 DB백업
dos>exp userid=system/비밀번호@전역데이타베이스명 owner=scott
file=c:\dump.dmp

scott계정으로 자신의 모든 데이타 백업
dos>exp userid=scott/비밀번호@전역데이타베이스명 file=c:\dump.dmp

scott계정으로 emp테이블만 백업
dos>exp userid=scott/비밀번호@전역데이타베이스명 file=c:\dump.dmp
tables=emp

※ 여러 개 테이블을 동시에 받으려면 tables=(테이블1,테이블2,...)
※ 백업 파일의 확장자는 보통 .dmp 혹은 .dat .bak으로 한다.

📁 복원(Import)

imp 아이디/비밀번호@전역데이타베이스명 file=백업경로

system계정으로 전체 복원
dos>imp system/비밀번호@전역데이타베이스명 file=c:\dump.dmp

system 계정으로 scott 계정에 있는 DB복원
dos>imp system/비밀번호@전역데이타베이스명 fromuser=scott touser=scott
file=c:\dump.dmp

scott계정으로 자신의 모든 데이타 복원
dos>imp scott/비밀번호@전역데이타베이스명 file=c:\dump.dmp

복원하고자하는 DB에 같은 이름의 Object가 있을때,오류를 무시하고 건너 띄고 싶을때 ignore 옵션사용
dos>imp 아이디/비밀번호@전역데이타베이스명 file=c:\dump.dmp ignore=y

system계정으로 들어가 scott에서 Export한 데이터를 scott2에게 Import
dos>imp system/비밀번호@전역데이타베이스명 fromuser=scott touser=scott2
file=c:\dump.dmp