[PostgreSQL] Trigger + Transaction

 

🎯 Goals

• 트리거 , 트랜잭션 내에서 언제 실행될까?

3가지 타입의 트리거

1. BEFORE
2. ✅ AFTER (Only handle this in this post)
3. INSTEAD OF

각 트리거 타입에 따라 Default Behavior 가 다르므로 꼭 공식문서를 참조하길 바란다. 이 글에선 `AFTER` 트리거만 다룬다.

 

 

아래 SQL문을 보고 실행 결과를 예상해보자.

과연 여기서 어떤 순서로 로그가 찍힐까?

 

Each statement? vs After all transaction `COMMIT()`?

당연히 나는 후자로 알고있었다, 트랜잭션의 모든 Statement 가 끝난 후에 `COMMIT()`  된 시점에 트리거가 발동할 줄 알았다.

예상한 로그는 다음과 같다.

 

 

❌ 틀렸다, 각 문장마다 곧바로 트리거가 실행됐다.

 

 

공식 문서 훔쳐보기

Row-level `BEFORE` triggers fire immediately before a particular row is operated on,
while row-level `AFTER` triggers fire at the end of the statement (but before any statement-level 
`AFTER` triggers). 

The execution of an `AFTER` trigger can be deferred to the end of the transaction, rather than the end of the statement, if it was defined as a constraint trigger.
In all cases, a trigger is executed as part of the same transaction as the statement that triggered it, so if either the statement or the trigger causes an error, the effects of both will be rolled back.

 

📝 트리거는 기본적으로 Statement 가 끝나는 시점에 실행되지만, `Constraint` 를 걸면, AFTER 트리거의 실행 시점을 트랜잭션 종료시로 미룰 수 있다.

 

 

Constraint Trigger 로 지연 실행하기

결론은 매우 간단하다, 트리거 생성시 `CONSTRAINT` 키워드와 `INITALLY DEFERRED` 옵션만 붙여주면 된다.

⚠️ CONSTRAINT TRIGGER 는 오로지 `AFETR` 트리거에 FOR EACH ROW 레벨에서만 사용 가능하다.

 

이와 관련된 두가지 옵션을 모두 알고있어야 적절한 용도로 쓸 수 있다.

[DEFERRABLE]
(중략..)
A constraint that is not deferrable will be checked immediately after every command.
Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable.

[INITIALLY DEFERRED]
If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is 
INITIALLY IMMEDIATE, it is checked after each statement. This is the default.
If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS command.

 

 

영문 그대로 직역했을 때는 용도와 동작 방식의 차이가 아리송하다.

⚠️ DEFERRABLE 옵션과
INITIALLY DEFERRED 옵션은 다르다.

DEFERRABLE

👉🏻 Unique, PK, EXCLUDE, FK 제약조건 검사를 트랜잭션 종료 시점까지  지연시킨다.

(Default:  NOT DEFERRABLE, (SQL 문장 단위로 제약조건 검증))

 

INITIALLY DEFERRED

👉🏻 모든 문장의 제약 조건 검증을 트랜잭션 종료 시점까지 지연시킨다.

(Default: INITIALLY IMMEDIATE)

 

 

⚠️ 위 예제에서 `DEFFERABLE' 만 설정하면 이전 예제와 같은 결과를 얻는다.
(SQL 1번 -> 트리거 발동-> SQL 2번 -> 트리거 발동)

 

 

 

 

 

---

🤔 이게 뭔 뜻이지? (추후 분석)

 In all cases, a trigger is executed as part of the same transaction as the statement that triggered it, so if either the statement or the trigger causes an error, the effects of both will be rolled back.

문장이 트리거 시킴으로써, 트리거는 트랜잭션의 일부로 실행된다. 그러므로 문장이나 트리거가 에러가 발생하면, 양쪽다 롤백된다.

직역은 이러한데 와닿지 않는다. 정확한 예시가 필요하다.

 

 

 

 

 

 

---

🔗 Reference

SQL Server Triggers and Transactions – Gavin Draper

CREATE CONSTRAINT TRIGGER

CREATE TABLE

 

Executing a trigger AFTER the completion of a transaction