[Udemy] Database Triggers

TheUltimate MySQL Bootcamp第18話整理の内容を学ぶ
Triggerとは、特定のテーブルの変更時に自動的に実行されるsql宣言です.
データが条件を満たす有効な値である場合、またはクエリを実行するAテーブルの変化をBテーブルに記録する場合、データを書き込むことができます.

syntax

CREATE TRIGGER trigger_name
  triger_time trigger_event
ON table_name 
FOR EACH ROW
  BEGING
  END;

trigger_timeBEFORE、AFTERで書くことができます.

trigger_eventINSERT、UPDATE、DELETEを作成できます.

DELIMITER $$(セパレータ:セパレータ)
以前にsql文の終了をプロンプトしたロールを$;に変更します.トリガは、1つのsqlブロックに;を複数回書き込むため、終了とみなされないように設定を一時的に変更します.

$$上記の設定に従って、sql文が終了したことを示します.

DELIMITER ; ;でsql文の終了を表すように再設定します.

NEWNEWという仮想変数NEWの使用カラム名とは、表の行が影響を受けるデータです.(New is just a placeholder. It is referring to data that is attempted to get inserted)

OLDOLDという名前の仮想変数OLDを使用します.コラム名とは、テーブル行の影響(update,delete)を受けるデータです.

SQLSTATESQLデータベースで使用される通常のエラーコードとして、エラーコードをSIGNALキーワードに設定できます.

MESSAGE_TEXTSETキーは、エラー発生時に表示されるテキストを設定できます.

Example1 age over 18

データの有効性条件に合致するデータのみを追加、変更します.条件に合致しない場合はエラーが発生します.

DELIMITER $$

CREATE TRIGGER must_be_adult
     BEFORE INSERT ON users FOR EACH ROW
     BEGIN
          IF NEW.age < 18
          THEN
              SIGNAL SQLSTATE '45000'
                    SET MESSAGE_TEXT = 'Must be an adult!';
          END IF;
     END;
$$

DELIMITER ;

Example2 preventing self-follow

followerのidとfollowee idが同じ場合、トリガがトリガーされます.

DELIMITER $$

CREATE TRIGGER prevent_self_follows
     BEFORE INSERT ON follows FOR EACH ROW
     BEGIN 
        IF NEW.follower_id = NEW.folowee_id
        THEN 
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'You cannot follow yourself!';
     END IF;
    END;
$$

DELIMITER ;

Example3 Logging Unfollows

ユーザーが他のユーザーに関心を持たない場合、unfollowsテーブルにデータを追加するトリガが発生します.

//follows와 이름만 다른 unfollows 테이블 생성
CREATE TABLE unfollows (
    follower_id INT NOT NULL,
    followee_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(follower_id) REFERENCES users(id),
    FOREIGN KEY(followee_id) REFERENCES users(id),
    PRIMARY KEY(follower_id, followee_id)
);

DELIMITER $$

CREATE TRIGGER capture_unfollow
     AFTER DELETE ON follows FOR EACH ROW
     BEGIN 
        //INSERT INTO unfollows (follow_id, followee_id) VALUES (OLD.follower_id, OLD.followee_id)
        INSERT INTO unfollows
        SET 
            follower_id = OLD.follower_id,
            followee_id = OLD.followee_id;
     END;
$$

DELIMITER ;

Managing Triggers

1回のsql文では、何百ものデータを追加、更新、削除できるため、デバッグが困難になる可能性があります.
また,フリップフロップを使わなくても実現できる方法があれば,フリップフロップを使わない.たとえば、3番目の例では、新しいテーブルにデータを挿入します.この場合、1回のリクエストで大量のデータが生成される場合があります.したがって、既存の下表にstatusコラムを追加し、activeとactiveに変更する方が効果的かもしれません.

SHOW triggers; //triggers 목록 확인하기
DROP TRIGGER trigger_name; //트리거 삭제하기