INSERT/UPDATE/DELETE 트리거 만들기

PROJECT 테이블에 변화가 일어나면

자동으로 PROJECT_MEMBER 테이에 갱신되는 트리거를 만들어보자

 

PROJECT 테이블

PROJECT_MEMBER 테이블

 

INSERT 트리거

CREATE TRIGGER PM_INSERT AFTER INSERT ON PROJECT
 FOR EACH ROW 
 BEGIN 
 INSERT INTO PROJECT_MEMBER(PROJECT_ID, USER_ID,GROUP_ID) VALUES(NEW.ID, NEW.MANAGER, 2);
 END;

 

UPDATE 트리거

CREATE TRIGGER PM_UPDATE AFTER UPDATE ON PROJECT
 FOR EACH ROW 
 BEGIN 
UPDATE PROJECT_MEMBER SET USER_ID=NEW.MANAGER WHERE PROJECT_ID=OLD.ID;
 END;

 

DELETE 트리거

CREATE TRIGGER PM_DELETE BEFORE DELETE ON PROJECT
 FOR EACH ROW 
 BEGIN 
DELETE FROM PROJECT_MEMBER WHERE PROJECT_ID=OLD.ID;
 END;

 

내가 만든 트리거 삭제하기

DROP TRIGGER IF EXISTS 트리거명;