trigger

트리거의 이용

---

트리거

특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램

문법

create trigger 트리거명 [before OR after] [insert OR update OR delete] on DML_실행되는_테이블명 [for each row]
begin
    실행할_쿼리_작성
end;

• before : DML 실행 전에 트리거 발동
• after : DML 실행 후에 트리거 발동

---

• insert : 만약 이 테이블에 인서트되면 해당 트리거 발동
• update : 만약 이 테이블이 업데이트되면
• delete : 삭제되면

---

• for each row : 한 레코드 당 한 번씩 발동

레코드 구조체

나는 mariaDB를 사용해서 프로젝트 구현 중이기에

• new.칼럼명 : 입력된 레코드에서 해당 칼럼의 데이터
• old.칼럼명 : 기존에 있던 레코드에서 칼럼의 데이터

(delete문에서는 new.칼럼명을, insert문에서는 old.칼럼명을 사용할 수 없다)

트리거에서 조건문 만들기

create trigger blind after update ON report FOR EACH ROW
-- blind란 트리거를 생성한다. 이 트리거는 report 테이블에 레코드 하나가 들어간 이후에 실행된다.
    begin
    -- 트리거 정의 시작
        if new.checkCode = 'PS02' then
        -- 만약 업데이트된 레코드의 checkCode 칼럼의 값이 'PS02'라면(if절 1번)
            if new.noteOrCommCode = 'NC00' then
            -- 만약 업데이트된 레코드의 noteOrCommCode 칼럼의 값이 'NC00'라면 (if절 2번)
                insert into notify(userEmail, notifyCode, notifyTarget, readCheck) values
                    ((select userEmail from note where noteNum=new.noteNum), 'RN', new.noteNum, 1)
                    -- 해당 SQL문을 실행한다.
                    on DUPLICATE KEY update readCheck=1;
                    -- 만약 이미 중복하는 레코드가 있다면 insert하지 않고 update하겠다.
                    -- readCheck라는 칼럼의 데이터를 1로
            else
            -- 업데이트된 레코드의 noteOrCommCode 칼럼의 값이 'NC00'이 아니라면
                insert into notify(userEmail, notifyCode, notifyTarget, readCheck) values
                    ((select userEmail from notecomm where noteCommNum=new.noteNum), 'RC',
                    (select noteNum from notecomm where noteCommNum=new.noteNum), 1)
                    on DUPLICATE KEY update readCheck=1;
            end if;
            -- if절 2번 종료
        end if;
        -- if절 1번 종료
    end;
    -- 트리거 정의 종료

on DUPLICATE KEY는 트리거의 문법이 아닌 일반 쿼리 문법

트리거로 루프 돌리기

create trigger QNAAdmin after insert on notify for each row
     begin
     DECLARE done INT DEFAULT FALSE;
     declare tempoUserEmail varchar;
     declare curUserEmail cursor for select userEmail from roles where roleCode='ROLE_ADMIN';
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

     if (select roleCode from roles where userEmail=new.userEmail) = 'ROLE_ADMIN' then
         open curUserEmail;
              ins_loop: LOOP
                  fetch curUserEmail into tempoUserEmail;
                  if done then
                      leave ins_loop;
                  end if;
                  insert into notify(userEmail, notifyCode, notifyTarget) values (tempoUserEmail, 'QNA', new.qnaNum);
              end loop;
         close curUserEmail;
     end if;
     end;

관리자가 여러 명일 수 있어서, 쿼리의 결과가 다중일 때 트리거 안에서 루프를 돌게끔 쿼리문을 짜보았다.
그런데 위 쿼리는 실제로 프로젝트에 사용은 못했다.
roles가 예약어로 잡혀있는 것 같다고 강사님이 그러셨다.
문법 자체는 오류가 없어보인다고 그러셔서, 나중에 또 내가 이런 뻘짓하고 싶어할까봐 같이 기록.

프로젝트에선 파라미터로 role이 관리자인 것들을 리스트 형태로 보낸다음, foreach로 돌렸다.