[Oracle] 오라클 - PL/SQL (트리거 Trigger)

[Oracle]
오라클 - PL/SQL (트리거 Trigger)

---

✔️ 트리거 Trigger

: 미리 정해 놓은 특정 조건이 만족하거나 어떤 동작이 수행하면 자동으로 실행하도록 정의한 동작

: 예를 들어 DML(INSERT, UPDATE, DELETE) 문장이 실행되거나 DDL(CREATE, ALTER, DROP) 문장이 실행될 때 트리거가 실행될 수 있다.

: 트리거는 CREATE TRIGGER 시스템 권한이 있어야 생성 가능하다.

: 트리거 내에서는 자동 커밋 된다!!

 

GRANT CREATE TRIGGER TO sky;

관리자에서 권한 주기


-- sky 계정에서 시스템 권한 확인
SELECT * FROM user_sys_privs;

 

 

 

✔️ 문장 트리거 Trigger

: 하나의 DML 문에서 트리거는 한번⭐ 일어난다.

: 예를 들어 "DELETE FROM 테이블;" 문장으로 5개의 레코드가 삭제 되어도 트리거도 한번⭐ 실행 된다.

: 테이블에 레코드가 입력, 수정, 삭제 등의 로그 기록 (프린트 사용 내역 또는 입출금/조회 기록 등)

 

📓 트리거 목록 확인

📓 트리거 소스 확인

📓 의존성 확인

SELECT * FROM user_triggers;

SELECT * FROM user_source;

SELECT * FROM user_dependencies;

 

 

-- 예시 테이블/시퀀스 생성

CREATE TABLE test (
		num NUMBER  PRIMARY KEY,
		name VARCHAR2(50) NOT NULL,
		content VARCHAR2(4000) NOT NULL,
		reg_date DATE DEFAULT SYSDATE
	);

	CREATE TABLE info(
		num NUMBER PRIMARY KEY,
		memo VARCHAR2(1000) NOT NULL,
		reg_date DATE DEFAULT SYSDATE
	);


	CREATE SEQUENCE test_seq;
	CREATE SEQUENCE info_seq;

	SELECT * FROM tab;
	SELECT * FROM seq;

 

-- TEST 테이블에서 DML 작업이 일어나는 경우 DML 작업이 일어난 시간을 등록하는 문장 트리거

CREATE OR REPLACE TRIGGER testTrigger
        AFTER INSERT OR UPDATE OR DELETE ON test
        BEGIN
            IF INSERTING THEN
                INSERT INTO info(num, memo) VALUES (info_seq.NEXTVAL, '레코드 추가');
            ELSIF UPDATING THEN
                INSERT INTO info(num, memo) VALUES (info_seq.NEXTVAL, '레코드 변경');
            ELSIF DELETING THEN
                INSERT INTO info(num, memo) VALUES (info_seq.NEXTVAL, '레코드 삭제');
            END IF;
        END;
        /

💡 DELETE FROM test; 했을 때
모든 데이터가 삭제 되었지만 트리거는 한 번만 실행된 것을 볼 수 있다.

 

 

-- 지정된 시간에만 DML 작업을 할 수 있도록 문장 트리거 작성

CREATE OR REPLACE TRIGGER testTrigger2
	BEFORE INSERT OR UPDATE OR DELETE ON test
	BEGIN
		IF TO_CHAR(SYSDATE, 'D') IN (1,7) OR
			(TO_CHAR(SYSDATE, 'HH24') < 9 OR TO_CHAR(SYSDATE, 'HH24') > 18 ) THEN
		RAISE_APPLICATION_ERROR(-20001, '지금은 데이터를 수정할 수 있는 시간이 아닙니다.');
		END IF;
	END;
	/

 

 

 

✔️ 행 트리거 Trigger

 : DML 문에서 조건만족하는 모든 행에 대하여 트리거가 일어난다.

: 예를 들어 "DELETE FROM 테이블;" 문장으로 5개의 레코드가 삭제된 경우 트리거는 5번 실행 된다.

 

📓 OLD 와 NEW 레코드
          - 행 트리거에서만 사용 가능
          - :OLD 
            UPDATE 에서는 수정전 레코드, DELETE 에서는 삭제할 레코드
          - :NEW
            INSERT에서는 추가할 레코드, UPDATE 에서는 수정할 레코드

 

 

-- INSERT 에 대한 행트리거 : score1 테이블에 데이터가 추가되면 평점을 계산하여 score2 테이블에 데이터 추가

CREATE OR REPLACE TRIGGER scoreInsertTrigger 
	AFTER INSERT ON score1
		-- 행 트리거 / 이게 없으면 문장 트리거이다.
	FOR EACH ROW 	
	DECLARE
		-- 필요한 변수 선언
	BEGIN
		-- : NEW -> INSERT 하는 레코드를 가지고 있는 레코드 (행 트리거만 쓸 수 있다.)
		-- 트리거 안에서의 DML 은 COMMIT 하지 않는다. 자동 커밋

		INSERT INTO score2(hak, kor, eng, mat) VALUES
				(:NEW.hak, fnGrade(:NEW.kor), fnGrade(:NEW.eng),  fnGrade(:NEW.mat));
	END;
	/
	
	INSERT INTO score1(hak, name, kor, eng, mat) VALUES ('111', 'aaa', 80, 90, 95);
	INSERT INTO score1(hak, name, kor, eng, mat) VALUES ('222', 'bbb', 70, 95, 85);
	INSERT INTO score1(hak, name, kor, eng, mat) VALUES ('333', 'ccc', 100, 75, 80);
	COMMIT;

💡 FOR EACH ROW 
이 문장이 없으면 행 트리거가 아닌 문장 트리거다.

NEW -> INSERT 하는 레코드를 가지고 있는 레코드 (행 트리거만 쓸 수 있다.)

 

 

-- UPDATE 에 대한 행트리거 : score1 테이블에 데이터가 수정되면 평점을 계산하여 score2 테이블에 데이터 수정

CREATE OR REPLACE TRIGGER scoreUpdateTrigger 
	AFTER UPDATE ON score1
		-- 행 트리거 / 이게 없으면 문장 트리거이다.
	FOR EACH ROW 	
	DECLARE
	BEGIN
		-- : NEW -> 새로 UPDATE 할 레코드
		-- : OLD -> UPDATE 이전 레코드

		UPDATE score2 SET kor = fnGrade(:NEW.kor), eng = fnGrade(:NEW.eng), 
					 mat = fnGrade(:NEW.mat)
		WHERE hak = :OLD.hak;

	END;
	/


	UPDATE score1 SET kor = 85, eng = 95, mat = 85 WHERE hak = '111';
	COMMIT;

💡 -- : OLD -> UPDATE 이전 레코드

 

 

🔒 문제

-- 부모의 기본키가 변경되면 자식의 참조키도 변경되도록 UPDATE 트리거 수정

-- score1 과 score2 는 참조관계이므로 score2 에 존재하는 레코드의 hak 를 score1 에서 수정하면 에러 발생

CREATE OR REPLACE TRIGGER scoreUpdateTrigger 
AFTER UPDATE ON score1
		-- 행 트리거 / 이게 없으면 문장 트리거이다.
	FOR EACH ROW 	
	DECLARE
	BEGIN
    UPDATE score2 SET hak = :NEW.hak,
                    kor = fnGrade(:NEW.kor), eng = fnGrade(:NEW.eng), 
                    mat = fnGrade(:NEW.mat)
		WHERE hak = :OLD.hak;

	END;
	/

 

 

-- DELETE 에 대한 행트리거 : score1 테이블에 데이터가 삭제되면 score2 테이블에 데이터 삭제

CREATE OR REPLACE TRIGGER scoreDeleteTrigger 
	BEFORE DELETE ON score1
	FOR EACH ROW 	
	DECLARE
	BEGIN
		-- :OLD -> DELETE 할 레코드		

		DELETE FROM score2
		WHERE hak = :OLD.hak;

	END;
	/

	DELETE FROM score1 WHERE hak = '100';

💡 score1 테이블을 삭제하기 전, BEFORE. score2 테이블을 삭제하는 트리거다.