どもです。

あるDBに登録された電話番号が、ハイフンがあったりなかったりします。
これを検索するとき、ハイフンを問わず検索できるようにしたい。

SELECT * FROM tableA WHERE REPLACE(`tel`, '-', '') = '[検索語句]'

一見するとこれで問題ないですが、もしtableAに10万件のレコードが入っていたら…
検索の度に10万件のtelにREPLACEをかけることになります。当然、INDEXも効きません。ゾッとしますね。

そこで、レコードの登録時に予めハイフンを除去したデータを保持することにしました。
tableA.tel自体をハイフン禁止にすれば楽ですが、それは仕様上できないとのことで、検索用データを保存したtableBを作成。

では、このtableBにどうやってハイフンを除去したtableA.telを保存するか、ですが、トリガーというものを登録します。
トリガーとは、まあJSとかでよく見るやつです。特定の条件を満たしたとき、自動で動く処理。

DELIMITER |
CREATE TRIGGER [適当なトリガー名] AFTER INSERT ON tableA FOR EACH ROW
BEGIN 
REPLACE INTO tableB SET 
id  = NEW.id,
tel = REPLACE(NEW.tel, '-', '');
END;
|
DELIMITER ;

INSERT を UPDATE にすれば、UPDATEをトリガーにしてtableBへのREPLACEが着火します。
2つ用意しないとなのが面倒ですね。1トリガーでUPDATEとINSERTの両方させてくれりゃいいのに。

BEGIN ～ END; の間に記載した内容が、トリガー着火で実行されるイベントです。
普通のREPLACEと異なるのは、更新されたtableAの値を NEW.○○ という形で表記することですね。

DELIMITERって要るん？？となるかもですが、登録するときには微妙に要ります。たぶん。

さて、このままだとtableAからレコードが消えても、tableBに電話番号が残ってしまいます。
それは宜しくないので、tableAのDELETEにもトリガーを設置します。

DELIMITER |
CREATE TRIGGER [適当なトリガー名] BEFORE DELETE ON tableA FOR EACH ROW
BEGIN 
DELETE FROM tableB WHERE id = OLD.id;
END;
|
DELIMITER ;

ミソになるのが、トリガ名の後ろ。AFTERからBEFOREに。カラムの表記がNEW.○○からOLD.○○なりました。

これは、AFTERが「更新後のtableA」、BEFOREが「更新前のtableA」を指し、NEW・OLDはそれぞれAFTER・BEFOREのときの値を指すからです。
INSERTやUPDATEは、大体のケースでは更新後の値を参照しますが、DELETEの後では参照できる値がありません。必ずBEFOREを拾います。

また、DELETEトリガーの注意点ですが、DELETE TABLE や TRUNCATE TABLE では着火しません。
そういった処理を行う場合は、tableBも一掃するよう実行側で調整が必要です。