[오라클 SQL] 트리거 (Trigger)

트리거

• 특정 조건이 만족하거나 어떤 동작이 수행되면 자동으로 실행
• 예를 들어, DML문이나 DDL문이 실행횔 때 트리거 실행 
• CREATE TRIGGER 시스템 권한 있어야 생성 가능

 

프로시저

• 필요할 때 마다 호출하여 실행 (수동)
• 자주 실행해야 하는 업무 흐름(SQL)을 미리 작성하여 DB 내에 저장

 

---

[ 트리거 ]

트리거 생성 권한 부여

• 관리자 계정에서 실행

-- 관리자(sys) 계정: sky계정에게 트리거를 만들 수 있는 권한 부여
GRANT CREATE TRIGGER TO sky;

 

 

-- 트리거 목록 확인
SELECT * FROM user_triggers;
        
-- 트리거 소스 확인
SELECT * FROM user_source;
        
-- 의존성 확인
SELECT * FROM user_dependencies;

 

 

문장 트리거

• 하나의 DML문에서 트리거는 한 번 일어난다
  • DELETE FROM table; 문장으로 5개의 레코드가 삭제되어도 트리거는 한 번 실행.
• 테이블에 레코드의 입력, 수정, 삭제 등의 로그 기록
• 트리거 내에서는 자동 커밋된다
  • COMMIT; 기술하면 오류 발생

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

 

트리거 생성 : 지정된 시간에만 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
    	(TO_CHAR(SYSDATE, 'HH24') >=14 AND TO_CHAR(SYSDATE, 'HH24') <=15) THEN
        RAISE_APPLICATION_ERROR(-20001, '지금은 데이터를 수정할 수 있는 시간이 아닙니다.');
     END IF;
END;
/

INSERT INTO test(num, name, content) VALUES (test_seq.NEXTVAL, 'a', 'aaa');
-- 지정된 시간이 아니므로 에러 발생

 

행 트리거

• DML문에서 조건 만족하는 모든 행에 대하여 트리거 발생
  • DELETE FROM table; 문장으로 5개의 레코드가 삭제된 경우, 트리거 5번 실행
• OLD와 NEW 레코드
  
  • 행 트리거에서만 사용 가능
  • OLD: UPDATE-수정 전 레코드, DELETE-삭제할 레코드
  • NEW: INSERT-추가할 레코드, UPDATE:수정할 레코드

 CREATE OR REPLACE FUNCTION fnGrade
(
	score NUMBER
)
RETURN NUMBER
IS
    result NUMBER(2, 1);
BEGIN
	IF score >= 95 THEN result := 4.5;
    ELSIF score >= 90 THEN result := 4.0;
    ELSIF score >= 85 THEN result := 3.5;
    ELSIF score >= 80 THEN result := 3.0;
    ELSIF score >= 75 THEN result := 2.5;
    ELSIF score >= 70 THEN result := 2.0;
    ELSIF score >= 65 THEN result := 1.5;
    ELSIF score >= 60 THEN result := 1.0;
    ELSE result := 0.0;
    END IF;
    
    RETURN result;
END;
/

 

INSERT 행 트리거 생성

• score1 테이블의 데이터 추가 시 평점을 계산하여 score2 테이블에 데이터 추가

CREATE OR REPLACE TRIGGER scoreInserTrigger
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);
COMMIT;

 

UPDATE 행 트리거 생성

• score1 테이블의 데이터 수정 시 평점을 계산하여 score2 테이블에 데이터 추가

CREATE OR REPLACE TRIGGER scoreUpdateTrigger
AFTER UPDATE ON score1 
FOR EACH ROW 
DECLARE
BEGIN
	-- :OLD -> UPDATE 전 레코드
    -- :NEW -> 새로 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=100, eng=95, mat=85 WHERE hak='111';
COMMIT;

 

UPDATE 행 트리거 생성 : 기본키와 참조키 UPDATE

• 부모테이블(score1)의 기본키(hak) 변경되면, 자식테이블(score2)의 참조키(hak)도 변경되도록 UPDATE 트리거 수정
  • score1와 score2 테이블은 참조관계. score2에 존재하는 레코드의 참조키를 score1에서 수정하면 에러.

UPDATE score1 SET hak = '100' WHERE hak = '111';
-- 에러
    
-- 트리거 수정
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;    
/
    
UPDATE score1 SET hak = '100' WHERE hak = '111';

 

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