12 Trigger

SQL에서 Trigger(트리거)는 데이터베이스에서 특정 이벤트가 발생할 때 자동으로 실행되는 일련의 SQL 문을 포함하는 데이터베이스 객체입니다. 이 이벤트는 특정 테이블에 대한 데이터 변경 작업(INSERT, UPDATE, DELETE 등)들이 대표적인 예시입니다. 

 

 

BEFORE Trigger

특정 이벤트가 발생하기 전에 실행되는 Trigger로, 주로 데이터의 유효성을 검사하거나 변경 전에 추가 작업을 수행하는 데 사용됩니다.

 

1. DELIMITER $$는 세미콜론 대신 $$로 구문 분리자를 변경하는 것을 의미합니다. 트리거의 본문은 여러 SQL 문을 포함할 수 있기 때문에 이렇게 구문 분리자를 변경해주는 것이 필요합니다.
2. CREATE TRIGGER 문은 트리거를 생성하는데 사용됩니다. 
3. BEFORE UPDATE: 이 트리거는 UPDATE 작업이 수행되기 전에 실행됩니다.
4. ON user: 이 트리거는 user 테이블에 대해 동작합니다.
5. FOR EACH ROW: 트리거는 각 레코드마다 실행됩니다.
6. BEGIN ~ END: 트리거의 본문을 감싸는 데 사용되는 블록입니다.
7. INSERT INTO users_log (id, name, date) VALUES (OLD.id, OLD.name, NOW()); : 트리거에서 실행될 SQL 문으로, users_log 테이블에 변경 전(OLD)의 데이터를 삽입합니다.
8. $$: 트리거의 본문이 끝나면 $$로 설정한 구문 분리자를 사용하여 끝을 표시합니다.

user 테이블에 유저 이름을 업데이트 하면 users_log 테이블에 기존의 데이터가 저장되는 트리

 

 

---

 

AFTER TRIGGER

특정 이벤트가 발생한 후에 실행되는 Trigger로, 일반적으로 데이터 변경 후에 추가 작업이나 로깅 작업을 수행하는 데 사용됩니다.

1. AFTER INSERT ON buy FOR EACH ROW: 이 트리거는 buy 테이블에 행이 삽입된 후에 실행되며, 각 행에 대해 별도로 실행됩니다.
2. BEGIN ~ END: 이 부분은 트리거의 본문을 감싸는 블록입니다. 이 블록 안에 트리거의 실행 로직이 들어갑니다.
3. DECLARE total INT;: total이라는 이름의 정수 변수를 선언합니다. 이 변수는 총 구매 가격을 저장하는데 사용됩니다.
4. DECLARE user_id INT DEFAULT NEW.user_id;: user_id라는 이름의 정수 변수를 선언하고, 이 변수에는 현재 트리거에서 삽입되는 행의 user_id 값을 기본값으로 설정합니다.
5. SELECT sum(price) INTO total FROM buy WHERE user_id = user_id;: buy 테이블에서 현재 user_id에 해당하는 모든 행의 price 값을 합산하여 total 변수에 저장합니다.
6. UPDATE user_buy_stats SET price_sum = total WHERE user_id = user_id;: user_buy_stats 테이블에서 현재 user_id에 해당하는 행의 price_sum 값을 위에서 계산한 total 값으로 업데이트합니다.

 

 

---

 

FOR EACH ROW와 FOR EACH STATEMENT 차이 

FOR EACH ROW

• 각각의 테이블 레코드에 대해 트리거가 실행되도록 지정합니다. (Ex, 직원이 5명 있는 테이블이면 5번 동작)
• 주로 데이터 변경 이벤트(INSERT, UPDATE, DELETE)에서 사용되며, 테이블의 각 레코드에 대한 동작을 정의합니다
• 특정 레코드가 업데이트되기 전이나 후에 실행되는 로직을 정의할 수 있습니다.

FOR EACH STATEMENT

• FOR EACH STATEMENT는 트리거가 특정 SQL 문(문장)이 실행될 때 한 번만 실행되도록 지정합니다.
• 데이터베이스 수준의 이벤트나 여러 레코드를 대상으로 하는 일반적인 작업에서 사용됩니다.
• 예여러 레코드를 대상으로 하는 통계 계산 등의 작업을 정의할 수 있습니다.

 

 

---

 

TRIGGER 주의사항    

1. 성능 영향: 트리거는 데이터베이스 성능에 영향을 미칠 수 있습니다. 특히 대규모 트랜잭션에서는 성능 저하가 발생할 수 있으므로 트리거의 복잡성을 최소화하고 효율적인 로직을 사용해야 합니다.
2. 무한 루프 피하기: 트리거에서 다른 트리거를 호출하거나, 같은 트리거가 자신을 호출하는 경우 무한 루프에 빠질 수 있습니다. 이를 방지하기 위해 트리거의 로직을 신중하게 작성해야 합니다.
3. 트랜잭션 관리: 트리거는 트랜잭션 내에서 동작하며, 롤백 시에 트리거에서 수행한 작업도 롤백됩니다. 트리거의 동작이 트랜잭션과 일치하도록 주의해야 합니다.
4. 보안 고려: 트리거가 실행되는 권한을 확인하고, 트리거가 수행하는 작업이 보안 정책을 준수하는지 확인해야 합니다.
   데이터 일관성 유지:
5. 트리거는 데이터의 일관성을 유지하는데 사용되므로, 잘못된 트리거 로직은 데이터베이스의 일관성을 깨뜨릴 수 있습니다. 신중한 로직 설계가 필요합니다.

 

Trigger는 데이터베이스 시스템에 따라 문법과 동작이 조금씩 다를 수 있습니다. 그러나 대부분의 관계형 데이터베이스 시스템에서 지원하고 있으며, 효과적으로 사용될 때 데이터베이스의 일관성과 무결성을 강화하는 데 도움이 됩니다.

 

 

 

---

A trigger is an object in a database that automatically executes a series of SQL statements when a specific event (such as INSERT, UPDATE, DELETE) occurs. Triggers are commonly used to maintain data integrity and perform additional actions in response to data changes.