SQL 기초 - 18. 트리거(무결성, BEFORE/AFTER, 다중, 중첩, DLL)

이 글은 '김상형의 SQL 정복'의 예제 코드를 설명하는 방식으로 SQL 쿼리 예제를 소개하고 있습니다.

 

SQL 기초 - 18. 트리거(무결성, BEFORE/AFTER, 다중, 중첩, DLL)

트리거는 특정 사건이 발생했을 때 자동으로 호출되는 코드로, 직접 호출할 수 없으며 일정한 조건이 되면 자동으로 호출된다. 시스템이 호출하므로 인수를 전달할 수 없고 리턴값도 반환할 수 없다.

주로 테이블이나 데이터베이스에 변화가 발생할 때 실행할 코드를 트리거로 정의한다. 정의는 다음과 같다.

CREATE [OR REPLACE] TRIGGER 트리거이름
BEFORE | AFTER | INSTEAD OF
INSERT OR UPDATE OR DELETE [OF 컬럼]
ON 테이블명
[FOR EACH ROW]
BEGIN
	명령;
END;

 

이제 구체적인 예제 코드를 살펴보자.

-- 간단한 예제를 통해 알아보자
CREATE OR REPLACE TRIGGER TR_Message
AFTER UPDATE ON tCity
BEGIN
    DBMS_OUTPUT.PUT_LINE('도시 정보를 갱신하였습니다.');
END;

UPDATE tCity SET popu = popu + 1 WHERE name = '서울';


-- 각 INSERT, UPDATE, DELETE 시 각각 다른 메시지를 출력한다.
-- 이때 OR을 써야함에 주의하자
CREATE OR REPLACE TRIGGER TR_Message
AFTER INSERT OR UPDATE OR DELETE ON tCity
BEGIN
    IF INSERTING THEN
        DBMS_OUTPUT.PUT_LINE('새로운 도시를 삽입하였습니다.');
    END IF;
    IF UPDATING THEN
        DBMS_OUTPUT.PUT_LINE('도시 정보를 갱신하였습니다.');
    END IF;
    IF DELETING THEN
        DBMS_OUTPUT.PUT_LINE('도시를 삭제하였습니다.');
    END IF;
END;

INSERT INTO tCity VALUES ('여주',608,11,'n','경기');
UPDATE tCity SET popu = 12 WHERE name = '여주';
DELETE FROM tCity WHERE NAME = '여주';

-- 삭제를 하지 않고 트리거를 잠시 정지시켜 놓을 때는 다음 두 구문을 사용한다.
ALTER TRIGGER TR_Message ENABLE | DISABLE;
ALTER TABLE tCity ENABLE | DISABLE ALL TRIGGERS;

-- SQL 명령 하나에 대해 한 번 호출되는 트리거를 문장(Statement) 트리거라고 한다.
UPDATE tCity SET popu = popu + 1 WHERE region = '경기';

-- NEW는 INSERT, UPDATE시 변경된 후의 레코드를, OLD는 변경되기 전의 레코드를 가진다.
-- ':'을 앞에 붙여 이전과 이후를 비교해 볼 수 있다.
CREATE OR REPLACE TRIGGER TR_Message
AFTER UPDATE ON tCity
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(:OLD.name || ':' || :OLD.popu || '->' || :NEW.popu);
END;

-- OLD, NEW가 맘에 들지 않으면 FOR EACH ROW 앞에 다음 구문으로 별명을 지정한다.
REFERENCING OLD AS pre_rec NEW AS post_rec

UPDATE tCity SET area = area + 1 WHERE name = '서울';

-- 트리거 발생제한 : 행에 대한 모든 변화를 잡지 말고, 관심있는 열이 바뀌었을 때만 트리거가 발생하도록 하자.
-- AFTER UPDATE OF 열 ON 테이블  구문을 활용한다.
CREATE OR REPLACE TRIGGER TR_Message
AFTER UPDATE OF popu ON tCity 
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(:OLD.name || ':' || :OLD.popu || '->' || :NEW.popu);
END;

CREATE OR REPLACE TRIGGER TR_Message
AFTER UPDATE ON tCity 
FOR EACH ROW
WHEN (NEW.popu > 10)
BEGIN
    DBMS_OUTPUT.PUT_LINE(:OLD.name || ':' || :OLD.popu || '->' || :NEW.popu);
END;

UPDATE tCity SET popu = popu + 1 WHERE region = '강원';

-- 트리거의 예외 발생 : 트리거는 무결성을 지키는 궁극의 장치다.
-- 하지만 무겁고 느리기 때문에 규칙 검사 체크, 기본값, NULL허용 등의 간편한 제약을 써야 한다.
-- 트리거가 중첩되면 무한 루프에 빠지거나 성능이 떨어질 수 있다.	
CREATE OR REPLACE TRIGGER TR_Message
AFTER UPDATE ON tCity
FOR EACH ROW
BEGIN
    IF :NEW.popu > 10000 THEN
        RAISE_APPLICATION_ERROR(-20000, '인구가 1억을 넘을 수는 없습니다.');
    END IF;
    DBMS_OUTPUT.PUT_LINE(:OLD.name || ':' || :OLD.popu || '->' || :NEW.popu);
END;

UPDATE tCity SET popu = 12345 WHERE name = '서울';

-- AFTER 문을 쓰게 되면, 먼저 삽입을 한 다음 롤백을 하는 식으로 트리거가 동작한다.
CREATE OR REPLACE TRIGGER TR_PreventChoi
AFTER INSERT ON tStaff
FOR EACH ROW
BEGIN
    IF SUBSTR(:NEW.name,1,1) = '최' THEN
        RAISE_APPLICATION_ERROR(-20000, '최가는 안되!');
    END IF;
END;

INSERT INTO tStaff VALUES ('최무선','총무부','남','2021-6-25','사원',280,0);

-- 만약 성이 최씨인 사람을 김씨로 바꾸어 삽입하려고 하면, BEFORE를 써야 한다.
CREATE OR REPLACE TRIGGER TR_PreventChoi
BEFORE INSERT ON tStaff
FOR EACH ROW
BEGIN
    IF SUBSTR(:NEW.name,1,1) = '최' THEN
        DBMS_OUTPUT.PUT_LINE('최가는 안되');
        :NEW.name := '김' || SUBSTR(:NEW.name, 2);
    END IF;
END;

CREATE OR REPLACE TRIGGER TR_DoubleMoney
BEFORE INSERT ON tMember
FOR EACH ROW
BEGIN
    IF :NEW.age = 18 THEN
        :NEW.money := :NEW.money * 2;
    END IF;
END;

INSERT INTO tMember (member, age, email, addr, money) VALUES 
('신입생', 18, 'fresher@kyunghee.ac.kr', '서울 회기동', 8000);

-- 다음 뷰는 자동차와 제조사의 정보를 조인해서 보여준다.
CREATE VIEW vCarMaker AS
SELECT tCar.*, factory, domestic FROM tCar INNER JOIN tMaker ON
tCar.maker = tMaker.maker;

INSERT INTO vCarMaker VALUES('티코', 800, 900, '대우', '울릉', 'y');

-- 다음 트리거를 정의하면 위의 INSERT문을 실행했을때 자동으로 두 개의 뷰로 나누어 삽입을 실행한다.	
CREATE OR REPLACE TRIGGER TR_AddNewCar
INSTEAD OF INSERT ON vCarMaker
FOR EACH ROW
BEGIN
	INSERT INTO tCar (car, capacity, price, maker) VALUES 
        (:NEW.car, :NEW.capacity, :NEW.price, :NEW.maker);
	INSERT INTO tMaker (maker, factory, domestic) VALUES 
        (:NEW.maker, :NEW.factory, :NEW.domestic);
END;

CREATE OR REPLACE TRIGGER TR_Message2
AFTER UPDATE ON tCity
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(:OLD.name || '면적:' || :OLD.area || '->' || :NEW.area);
END;

CREATE OR REPLACE TRIGGER TR_Message3
AFTER UPDATE ON tCity
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(:OLD.name || '지역:' || :OLD.region || '->' || :NEW.region);
END;

-- 비슷한 여러 개의 트리거를 사용할 때, FOLLOWS를 이용하여 순서를 지정할 수 있다.
CREATE OR REPLACE TRIGGER TR_Message3
AFTER UPDATE ON tCity
FOR EACH ROW
FOLLOWS TR_Message
BEGIN
    DBMS_OUTPUT.PUT_LINE(:OLD.name || '지역:' || :OLD.region || '->' || :NEW.region);
END;

UPDATE tCity SET area = 1000, popu = 12345 WHERE name = '서울';

-- 중첩 트리거는 트리거가 또 다른 트리거를 호출하는 구조이다.
-- 이전의 TR_AddNewCar 트리거에서 TR_OnNewCar를 호출한다.
-- 중첩은 일반적으로 바람직하지 않고, 신중하게 정의해야 한다.
CREATE OR REPLACE TRIGGER TR_OnNewCar
AFTER INSERT ON tCar
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('신차가 출시되었습니다.');
END;

INSERT INTO vCarMaker VALUES('티코', 800, 900, '대우', '울릉', 'y');

-- DDL 트리거 : CREATE, DROP, ALTER 같은 이벤트를 설치하면 서버의 상태를 쉽게 모니터링할 수 있다.
CREATE OR REPLACE TRIGGER TR_Change
AFTER DDL ON DATABASE
BEGIN
    DBMS_OUTPUT.PUT_LINE('명령 : ' || ora_sysevent);
    DBMS_OUTPUT.PUT_LINE('타입 : ' || ora_dict_obj_type);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ora_dict_obj_name);
END;

CREATE TABLE tTemp ( id INT );
DROP TABLE tTemp;

CREATE TABLE tCityHistory
(
    dt DATE,
    suser VARCHAR(20),
    ip VARCHAR(20),
    action VARCHAR(10),
    name CHAR(10),
    area VARCHAR(30) NULL,
    popu VARCHAR(30) NULL
);

-- 변경 이력 관리 : 트리거를 이용해 tCityHistory에 변경 이력을 저장하는 예제이다.
CREATE OR REPLACE TRIGGER TR_History
AFTER INSERT OR UPDATE OR DELETE ON tCity
FOR EACH ROW
DECLARE
    areaChange VARCHAR(30);
    popuChange VARCHAR(30);
    suser VARCHAR(20);
    ip VARCHAR(20);
BEGIN
    SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') INTO suser FROM DUAL;
    SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO ip FROM DUAL;
    
    IF INSERTING THEN
        INSERT INTO tCityHistory VALUES 
            (SYSDATE, suser, ip, 'INSERT', :NEW.name, :NEW.area, :NEW.popu);
    END IF;
    IF UPDATING THEN
        IF :OLD.area = :NEW.area THEN
            areaChange := :OLD.area;
        ELSE
            areaChange := :OLD.area || '->' || :NEW.area;
        END IF;
        IF :OLD.popu = :NEW.popu THEN
            popuChange := :OLD.popu;
        ELSE
            popuChange := :OLD.popu || '->' || :NEW.popu;
        END IF;
        INSERT INTO tCityHistory VALUES 
            (SYSDATE, suser, ip, 'UPDATE', :NEW.name, areaChange, popuChange);
    END IF;
    IF DELETING THEN
        INSERT INTO tCityHistory VALUES 
            (SYSDATE, suser, ip, 'DELETE', :OLD.name, :OLD.area, :OLD.popu);
    END IF;
END;