프로시저 -> 자주 사용되는 쿼리 저장 -> 유저가 필요하면 실행
트리거 -> 쿼리 저장 -> 자동으로 실행 (서버에 부하를 줄 수 있다.)
트리거 ? DML 이나 DDL 명령이 실행되면 자동으로 실행되도록 정의한 동작
트리거는 CREATE TRIGGER 권한이 있어야 만들 수 있다.
관리자(sys)에서 GRANT CREATE TRIGGER TO 계정; 을 통해 권한을 줄 수 있다.
문장 트리거 : DML 작업이 여러번 일어나도 트리거는 한번만 실행. 일반적으로 로그 등을 출력.
실습을 위한 테이블을 작성해봅시다.
CREATE TABLE insa (
num VARCHAR2(30) PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
dept VARCHAR2(50) NOT NULL,
sal NUMBER NOT NULL
);
CREATE TABLE insa_time (
memo VARCHAR2(1000),
reg_date DATE DEFAULT SYSDATE
);
SELECT* FROM tab; -- 테이블 생성 확인
SELECT * FROM user_sys_privs; -- 권한 확인
-- 트리거에서 DML 문은 자동으로 COMMT/ROLLBACK되므로 COMMIT이나 ROLLBACK을 하지 않습니다.
CREATE OR REPLACE TRIGGER tri_insa
AFTER INSERT OR UPDATE OR DELETE ON insa
BEGIN
IF INSERTING THEN
INSERT INTO insa_time(memo) VALUES('추가 ...');
ELSIF UPDATING THEN
INSERT INTO insa_time(memo) VALUES('수정 ...');
ELSIF DELETING THEN
INSERT INTO insa_time(memo) VALUES('삭제 ...');
END IF;
END;
/
-- 트리거 목록 확인
SELECT * FROM user_triggers;
SELECT * FROM user_source;
INSERT INTO insa VALUES ('11', '김자바', '개발부', 100);
INSERT INTO insa VALUES ('22', '너자바', '개발부', 100);
INSERT INTO insa VALUES ('33', '다자바', '개발부', 100);
COMMIT;
위 데이터를 추가하면 insa_time에 있는 memo컬럼에 데이터 추가의 시간과 추가... 라는 메모가 같이 적혀져있는 것을 확인할 수 있습니다.
SELECT memo, TO_CHAR(reg_date, 'YYYY-MM-DD HH24:MI:SS') reg_date FROM insa_time;
위 쿼리를 이용하면 insa_time에 작성된 memo의 날짜뿐만 아닌 시, 분, 초를 확인할 수 있습니다.
-- 지정된 시간에만 DML이 이루어 지도록 문장 트리거 만들기
CREATE OR REPLACE TRIGGER tri_insa
AFTER INSERT OR UPDATE OR DELETE ON insa
BEGIN
IF TO_CHAR(SYSDATE, 'D') IN (1, 7) OR -- 일요일이거나 토요일이면
( TO_CHAR(SYSDATE, 'HH24') >= 20 AND TO_CHAR(SYSDATE, 'HH24') <= 8 ) THEN
RAISE_APPLICATION_ERROR(-20001, '지금은 근무 시간이 아닙니다.');
END IF;
END;
/
일요일이거나 토요일이면 예외처리를 통해 '지금은 근무 시간이 아닙니다.'를 출력하는 트리거입니다. 또 안에 적혀져 있는 시간을 통해 오후 8시 부터 오전 8시 까지를 예외처리를 했습니다.
-- 트리거 지우기
DROP TRIGGER tri_insa;
DROP TRIGGER 트리거명; 을 통해 삭제할 수 있습니다.
-- 행 트리거
DML 문장이 일어난 횟수만큼 트리거가 실행됩니다. 예를 들어 DELETE FROM insa;에서 30개 레코드가 삭제되면 트리거도 30번 실행됩니다.
새로운 실습을 위해 테이블을 생성합니다.
CREATE TABLE score1 (
hak VARCHAR2(20) NOT NULL
,name VARCHAR2(30) NOT NULL
,kor NUMBER(3) NOT NULL
,eng NUMBER(3) NOT NULL
,mat NUMBER(3) NOT NULL
,CONSTRAINT pk_score1_hak PRIMARY KEY(hak)
);
CREATE TABLE score2 (
hak VARCHAR2(20) NOT NULL
,kor NUMBER(2,1) NOT NULL
,eng NUMBER(2,1) NOT NULL
,mat NUMBER(2,1) NOT NULL
,CONSTRAINT pk_score2_id PRIMARY KEY(hak)
,CONSTRAINT fk_score2_id FOREIGN KEY(hak)
REFERENCES score1(hak)
);
CREATE OR REPLACE FUNCTION fnGrade
(
pScore NUMBER
)
RETURN NUMBER
IS
n NUMBER(2,1);
BEGIN
IF pScore>=95 THEN n:=4.5;
ELSIF pScore>=90 THEN n:=4.0;
ELSIF pScore>=85 THEN n:=3.5;
ELSIF pScore>=80 THEN n:=3.0;
ELSIF pScore>=75 THEN n:=2.5;
ELSIF pScore>=70 THEN n:=2.0;
ELSIF pScore>=65 THEN n:=1.5;
ELSIF pScore>=60 THEN n:=1.0;
ELSE n:=0.0;
END IF;
RETURN n;
END;
/
-- 행트리거 : INSERT
CREATE OR REPLACE TRIGGER triScoreInsert
AFTER INSERT ON score1
FOR EACH ROW -- 행 트리거
DECLARE
-- 변수선언 영역
BEGIN
-- :NEW -> score1에서 INSERT 한 행. 행트리거만 사용 가능
INSERT INTO score2(hak, kor, eng, mat) VALUES (:NEW.hak,
fnGrade(:NEW.kor), fnGrade(:NEW.eng), fnGrade(:NEW.mat) );
END;
/
score1 테이블에 hak, name, kor, eng, mat을 입력하면 자동으로 score2테이블 hak에는 입력받은 값을 입력해주고 kor, eng, mat에는 평점을 계산하여 넣어주는 트리거가 작성되었습니다.
INSERT INTO score1(hak, name, kor, eng, mat) VALUES ('1', 'a', 95, 100, 80);
INSERT INTO score1(hak, name, kor, eng, mat) VALUES ('2', 'b', 70, 90, 100);
COMMIT;
SELECT * FROM score1;
SELECT * FROM score2;
-- 행트리거 : UPDATE
CREATE OR REPLACE TRIGGER triScoreUpdate
AFTER UPDATE ON score1
FOR EACH ROW -- 행 트리거
DECLARE
-- 변수선언 영역
BEGIN
-- :NEW -> score1에 새로 UPDATE할 레코드
-- :OLD -> score1에서 UPDATE 하기 전 레코드
UPDATE score2 SET kor = fnGrade(:NEW.kor), eng = fnGrade(:NEW.eng), mat = fnGrade(:NEW.mat)
WHERE hak = :OLD.hak ;
END;
/
score1이 UPDATE로 데이터가 바뀌면 score2에 score1에서 값을 연산해서 넣어줍니다.
OLD.hak 은 바뀌기 전의 hak 을 말합니다.
UPDATE score1 SET kor = 100, eng = 100, mat = 100 WHERE hak = '1';
COMMIT;
SELECT * FROM score1;
SELECT * FROM score2;
-- 행 트리거 : DELETE
CREATE OR REPLACE TRIGGER triScoreDELETE
BEFORE DELETE ON score1
FOR EACH ROW -- 행 트리거
DECLARE
-- 변수선언 영역
BEGIN
-- :OLD -> score1에서 DELETE할 레코드
DELETE FROM score2 WHERE hak = :OLD.hak ;
END;
/
여기서는 score1의 데이터가 삭제되기전에 score2에 입력한 hak에 해당되는 자료를 먼저 지워줍니다. score2 테이블에서 hak은 참조키 이므로, 부모테이블에서 해당 hak을 지우고 싶으면 자식을 먼저 지워야 되기때문에 자동으로 자식을 먼저 지워지게 한 것입니다.
DELETE FROM score1 WHERE hak = '1';
COMMIT;
SELECT * FROM score1;
SELECT * FROM score2;
