sql trigger

예전에 어떤 유튜브 영상 보면서 정리했던 것

 

sql trigger란

 

데이터베이스에서 어떤 이벤트가 발생했을 때

자동적으로 실행되는 프로시저

 

데이터베이스 시스템에서 데이터의 삽입, 갱신, 삭제 등의 이벤트가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL

이벤트=> 데이터 조작 작업

 

데이터에 변경이 생겼을 때(CRUD)

이것이 계기가 되어 자동적으로 실행되는 프로시저

 

 

목적

 

데이터 변경 및 무결성 유지

로그 메시지 출력

 

 

ex. 사용자의 닉네임 변경 이력을 저장하는 트리거

 

users (id, nickname)

users_log (id, nickname, until) 테이블이 있을 때

 

update가 발생할 때마다,

업데이트 이전에 실행

ON [테이블명] => 해당 테이블에 업데이트가 일어날 때

업데이트가 일어나는 각 row에 대해 => for each row

OLD => (update)업데이트 되기 전의 튜플을 가리킴, 혹은 (delete시) delete된 튜플을 가리킴

NEW라는 키워드도 사용할 수 있음

 

 

ex. <학생> 테이블에 새로운 튜플이 삽입될 때, 삽입되는 튜플에 학년 정보가 누락됐으면 ‘학년’ 속성에 ‘신입생’을 저장하는 트리거를 ‘학년정보_tri’라는 이름으로 정의하시오.

 

오라클 예시

 

CREATE TRIGGER 학년정보_tri BEFORE INSERT ON 학생

REFERENCING NEW AS new_table

FOR EACH ROW

WHEN (new_table.학년 IS NULL)

BEGIN

:new_table.학년 := ‘신입생’;

END;

 

 

NEW 또는 OLD로 지정된 테이블 이름 앞에는 콜론이 들어간다

A :=B A에 B를 저장하라는 의미

 

 

ex 2. 사용자가 마트에서 상품을 구매할 때마다 지금까지 누적된 구매 비용을 구하는 트리거 작성하기

 

BUY 테이블 (id, user_id, price, buy_at)

USER_BUY_STATS 테이블이 있을 때 (user_id, price_sum) => 누적 구매 비용

 

insert되는 각 row에 대해서 액션 수행

 

NEW: insert된 tuple을 가리킴, update된 후의 tuple을 가리킴

 

• update, insert, delete 등을 한번에 감지하도록 설정 가능하다 (MySQL은 불가능)

 

postgre는 가능

 

 

for each row 문제점

update employee set salary = 1.0*salary where dept_id=1003;

1003부서에 임직원이 다섯 명이 있다면 avg_empl_salary_trigger는 다섯번 실행된다

=> 비효율적으로 동작한다

 

효율적으로 동작하려면(트리거가 한 번만 실행되려면)

for each statement로 바꾸기

 

 

• row 단위가 아니라 statement 단위로 trigger가 실행될 수 있도록 할 수 있다(my sql은 불가능)
• trigger를 발생시킬 디테일한 조건을 지정할 수 있다(mysql은 불가능)

 

when 조건을 만족해야 한다

기존의 닉네임과 다를 때만 실행

 

 

ex. 사원 테이블이 갱신될 때, 갱신된 튜플마다 태도, 성과 속성의 평균을 계산하여 50 이상이면 우수를, 아니면 미달을 화면에 출력하는 트리거 정의

 

CREATE TRIGGER 사원_tri AFTER UPDATE ON 사원

FOR EACH ROW

BEGIN

IF (태도+성과)/2 >= 50 THEN

DBMS_OUTPUT.PUT_LINE(‘우수’);

ELSE

DBMS_OUTPUT.PUT_LINE(‘미달);

END_IF;

END;

 

DBMS_OUTPUT.PUT_LINE은

PL/SQL에서 콘솔(버퍼)에 문자열을 출력할 때 사용

 

 

ex.

CREATE OR REPLACE M_TRI

BEFORE INSERT

ON MEM_SC

REFERENCING NEW AS n

FOR EACH ROW

BEGIN

IF :n.M_ATTD IS NULL THEN

SELECT AVG(M_ATTD) INTO :n.M_ATTD FROM MEM_SC;

ELSIF :n.M_MARK IS NULL THEN

SELECT AVG(M_MARK) INTO :n.M_MARK FROM MEM_SC;

END IF;

DBMS_OUTPUT.PUT_LINE(:n.M_ATTD + :n.ㅡM_MARK);

END:

 

NEW 또는 OLD로 지정된 테이블 이름 앞에는 콜론이 들어간다

A :=B A에 B를 저장하라는 의미

 

 

트리거의 제거

 

DROP TRIGGER 트리거명;

 

 

 

• 주의사항

(1) 소스 코드로는 발견할 수 없는 로직이기 때문에 

어떤 동작이 일어나는지 파악하기 어렵고 문제가 생겼을 때 대응하기 어렵다

 

로직티어=>애플리케이션 실행

웹 애플리케이션에서는 트리거의 존재를 알기 어렵다

 

프로시저라면 소스 코드에서 호출하기라도 하는데.

 

가시적이지 않아서 개발도, 관리도, 문제 파악도 힘들다

 

(2) 트리거가 여러 개 등록 돼 있다면 파악하기 힘들다

 

과도한 트리거 사용은 DB에 부담을 주고 응답을 느리게 만든다.

 

디버깅이 어렵다.

 

문서 정리가 특히나 중요하다.

 

트리거는 최후의 카드로 남겨놓는 것이 좋아보인다.