[필기노트] 2021-07-20 SQL, TRIGGER

== 트리거 
SHOW TRIGGERS;
 - 현재 데이터베이스에 정의되어있는 트리거를 출력한다.

drop trigger if exists TR_Posts;
 - TR_Posts 트리거가 존재할 경우 삭제한다.

Sequelize.hook를 이용해 다른 TRIGGER를 실행할 수 있다.
 - TODO 다른 테이블을 Models폴더에서는 왜 참조가 안되는걸까?


//postId는 변경되지 않는다.
-- Posts 테이블 수정시 Images, Tags 데이터를 지우는 트리거

drop trigger if exists TR_Posts;  
DELIMITER $$  
CREATE TRIGGER TR_Posts  
AFTER UPDATE ON Posts  
FOR EACH ROW  
BEGIN  
    DELETE FROM Images WHERE postId = old.postId;  
    DELETE FROM Tags WHERE postId = old.postId;  
END   
$$  
DELIMITER ;

 - 데이터가 삽입되는 행을 기준으로 트리거가 실행된다.


-- Follows 테이블에 데이터 삽입시 Alarm테이블에 데이터가 생성되는 TRIGGER

drop trigger if exists TR_Follows_Alarm;  
DELIMITER $$  
CREATE TRIGGER TR_Follows_Alarm  
AFTER INSERT ON Follows  
FOR EACH ROW  
BEGIN  
    INSERT INTO Alarms (giverUserId, receiverUserId, type, createdAt, updatedAt) values  
        (NEW.followUserId, NEW.followerUserId, 1, NOW(), NOW() );  
END   
$$  
DELIMITER ;

 - 데이터가 생성된 행마다 트리거가 실행된다.
 - DELIMITER ; : 작성하지 않을 경우 끝맺는 단어가 ; 이 아니게 설정이 된다.

 

-- 리블로그 데이터가 삽입시, Alarm에 데이터가 삽입되는 트리거

1. 리블로그한 게시글의 userId를 확인

SELECT userId FROM Posts WHERE postId = 4  

  - 4 : reBlog의 인자 

2. 트리거를 작성한다.

drop trigger if exists TR_Posts_reBlog_Alarm;  
DELIMITER $$  
CREATE TRIGGER TR_Posts_reBlog_Alarm  
AFTER INSERT ON Posts  
FOR EACH ROW  
BEGIN  
    IF (NEW.reBlog IS NOT NULL) THEN   
        INSERT INTO Alarms (giverUserId, receiverUserId, type, createdAt, updatedAt) values  
            (NEW.userId, (SELECT userId FROM Posts WHERE postId = NEW.reBlog), 2, NOW(), NOW() );  
    END IF;  
END  
$$  
DELIMITER ;

-- Favorites 테이블에 데이터 삽입시 Alarm테이블에 데이터가 생성되는 TRIGGER

 

 

1. Posts를 작성한 사람의 아이디를 Sub Query로 가져와야 한다.

SELECT userId FROM Posts WHERE postId = 3  

2. Favorites에 데이터 삽입시 실행되는 TRIGGER 작성

drop trigger if exists TR_Favorites_Alarm;  
DELIMITER $$  
CREATE TRIGGER TR_Favorites_Alarm  
AFTER INSERT ON Favorites  
FOR EACH ROW  
BEGIN  
    INSERT INTO Alarms (giverUserId, receiverUserId, type, createdAt, updatedAt) values  
        (NEW.userId, (SELECT userId FROM Posts WHERE postId = NEW.postId), 3, NOW(), NOW() );  
END   
$$  
DELIMITER ;

 - 데이터가 생성된 행마다 트리거가 실행된다.
 - 자기자신을 reBlog하여도 알람이 생성되어야 하기 때문에 자기자신을 배제하지 않았다.

 

게시글 수정을 위해 해야하는 것
1. 게시글이 존재하는가?  
2. 게시글이 내 게시글이 맞는가  
3. 게시글을 수정한다.  
4. Images 테이블을 비운다.  
5. Images 작성한다.  
6. Tags 테이블을 비운다.  
7. Tags 작성한다.  
  
1 = 2. 합칠수 있음 findOne의 WHERE 절로 확인할 수 있음  
3. Posts.update 로 수정  
남은건 4개 \[4,5,6,7\]  
4, 6 생략 할 수 있음 / Posts의 데이터가 Update 되었을 때 Images랑 Tags 테이블을 비운다는 트리거 작성  
  
5, 7번만 남음  
그러면 총 DB 통신횟수를 7번에서 > 4번으로 줄인다.  
1=2 > 3 > 5 > 7



== 18장 트리거 
Trigger : 특정 사건이 발생했을 때 자동으로 호출되는 코드
 - 어떤 사건에 대해 자동으로 후속 조치를 하는 장치
 - 직접 호출할 수 없으며 일정한 조건이 되면 자동으로 호출된다.
 - 시스템이 호출하므로 인수를 전달할 수 없고 리턴값도 반환할 수 없다.
 - 수행 시점과 작업 규칙을 서버에 저장해 놓은 DB 오브젝트
 - 정의문에 적용 대상과 발생 시점에 대해 지정을 상세히 적는다
 - 트리거는 한 번 설치해 놓으면 테이블에 변화가 발생할 때마다 호출된다.
  - 즉, 너무 많은 트리거를 설치하면 서버에 상당한 무리를 준다.
 - 트리거는 인수를 받지 않는 대신 시스템이 자동으로 생성하는 임시 테이블을 통해 변화에 대한 정보를 제공한다.

CREATE OR REPLACE
 - 트리거를 생성하면 언제든지 본체를 편집할 수 있다.



Trigger의 삭제
 - DROP TRIGGER
 - 명시적으로 삭제하지 않더라도 트리거가 부착된 테이블을 삭제하면 같이 제거한다.

STATEMENT TRIGGER (문장 트리거)
 - SQL 명령 하나에 대해 한 번 호출 되는 트리거
 - 단순히 사건이 발생했음을 알리기만 한다

행 트리거
 - FOR EACH ROW : 매 레코드를 변경할 때마다 호출되는 트리거
 - 어떤 사건이 어떻게 발생했는지 상세한 정보를 제공한다

NEW : INSERT, UPDATE시 변경된 후의 레코드를 가진다.
OLD : DELETE, UPDATE시 삭제 또는 변경 전의 레코드를 가진다.
 - 이 둘은 트리거가 끝나면 자동으로 삭제된다.
 - 본체에서 참조할 때 앞에 :을 붙이며 레코드 변수처럼 :NEW. 필드 식으로 내부의 필드를 읽고 쓴다.
 - NEW, OLD를 통해 변경 전후의 레코드 상태를 알 수 있다.