SQL 트리거(Trigger)의 개념

트리거(Trigger)

SQL에서 트리거(Trigger)는 특정이벤트(INSERT,UPDATE,DELETE)가 발생할때 자동으로 실행되는 것을 말한다

즉 어떤 테이블에서 특정이벤트가 발생했을때 실행시키고자 하는 쿼리작업을 자동으로 수행할수 있게 미리 설정해두는것이다

장점

• 여러 SQL 문 또는 서브프로그램을 일일이 실행할필요없이 데이터 관련작업을 간편하게 수행할수 있다
• 제약조건만으로 구현이 어려운 복잡한 규칙을 정의할수 있다
• 데이터 변경과 관련된 일련의 정보를 기록하여 여러사용자가 공유하는 데이터의보안성과 안정성을 높일수있다

단점으로는 무분별한 사용시 데이터베이스의 성능을 저하시키는 원인이 될수있다 따라서 필요한 곳에만 사용해야한다

트리거 주요 키워드의 의미

키워드	의미
BEFORE / AFTER	작업이 실행되기 전 / 후 실행
INSERT / UPDATE / DELETE	트리거가 실행되는 조건
FOR EACH ROW	한 행(row)마다 실행하겠다는 의미 (거의 항상 씀)

기본 문법

CREATE OR REPLACE TRIGGER 트리거이름
BEFORE | AFTER
INSERT OR UPDATE OR DELETE ON 테이블명
FOR EACH ROW
BEGIN
    -- 실행할 SQL 로직
END;
/

트리거가 필요한 테이블 밑에 바로 작성할수도 있고 SQL 파일을 새로 만들어서 작성해도 된다 중요한점은 테이블 안에 만들면 안된다 트리거는 SQL로 생성하는 객체 이기때문이다

트리거 안에서 변경 전과 변경 후의 값을 볼수 있는데 가상컬럼인 :NEW와 :OLD를 사용한다

상태	설명
:OLD.column	UPDATE/DELETE에서 기존 데이터
:NEW.column	INSERT/UPDATE에서 새로운 데이터

 

:NEW.price   -- 새로 들어오는 가격
:OLD.price   -- 기존 저장되어 있던 가격

DELETE에는 NEW가 없고 INSERT에는 OLD가 없다

트리거 리스트 확인하기

SELECT trigger_name, table_name, status
FROM user_triggers;

트리거 삭제하기

DROP TRIGGER 트리거이름;

트리거가 필요한 경우는 컨트롤러나 서비스단에서 처리가할수 없는 데이터의보정이나 데이터의 무결성이 필요한경우에 사용할수 있다

데이터의 무결성이란 DB에 저장된 값들이 틀리지 않고 일관적으로 유지되는 상태를 말한다

우리 프로젝트에서 트리거가 필요한 이유는 주소테이블에 기본배송지가 단하나만 유지되도록 구분해야하기 때문이다

CREATE TABLE ADDRESS(
	ADDRESS_PK INT PRIMARY KEY,
	ACCOUNT_PK INT, --FK
	ADDRESS_NAME VARCHAR(50) NOT NULL,
	ADDRESS_IS_DEFAULT NUMBER(1) DEFAULT 0,
	--회원가입시 입력한 주소가 기본배송지 = 넘버(1)
	--배송지 추가시 추가된 주소는 일반주소지 = 디폴트 0 
	ADDRESS_POSTAL_CODE VARCHAR(20) NOT NULL,
	ADDRESS_LINE1 VARCHAR(100) NOT NULL,
	ADDRESS_LINE2 VARCHAR(200) NOT NULL,
	
	CONSTRAINT FK_ACCOUNT_ADDRESS FOREIGN KEY (ACCOUNT_PK) REFERENCES ACCOUNT(ACCOUNT_PK)
);

현재 작성되어 있는 테이블을 가져왔다 사용자가 회원가입시 주소지를 입력하면 그 주소가 기본주소지가 되고 주소지를 추가하게 되면 그 주소지는 일반주소지가 된다

이렇게 작성하면 컬럼만으로 기본배송지와/일반배송지의 구분이 가능하고 트리거가 없이도 기능구현이 가능하다 그러나

INSERT INTO ADDRESS VALUES (..., 1); 
INSERT INTO ADDRESS VALUES (..., 1);

INSERT시 기본배송지설정인 1을 두번쓰거나 코드를 누락하거나 하는 등의 실수가 발생하면 데이터의 무결성이 깨지게 된다

 따라서 기본배송지가 무조건 1개만 남도록 자동정리를 해주기 위해 트리거를 사용해야한다

CREATE OR REPLACE TRIGGER TRG_ADDRESS_DEFAULT
--트리거가 이미 있으며 덮어쓰기(수정) 없으면 트리거 새로만들기
BEFORE INSERT OR UPDATE ON ADDRESS
-- ADDRESS 테이블에 추가하거나 업데이트를 실행하기 직전에 작동된다
-- 즉 사용자가 새로운 주소를 추가하거나 기존 주소지를 수정할때 실행함
FOR EACH ROW
-- 추가/업데이트가 여러행에 영향을 줄수는 있는데
-- 해당영향을 받은 각 행별로 트리거가 한번씩 실행된다
WHEN(:NEW.ADDRESS_IS_DEFAULT = 1)
-- 새로들어오는 데이터(NEW)에서 기본주소지가 1일 경우에만 실행한다
-- 즉 사용자가 이번주소를 기본 배송지로 설정했을 때만 트리거가 실행된다
BEGIN
-- 비긴이후에 실제로 트리거가 실행할 코드를 넣는다
-- 같은 계정을 가진 기존주소들 중에서 이미 기본배송지(1)가 있다면
-- 값을 일반주소지(0)으로 바꾼다 

	UPDATE ADDRESS
	SET ADDRESS_IS_DEFAULT = 0
	WHERE ACCOUNT_PK = :NEW.ACCOUNT_PK
    -- 조건1 같은 계정
	AND ADDRESS_PK <> :NEW.ADDRESS_PK;
    -- 조건2 같은 계정으로 이번에 새로들어오는 주소는 제외(즉 자기자신제외)
    -- <> = 같지 않다를 의미
    -- 이번에 새로추가/업데이트 되는 주소를 제외하고 수정해라
    -- 제외안하면 다 일반주소지로 변경되서 트리거의 목적이 없어짐
    
    -- 결과 같은 회원의 기본 배송지가 존재해도 자동으로 0으로 변경됨

END;
/
-- /는 지금 작성한 PL/SQL 블록실행을 끝내겟다는 의미로 꼭 써야한다

트리거의 실행과정 

주소	조건 1 맞음?	조건 2 맞음?	최종 처리
주소1(기존 주소)	✔ (같은 계정)	✔ (ADDRESS_PK ≠ NEW) → 즉, 자신이 아님	0으로 변경됨
주소2(새 주소)	✔	✖ (ADDRESS_PK = NEW)	업데이트 대상에서 제외 → 계속 1 유지

결과적으로 새주소만 기본 배송지 유지하기 가 된다

기본배송지는 사용자가 선택하는 것이므로 새로 등록한 주소가 무조건 기본배송지가 되는건 아니고 사용자가 입력한 주소지중에  기본배송지를 선택하면 선택한 주소만 기본배송지가 되고 나머지 주소는 일반주소지가 된다 

입력 순서	사용자 입력 값 (ADDRESS_IS_DEFAULT)	트리거 동작	최종 기본배송지
1번째 주소	1 (기본배송지 선택)	기존 X → 그냥 저장	1번째 주소
2번째 주소	0 (일반주소로 입력)	트리거 실행 X	여전히 1번째 주소
3번째 주소	0 (일반주소로 입력)	트리거 실행 X	여전히 1번째 주소
4번째 주소	1 (사용자가 "이거 기본배송지로 변경" 선택)	기존 기본배송지(1번째 주소) → 0으로 변경	4번째 주소가 기본배송지

PL/SQL이란

오라클DB에서 SQL을 프로그래밍 처럼 사용할수 있게만든 언어다 SQL의 단점을 개선해서 나온것이며 SQL의 확장버젼이다

여러 SQL을 하나의 블록으로 구성해서 제어할수 있다

조건문,반복문,변수,예외처리,로직실행까지 가능하다

대량의 데이터를 처리하는데 사용할수 있으며 SQL 문으로 해결이 안될때 사용한다

기본 형태

DECLARE
    v_name VARCHAR2(30);  -- 변수 선언
BEGIN
    v_name := 'Hello PL/SQL'; -- 실행 로직
    DBMS_OUTPUT.PUT_LINE(v_name); -- 결과 출력
END;
/