SQLiteを学んでいこうという連載の20回目です。前回の記事はこちらになります。
前回は、簡単なトリガー機能の例をご紹介しました。SQL文で特定のテーブルが更新された際に、別のSQL文を自動的に実行するという機能です。
今回は、前回ご紹介したトリガー機能を解説します。
データ追加時のトリガー
それでは、前回のトリガー機能設定のSQL文を再掲します。
CREATE TRIGGER insert_data
AFTER INSERT ON sample
FOR EACH ROW
BEGIN
INSERT INTO log_table(timestamp, id) VALUES(datetime('now'),NEW.id);
END;
トリガーの宣言
「CREATE TRIGGER トリガー名」でトリガー機能を宣言します。
動作時期と契機
次の行はトリガー機能の動作時期と契機を指定します。今回の例ではテーブル「sample」にデータが追加された後に実行します。 動作時期は「AFTER」以外に「BEFORE」（実行前）が指定可能です。 契機は「INSERT」以外に「DELETE」（削除）、「UPDATE」（更新）が指定可能です。 また、契機の後に「OF 列名」と記述すると対象となる列名を指定できます。
動作対象
続いて動作対象です。「FOR EACH ROW」は対象テーブル内データに対して変更が発生した時に、変更が発生した全行に対してSQLが動作します。 「FOR EACH STATEMENT」を指定すると、SQLの動作は一度だけになります。
SQLの指定
最後に「BEGIN」と「END」の間に、実行されるSQL文を記述します。ここでSQL文の中に「NEW.id」という記述が出てきますが、次で説明します。
更新前後の行の参照
トリガー機能で実行されるSQL文内に、対象テーブルの変更前後の行を参照できます。「NEW.列名」と指定すると変更後の行の列データを参照できます（「INSERT」、「UPDATE」で使用可能）。「OLD.列名」と指定すると変更前の行の列データを参照できます（「UPDATE」、「DELETE」で使用可能）。
データ削除の記録を取る
以下のテーブルを作成します。
sqlite> CREATE TABLE sample(id INTEGER,name TEXT,age INTEGER); sqlite> INSERT INTO sample VALUES(1,"TANAKA",25); sqlite> INSERT INTO sample VALUES(2,"YAMADA",30); sqlite> INSERT INTO sample VALUES(3,"OOTA",27);
また、記録を取るためのテーブルも用意します。
sqlite> CREATE TABLE delete_log(timestamp TEXT,id INTEGER,name TEXT,age INTEGER);
次にトリガー機能を設定します。
sqlite> CREATE TRIGGER delete_data
...> AFTER DELETE ON sample
...> FOR EACH ROW
...> BEGIN
...> INSERT INTO delete_log VALUES(datetime('now'),OLD.id,OLD.name,OLD.age);
...> END;
次に、以下のSQL文を実行してレコードを1つ削除してみましょう。
sqlite> DELETE FROM sample WHERE id = 2;
テーブル「sample」の内容は以下の様なりました。
sqlite> .mode qbox sqlite> SELECT * FROM sample; ┌────┬──────────┬─────┐ │ id │ name │ age │ ├────┼──────────┼─────┤ │ 1 │ 'TANAKA' │ 25 │ │ 3 │ 'OOTA' │ 27 │ └────┴──────────┴─────┘
そしてテーブル「delete_log」の内容を確認してみましょう。
sqlite> SELECT * FROM delete_log; ┌───────────────────────┬────┬──────────┬─────┐ │ timestamp │ id │ name │ age │ ├───────────────────────┼────┼──────────┼─────┤ │ '2023-07-31 03:43:43' │ 2 │ 'YAMADA' │ 30 │ └───────────────────────┴────┴──────────┴─────┘
削除されたレコードが記録されています。
次回はPython
いかがでしょうか。トリガー機能をうまく利用できれば、信頼性の高いシステムが構築できそうです。次回はPythonです。この連載の初回でもお話ししましたが、この連載はAnaconda経由でインストールされたSQLiteを中心に書いています。そして、SQLiteはPythonから呼び出して使うことができます。次回はPythonからSQLiteを使用する方法をご紹介します。お楽しみに！！
