트리거와 이벤트

트리거(trigger)는 표에 INSERT·UPDATE·DELETE가 일어날 때 데이터베이스가 저절로 실행하는 절차형 SQL입니다. 정해진 시각에 실행되는 이벤트(event)와는 깨우는 것이 다릅니다.

프로시저와 함수는 누군가 이름을 불러야 돕니다. 그런데 '점수를 고치면 반드시 변경 이력을 남긴다' 같은 규칙은 부르는 쪽이 한 번만 잊어도 깨집니다. 데이터를 바꾸는 프로그램이 여럿이면 모두가 기억해야 합니다. 트리거는 그 규칙을 표에 붙여 두어, 누가 어떤 경로로 데이터를 바꾸든 같은 처리가 일어나게 합니다. 아래 그림은 수강 표에 행이 들어가는 순간 트리거가 깨어나 기록 표에 행을 더하는 장면입니다.

AFTER INSERT ON 수강FOR EACH ROW

ON SCHEDULE EVERY 1 DAYDO  DELETE FROM 수강기록

AFTER INSERT ON 수강FOR EACH ROW

1트리거를 만드는 꼴 — 네 자리

트리거에는 부르는 문장이 없습니다. 그래서 '언제 깨어날지'를 만들 때 적어 둡니다. 그 자리가 머리의 두 줄입니다.

CREATE TRIGGER 이름
AFTER INSERT ON 표
FOR EACH ROW
BEGIN
  실행할 문장;
END;

• 이벤트 자리의 시점은 BEFORE 또는 AFTER입니다. 변경 문장 자리에는 INSERT·UPDATE·DELETE를 쓰고, OR로 여럿을 묶어도 됩니다.
• FOR EACH ROW가 있으면 바뀐 행마다 한 번씩 깨어납니다(행 트리거). 없으면 문장 하나에 한 번만 깨어납니다(문장 트리거). 세 행을 고치는 UPDATE 하나에 행 트리거는 세 번, 문장 트리거는 한 번 돕니다.
• 지울 때는 DROP TRIGGER 이름;입니다.
• 트리거 몸에는 COMMIT·ROLLBACK을 쓸 수 없습니다. 쓰면 트리거를 깨운 문장이 ORA-04092 오류로 막힙니다. 트리거는 자기를 깨운 문장과 같은 트랜잭션 안에서 돌기 때문입니다.

2:NEW와 :OLD — 바뀌는 행을 가리키는 이름

행 트리거는 지금 바뀌는 행의 값을 볼 수 있습니다. 그 행을 가리키는 이름이 둘입니다. 앞에 콜론을 붙이고 :NEW.학번처럼 칸 이름을 점으로 잇습니다.

• 들어오는 쪽이 :NEW, 나가는 쪽이 :OLD입니다. INSERT에는 나가는 행이 없고 DELETE에는 들어오는 행이 없습니다.
• BEFORE 행 트리거에서는 :NEW에 값을 담을 수 있습니다. 저장되기 전이라 담은 값이 그대로 저장됩니다. 비어 있는 칸을 기본값으로 채우거나 범위를 벗어난 값을 다듬을 때 씁니다.
• AFTER 트리거에서는 :NEW에 담지 못합니다. 이미 저장된 뒤이기 때문입니다. 담으려 하면 만들 때 ORA-04084 오류가 납니다.
• 문장 트리거에는 :NEW·:OLD가 없습니다. 어느 한 행을 가리킬 수 없기 때문입니다. 쓰면 만들 때 ORA-04082 오류가 납니다.

3트리거와 이벤트 — 무엇이 깨우나

낱말이 겹칩니다. 트리거를 만들 때 쓰는 '이벤트'는 트리거를 깨우는 데이터 변경(AFTER INSERT ON 수강)입니다. 여기서 트리거와 견주는 '이벤트(event)'는 다른 것으로, 정해진 때가 되면 데이터베이스가 실행하는 일거리입니다.

이벤트를 만드는 꼴입니다. Oracle에는 이 문장이 없어 돌려 볼 수 없습니다. 같은 일은 DBMS_SCHEDULER로 합니다.

CREATE EVENT 밤정리
ON SCHEDULE EVERY 1 DAY
DO
  DELETE FROM 수강기록
  WHERE 작업 = '등록';

• ON SCHEDULE AT 시각은 그때 한 번만, ON SCHEDULE EVERY 간격은 그 간격마다 되풀이합니다.
• DO 다음에 실행할 문장을 적습니다.

예제

표를 바꾸면 트리거가 따라 도는 것을 봅니다. 기록을 담을 표는 블록 안에서 만듭니다. 블록마다 처음 데이터에서 시작합니다 — 앞 블록이 만들거나 바꾼 것은 남아 있지 않습니다.

AFTER INSERT — 들어간 행을 기록한다

그림의 트리거입니다. 수강 표에 행이 들어가면 기록 표에 한 줄이 쌓입니다.

CREATE TABLE 수강기록 (
  학번 NUMBER,
  과목코드 VARCHAR2(10),
  작업 VARCHAR2(10)
);
CREATE TRIGGER 수강_등록기록
AFTER INSERT ON 수강
FOR EACH ROW
BEGIN
  INSERT INTO 수강기록
  VALUES (:NEW.학번,
      :NEW.과목코드, '등록');
END;
/
INSERT INTO 수강
VALUES (106, 'DS', 88);
SELECT 학번,
  과목코드,
  작업
FROM 수강기록;

기록 표에 INSERT하는 문장은 어디에도 적지 않았습니다. 수강 표에 넣는 문장 하나가 트리거를 깨웠습니다.

AFTER UPDATE — :OLD와 :NEW를 함께 남긴다

운영체제(OS) 점수를 10점씩 올립니다. 세 행이 바뀌므로 행 트리거가 세 번 깨어납니다.

CREATE TABLE 점수기록 (
  학번 NUMBER,
  옛점수 NUMBER,
  새점수 NUMBER
);
CREATE TRIGGER 점수_변경기록
AFTER UPDATE ON 수강
FOR EACH ROW
BEGIN
  INSERT INTO 점수기록
  VALUES (:OLD.학번,
      :OLD.점수, :NEW.점수);
END;
/
UPDATE 수강
SET 점수 = 점수 + 10
WHERE 과목코드 = 'OS';
SELECT 학번,
  옛점수,
  새점수
FROM 점수기록
ORDER BY 학번;

UPDATE 한 문장에 기록이 세 줄 쌓였습니다. 행마다 한 번씩 깨어났기 때문입니다. 이 트리거에서 FOR EACH ROW를 빼면 문장 트리거가 되는데, 몸이 :OLD·:NEW를 쓰고 있어 만들 때 ORA-04082 오류가 납니다. 문장마다 한 번만 돌게 하려면 몸에서 :OLD·:NEW를 쓰지 않아야 합니다.

BEFORE INSERT — 저장되기 전에 값을 다듬는다

점수가 100점을 넘으면 100으로 깎아 넣습니다. 저장되기 전이라 :NEW에 값을 담을 수 있습니다.

CREATE TRIGGER 점수상한
BEFORE INSERT OR UPDATE ON 수강
FOR EACH ROW
BEGIN
  IF :NEW.점수 > 100 THEN
    :NEW.점수 := 100;
  END IF;
END;
/
INSERT INTO 수강
VALUES (106, 'OS', 120);
SELECT 학번,
  과목코드,
  점수
FROM 수강
WHERE 학번 = 106;

120을 넣었는데 100이 저장됐습니다. 같은 일을 AFTER 트리거로 하려 하면 만들 때 ORA-04084 오류가 납니다.

시험 포인트

• 필기: 트리거는 데이터 변경이 일어날 때 저절로 실행됩니다. 만드는 문장은 CREATE TRIGGER이고, 머리에 시점(BEFORE·AFTER)과 변경 문장(INSERT·UPDATE·DELETE)과 표를 적습니다. FOR EACH ROW가 있으면 행 트리거(row trigger)로 바뀐 행마다, 없으면 문장 트리거(statement trigger)로 문장마다 한 번 돕니다. 바뀌는 행을 가리키는 이름은 :NEW와 :OLD이고, INSERT에는 :OLD가, DELETE에는 :NEW가 없습니다. :NEW에 값을 담는 것은 BEFORE에서만 됩니다. 트리거 몸에는 COMMIT·ROLLBACK을 쓸 수 없습니다. 이벤트와 가르는 기준은 깨우는 것이 데이터 변경인지 정해진 시각인지입니다.
• 실기: 이름을 쓰게 하면 트리거(Trigger)와 이벤트(Event)이고, 바뀌는 행을 가리키는 이름은 :NEW와 :OLD입니다. 문장을 쓰게 하면 CREATE TRIGGER 이름·AFTER INSERT ON 표·FOR EACH ROW·BEGIN … END; 네 자리를 모두 쓰고, 바뀌는 행은 :NEW.칸이름처럼 콜론과 점까지 씁니다.

확인 문제

1. 학과 표에 새 학과를 넣을 때, 건물 칸을 비워 두고 넣으면 저장되기 전에 '본관'으로 채워 두려고 합니다. 트리거의 시점과 범위로 알맞은 것을 고르세요.

• ① BEFORE INSERT, FOR EACH ROW 있음
• ② BEFORE INSERT, FOR EACH ROW 없음
• ③ AFTER INSERT, FOR EACH ROW 있음
• ④ AFTER INSERT, FOR EACH ROW 없음

2. 다음 (가)~(라)에 맞는 말을 쓰세요. (가)·(나)는 한글 이름과 영문으로 함께, (다)·(라)는 트리거 몸에서 쓰는 꼴 그대로 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 표에 변경이 일어날 때 데이터베이스가 저절로 실행하는 것입니다. 부르는 문장이 따로 없고, 깨어날 때를 만들 때 적어 둡니다.
• (나) 데이터가 그대로여도 정해진 시각이 되면 실행되는 것입니다. 특정 표에 매이지 않고 데이터베이스에 걸립니다.
• (다) UPDATE로 깨어난 (가) 안에서 고치기 전의 값을 가리키는 이름입니다. DELETE로 깨어나면 지워지는 행을 가리킵니다.
• (라) INSERT로 깨어난 (가) 안에서 들어가는 행을 가리키는 이름입니다. BEFORE 시점이면 여기에 값을 담아 저장될 값을 바꿀 수 있습니다.

3. 학생 표에서 행이 지워질 때마다 지워진 학번과 이름을 삭제기록 표에 남기는 트리거를 쓰세요. 표를 만드는 문장부터 씁니다. 답을 끝까지 쓴 뒤 정답을 펼치세요.

CREATE TABLE 삭제기록 (
  학번 NUMBER,
  이름 VARCHAR2(10)
);
CREATE TRIGGER 학생_삭제기록
AFTER DELETE ON 학생
FOR EACH ROW
BEGIN
  INSERT INTO 삭제기록
  VALUES (:OLD.학번,
      :OLD.이름);
END;