SQL - 트리거

트리거

• 데이터베이스의 특정 이벤트가 발생할 때 자동으로 실행되는 일련의 작업들을 말한다.

• 데이터의 일관성과 무결성을 유지하기 위해 사용한다.

트리거는 다음과 같은 이점을 제공한다.

• 자동화: 트리거를 사용하면 특정 이벤트가 발생할 때마다 자동으로 작업이 실행되므로, 작업의 자동화가 가능해진다.

• 일관성: 트리거를 사용하면 데이터베이스의 일관성을 유지하기 위한 작업이 자동으로 실행되므로, 데이터 일관성을 보장할 수 있다.

트리거는 다음과 같은 단점도 있다.

• 복잡성: 트리거가 복잡해질수록 유지보수가 어려워진다.
• 오작동: 트리거가 잘못 작성될 경우 예상치 못한 결과를 초래할 수 있다.

다음과 같은 상황에서 트리거를 사용할 수 있다.

• 데이터 변경 시 자동으로 로그를 기록하고, 변경 이력을 추적한다.

• 특정 테이블에 데이터가 삽입되거나 삭제될 때, 해당 데이터의 관련 데이터도 함께 삭제하거나 수정한다.

다음은 Oracle 데이터베이스에서 트리거를 작성하는 예시이다.

1. insert를 실행할 시 트리거 예시

-- insert 에는 idx가 없고, update에는 idx가 있음
create or REPLACE TRIGGER student3_insert_trg
-- for each row (:old.score, :new score)
-- declare

-- after는 insert로 값을 넣은 다음에 update 실행
AFTER
    insert on student3
    -- insert만 걸어 놓았기 때문에 수정시에는 트리거가 실행되지 않는다.
begin
    update student3
         set
            total = kor + eng + mat,
            average = (kor + eng + mat)/3;
end;

create or replace trigger student3_trg 구문을 사용하여 student3_trg라는 이름의 트리거를 생성한다. 이 트리거는 student3 테이블에 대한 트리거이며, 트리거는 insert 이벤트가 발생한 후에 실행된다.

트리거는 begin과 end 사이에 있는 코드 블록으로 구성되어 있다. 이 코드 블록은 insert 이벤트가 발생하면 실행되며, student3 테이블의 total과 average 열 값을 갱신하는 역할을 한다.

total 열은 kor, eng, math 열 값의 합을 저장하고, average 열은 kor, eng, math 열 값의 평균을 저장한다. 이를 위해 update 문을 사용하여 student3 테이블의 total과 average 열 값을 갱신한다.

이 코드는 데이터 일관성과 무결성을 유지하기 위한 목적으로 사용된다. insert 이벤트가 발생하면 자동으로 total과 average 열 값을 갱신하여 데이터 일관성을 보장한다.

2. update를 실행할 시 트리거 예시

create or replace trigger student3_update_trg

-- BEFORE를 사용하므로, 업데이트가 수행되기 전에 트리거 내부의 코드가 실행된다. 
-- 이를 통해 새로운 값으로 업데이트하기 전에 트리거를 통해 값을 수정할 수 있다.
before
    update of kor, eng, math on student3 

-- for each가 없으면 new, old를 사용할 수 없다
for each row -- (old.score, :new.score)

    -- update 시에는 다시 update를 수행하지 않고 new에 값을 대입하는 형식으로 진행
    -- for each row 를 사용하여 :new와 :old로 구분
    -- := 는 대입 연산이다
begin
    -- 트리거 내부의 코드는 :new.total과 :new.average 에 업데이트될 값을 대입한다
    :new.total := :new.kor + :new.eng + :new.math; -- 합계를 새로운 값으로 계산해주고나서 update로 들어간다
    :new.average := (:new.kor + :new.eng + :new.math) / 3; -- 평균을 새로운 값으로 계산해주고나서 update로 들어간다
            
end;
-- 이 트리거는 kor, eng, math 열 중 하나라도 업데이트될 때마다 해당 레코드의 total과 average 열을 자동으로 갱신하여 데이터 일관성을 유지한다

create or replace trigger student3_update_trg 구문을 사용하여 student3_update_trg라는 이름의 트리거를 생성한다. 이 트리거는 student3 테이블에 대한 트리거이며, 업데이트 이벤트가 발생하기 전에 실행된다.

트리거는 before과 after 중 하나를 선택하여 사용할 수 있으며, 이 경우에는 before을 사용한다. 업데이트 이벤트가 발생하기 전에 트리거 내부의 코드가 실행되므로, 업데이트 이벤트가 발생하기 전에 새로운 값을 수정할 수 있다.

for each row 구문은 업데이트 이벤트가 발생하는 각 행에 대해 코드 블록을 실행하도록 지정하는 역할을 한다. 이를 통해 업데이트 이벤트가 발생하는 모든 행에 대해 트리거를 실행할 수 있다.

트리거 내부의 코드 블록에서는 :new.total과 :new.average에 업데이트될 값을 대입한다. 업데이트될 값은 :new.kor, :new.eng, :new.math 열 값의 합과 평균으로 계산된다. 이를 위해 :new.kor, :new.eng, :new.math 열 값의 합을 계산하여 :new.total에 대입하고, 합의 평균을 계산하여 :new.average에 대입한다.

이 코드는 업데이트 이벤트가 발생하기 전에 자동으로 total과 average 열 값을 갱신하여 데이터 일관성을 보장하는 목적으로 사용된다. 새로운 값으로 업데이트하기 전에 트리거를 통해 값을 수정할 수 있으며, 이를 통해 데이터 무결성을 유지할 수 있다.