[DB] Mysql 트리거

Trigger

ECA(Event-Condition-Action) rule 을 따르는 객체이며, 사건 발생 전(후) 조건 만족 시 Action 을 취한다는 뜻

Event : Insert, delete, update

insert 혹은 update 시 “referencing new row as A” 에서 new row, 즉 변경된 새로운 row 를 A로 부르겠다. 라는 뜻

delete 혹은 update 시 “referencing old row as B” 에서 old row, 즉 변경전의 기존 row 를 B로 부르겠다, 라는 뜻

 

Event 발생 전 : Before

Event 발생 후 : After

 

조건 만족 : When <predicate>

행동 : Begin atomic <statement> End;

CREATE TRIGGER myCredits AFTER UPDATE ON takes
REFERENCING NEW ROW AS nrow
REFERENCING OLD ROW AS orow
FOR EACH ROW
WHEN nrow.grade <> ‘F’ AND nrow.grade IS NOT NULL
	AND (orow.grade = ‘F’ OR orow.grade is NULL)
BEGIN ATOMIC
  UPDATE student
  SET totalcredit = totalCredit + (SELECT credit FROM course WHERE cID = nrow.cID)
  WHERE sID = nrow.sID;
END;

Statement level trigger

위 예시의 경우 FOR EACH ROW 로 인해 row 단위의 trigger 임

즉 100개의 row 가 insert 된 상황에서는 100번 수행되는 trigger

 

For each statement 키워드를 사용하게 된다면 statement, table 단위의 trigger 를 만들 수 있음

row 와 마찬가지로 “referencing new(old) table as” 사용 가능

한 번에 많은 row 가 변경될 때 효율적임

CREATE TRIGGER myTotalSalary AFTER UPDATE OF salary ON employee
REFERENCING OLD TABLE AS O
REFERENCING NEW TABLE AS N
FOR EACH STATEMENT
WHEN EXISTS (SELECT * FROM N WHERE N.dno IS NOT NULL) 
	OR EXISTS(SELECT * FROM O WHERE O.dno IS NOT NULL)
UPDATE department as D
SET D.totalSalary = D.totalSalary + (SELECT SUM(N.salary) FROM N WHERE D.dno = N.dno)
	- (SELECT SUM(O.salary) FROM O WHERE D.dno = O.dno)
WHERE dno is ((SELECT dno FROM N) UNION (SELECT dno FROM O));

과거에는 요약데이터(summary data) 를 관리하거나 데이터의 복사본을 유지할 때 Trigger 를 많이 사용했는데 요즘에는 materialized view (실체화된 뷰)를 통해 요약 데이터를 관리하고, DB 자체적으로 내장 복제(Replication)을 지원함

 

트리거는 무한 루프를 만들어낼 수 있으므로 사용시에 항상 주의해야함

(A가 수정되면 B를 수정하는 트리거 T1, B가 수정되면 A를 수정하는 트리거 T2 가 동시에 존재하면 이 둘이 서로에게 영향을 줌)