[MySQL] Trigger

 

• Trigger

DML문이 수행되었을 때, 데이터베이스 내부에서 자동으로 동작하도록 작성된 프로그램
사용자가 직접 호출하는 것이 아니라, 데이터베이스에서 자동적으로 호출하는 것이 가장 큰 특징

SQL 표준으로 Row 레벨 트리거와, Statement 레벨 트리거가 존재
Row 레벨: Row의 insert, update, delete시에 발생
Statement 레벨: Row의 insert, update, delete개수와 발생과 관계없이 한 트랜잭션에 한번 호출

---

• MySQL Trigger

Row 레벨 트리거만 지원

한 DB에서 트리거의 이름은 Unique

---

특정 스키마에 존재하는 모든 트리거 확인 

SHOW TRIGGERS;

 

트리거 삭제

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;
/* 스키마 생략시 현재 스키마에 존재하는 트리거 DROP */

 

테이블 삭제시 해당 테이블과 관련된 모든 트리거들이 자동으로 제거된다.

CREATE TRIGGER 트리거1 .... ON 테이블1 .....;
CREATE TRIGGER 트리거2 ..... ON 테이블1 .....;
DROP TABLE 테이블1; //-> 트리거1,트리거2 자동으로 삭제 된다.

 

DML이 발생할 때 수행될 문이 하나라면 DELIMITER가 필요없지만, 수행될 문이 여러 문이면 DELIMITER를 임시 변경하고 BEGIN ... END을 사용해야 한다.

//수행될 문이 하나일 때 예시
CREATE TRIGGER before_employee_update 
    BEFORE UPDATE ON employees
    FOR EACH ROW 
 INSERT INTO employees_audit
 SET action = 'update',
     employeeNumber = OLD.employeeNumber,
     lastname = OLD.lastname,
     changedat = NOW();
     
     
//수행될 문이 여러개 일때 예시
DELIMITER $$
CREATE TRIGGER before_billing_update
    BEFORE UPDATE 
    ON billings FOR EACH ROW
BEGIN
    IF new.amount > old.amount * 10 THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'New amount cannot be 10 times 
            greater than the current amount.';
    END IF;
END$$    
DELIMITER ;

 

---

트리거에 설정된 대상 테이블의 변경전 컬럼 값은 OLD로, 변경후 컬럼 값은 NEW를 통해 사용가능할 수 있다.

 

출처: https://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx

 

INSERT로 설정된 트리거의 트리거 바디

기존 ROW는 없는 상태이기 때문에 OLD는 사용불가, NEW 사용가능

 

UPDATE로 설정된 트리거의 트리거 바디

업데이트 전 값은 OLD, 업데이트 후 값은 NEW로 사용가능

 

DELETE로 설정된 트리거의 트리거 바디

삭제 전 값은 OLD, 삭제 후에는 값이 없으므로 NEW 사용불가

---

• BEFORE [INSERT,UPDATE] 트리거는 INSERT,UPDATE발생 전에 채워질 컬럼 값을 변경할 수 있다는 점이 있다.

 

BEFORE INSERT 

you can access and change the NEW values.

However, you cannot access the OLD values because OLD values obviously do not exist.

 

AFTER INSERT

you can access the NEW values but you cannot change them. 
Also, you cannot access the OLD values because there is no OLD on INSERT triggers.

 

BEFORE UPDATE

you can update the NEW values but cannot update the OLD values.

 

AFTER UPDATE

you can access OLD and NEW rows but cannot update them.

 

BEFORE DELETE

you can access the OLD row but cannot update it. Also, there is no NEW row in the BEFORE DELETE trigger.

 

AFTER DELETE

you can access the OLD row but cannot change it.
Note that there is no NEW row in the AFTER DELETE trigger.

---

• 동일한 동작에 해당하는 여러 트리거에 대하여 트리거가 호출될 순서를 결정

DELIMITER $$

CREATE TRIGGER trigger_name
{BEFORE|AFTER}{INSERT|UPDATE|DELETE} 
ON table_name FOR EACH ROW 
{FOLLOWS|PRECEDES} existing_trigger_name
BEGIN
    -- statements
END$$

DELIMITER ;

FOLLOWS : existing_trigger_name 이후에 호출

PRECEDES : existing_trigger_name 이전에 호출

 

트리거 호출 순서 확인

SELECT 
    trigger_name, 
    action_order
FROM
    information_schema.triggers
WHERE
    trigger_schema = '스키마명'
ORDER BY 
    event_object_table , 
    action_timing , 
    event_manipulation;

---

https://www.mysqltutorial.org/mysql-triggers/

MySQL Triggers