SQLiteでトリガー（INSERT、UPDATE、DELETE）を作成する方法について記載します。
目次
1. INSERTトリガー
INSERTが行われた際に実行するトリガーです。
INSERTトリガーでは、NEW.列名 と指定することで、インサートする値を参照することができます。
構文
CREATE TRIGGER トリガー名 AFTER（BEFORE） INSERT ON テーブル名
BEGIN
実行するSQL文;
END;
BEGIN
実行するSQL文;
END;
トリガーの実行タイミングは、BEFORE とAFTER から指定します。
BEFORE：INSERT実行前にトリガーの処理を行います。
AFTER：INSERT実行後にトリガーの処理を行います。
実行例
商品テーブル（product）と 在庫テーブル（product_stock）がある場合に、在庫テーブルにインサートされたタイミングで、商品テーブルの在庫数をインクリメントするトリガーを作成します。
商品テーブル
|
1 2 3 4 5 |
CREATE TABLE product( id INTEGER PRIMARY KEY, name TEXT, count INTEGER ); |
在庫テーブル
|
1 2 3 4 5 |
CREATE TABLE product_stock( id INTEGER PRIMARY KEY, product_id INTEGER, FOREIGN KEY(product_id) REFERENCES product(id) ); |
トリガー（商品テーブルの在庫（count）をインクリメントする）
|
1 2 3 4 |
CREATE TRIGGER tri_countup AFTER INSERT ON product_stock BEGIN UPDATE product SET count = count + 1 WHERE id = NEW.product_id; END; |
実行結果
|
1 2 3 4 5 6 7 8 9 10 11 |
// 商品テーブルにインサート sqlite> INSERT INTO product ( id, name, count ) values ( 1,"商品A", 0 ); sqlite> INSERT INTO product ( id, name, count ) values ( 2,"商品B", 0 ); // 在庫テーブルに商品Aのデータをインサート sqlite> INSERT INTO product_stock ( id, product_id ) values ( 1, 1 ); // 商品Aのみ在庫（count）がインクリメントされている。 sqlite> SELECT * FROM product; id name count ---------- ---------- ---------- 1 商品A 1 2 商品B 0 |
2. UPDATEトリガー（更新列指定なし）
UPDATEが行われた際に実行するトリガーです。
UPDATEトリガーでは、UPDATE前後の値を OLD.列名、NEW.列名 として参照することができます。
構文
CREATE TRIGGER トリガー名 AFTER（BEFORE）UPDATE ON テーブル名
BEGIN
実行するSQL文;
END;
BEGIN
実行するSQL文;
END;
実行例
商品テーブル（product）が更新される度に、商品履歴テーブル（product_history）に更新前の値をインサートするトリガーを作成します。
商品テーブル
|
1 2 3 4 5 |
CREATE TABLE product ( id INTEGER PRIMARY KEY, name TEXT, price INTEGER ); |
商品履歴テーブル
|
1 2 3 4 5 6 7 |
CREATE TABLE product_history ( product_id INTEGER, name TEXT, price INTEGER, crate_date TEXT, FOREIGEN KEY product_id REFERENCES product(id) ); |
トリガー（商品テーブルが更新されると、商品履歴テーブルに古い内容を登録）
|
1 2 3 4 5 |
CREATE TRIGGER tri_insert_product_history AFTER UPDATE ON product BEGIN INSERT INTO product_history( product_id, name, price, create_date ) VALUES ( OLD.id, OLD.name, OLD.price, strftime('%Y-%m-%d %H:%M:%S','now') ); END; |
実行結果
|
1 2 3 4 5 6 7 8 9 10 |
// 商品テーブルに商品A・Bを登録 sqlite> INSERT INTO product (id,name,price) VALUES (1, '商品A', 1000); sqlite> INSERT INTO product (id,name,price) VALUES (2, '商品B', 500); // 商品テーブルを更新 sqlite> UPDATE product SET name = '商品AA' WHERE id = 1; // 商品履歴テーブルに変更前の値が登録 sqlite> SELECT * FROM product_history; product_id name price create_date ---------- ---------- ---------- ------------------- 1 商品A 1000 2020-03-23 15:54:31 |
3. UPDATEトリガー（更新列指定あり）
UPDATEトリガーでは、”特定の列が更新された” という条件を指定することも出来ます。
構文
CREATE TRIGGER トリガー名 AFTER（BEFORE） UPDATE OF カラム名1, ・・・ON テーブル名
BEGIN
実行するSQL;
END;
BEGIN
実行するSQL;
END;
特定の列は、OF の後にカラムを指定します。対象カラムはカンマ区切りで複数指定することも出来ます。
実行例
商品テーブル（product）の price列が更新された場合のみ、価格履歴テーブル（price_history）にインサートするトリガーを作成します。
商品テーブル
|
1 2 3 4 5 |
CREATE TABLE product ( id INTEGER PRIMARY KEY, name TEXT, price INTEGER ); |
価格履歴テーブル
|
1 2 3 4 5 6 |
CREATE TABLE price_history ( product_id INTEGER, price INTEGER, create_date TEXT, FOREIGN KEY (product_id) REFERENCES product(id) ); |
トリガー（商品テーブルのprice列が更新された場合、価格履歴テーブルに古いデータをインサート）
|
1 2 3 4 5 |
CREATE TRIGGER tri_insert_price_history AFTER UPDATE OF price ON product BEGIN INSERT INTO price_history( product_id, price, create_date ) VALUES ( OLD.id, OLD.price, strftime('%Y-%m-%d %H:%M:%S','now') ); END; |
実行結果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
// 商品テーブルに商品A・Bをインサート sqlite> INSERT INTO product (id,name,price) VALUES (1, '商品A', 1000); sqlite> INSERT INTO product (id,name,price) VALUES (2, '商品B', 500); // 商品Aの価格を更新 sqlite> UPDATE product SET price = '1100' WHERE id = 1; // 価格履歴テーブルに古い価格が登録される sqlite> SELECT * FROM price_history; product_id price create_date ---------- ---------- ------------------- 1 1000 2020-03-23 16:08:12 // 商品Aの名前を更新 sqlite> UPDATE product SET name = '商品AA' WHERE id = 1; // 価格履歴テーブルは更新されていない sqlite> SELECT * FROM price_history; product_id price create_date ---------- ---------- ------------------- 1 1000 2020-03-23 16:08:12 // 商品Aの名前は、商品AAに更新されている sqlite> SELECT * FROM product; id name price ---------- ---------- ---------- 1 商品AA 1100 2 商品B 500 |
4. DELETEトリガー
DELETEが行われた際に実行するトリガーです。
DELETEトリガーでは、DELETEする行の列値を OLD.列名 として参照することができます。
構文
CREATE TRIGGER トリガー名 AFTER（BEFORE）DELETE ON テーブル名
BEGIN
実行するSQL文;
END;
BEGIN
実行するSQL文;
END;
実行例
商品テーブル（product）の データが削除された際に、同データを削除商品テーブル（deleted_product）にインサートするトリガーを作成します。
商品テーブル
|
1 2 3 4 5 |
CREATE TABLE product ( id INTEGER PRIMARY KEY, name TEXT, price INTEGER ); |
削除商品テーブル
|
1 2 3 4 5 |
CREATE TABLE deleted_product ( id INTEGER PRIMARY KEY, name TEXT, price INTEGER ); |
トリガー（商品を削除すると、削除商品テーブルに同データを挿入）
|
1 2 3 4 |
CREATE TRIGGER tri_insert_delete_product AFTER DELETE ON product BEGIN INSERT INTO deleted_product(id, name, price) VALUES ( OLD.id, OLD.name, OLD.price ); END; |
実行結果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
// 商品テーブルに商品A・Bをインサート sqlite> INSERT INTO product (id,name,price) VALUES (1, '商品A', 1000); sqlite> INSERT INTO product (id,name,price) VALUES (2, '商品B', 500); // 商品テーブルから商品Aを削除 sqlite> DELETE FROM product WHERE id = 1; // 商品テーブルに商品Bのみ残っている sqlite> SELECT * FROM product; id name price ---------- ---------- ---------- 2 商品B 500 // 削除商品テーブルに商品Aがインサートされている sqlite> SELECT * FROM deleted_product; id name price ---------- ---------- ---------- 1 商品A 1000 |
5. トリガーに実行条件を指定する
INSERT・UPDATE・DELETE の各トリガーには、条件に一致した場合のみ、トリガーを実行させることが出来ます。
条件は テーブル名の後ろにWHEN句を使用して指定します。
構文
CREATE TRIGGER ・・・ON テーブル名 WHEN 条件
BEGIN
実行するSQL
END;
BEGIN
実行するSQL
END;
実行例
商品テーブル（product）が更新された場合、price列の値が 1000 以上の場合は、price列の値を 1.1倍 するトリガーを作成します。
商品テーブル
|
1 2 3 4 5 |
CREATE TABLE product ( id INTEGER PRIMARY KEY, name TEXT, price INTEGER ); |
トリガー（price列が1000以上の場合、price列を1.1倍する）
|
1 2 3 4 |
CREATE TRIGGER tri_upd_price AFTER UPDATE ON product WHEN OLD.price >= 1000 BEGIN UPDATE product SET price = price * 1.1 WHERE id = OLD.id; END; |
実行結果
|
1 2 3 4 5 6 7 8 9 10 11 12 |
// 商品テーブルに商品A・Bをインサート sqlite> INSERT INTO product (id,name,price) VALUES (1, '商品A', 1000); sqlite> INSERT INTO product (id,name,price) VALUES (2, '商品B', 500); // 商品A・Bの商品名を更新 sqlite> UPDATE product SET name = '商品AA' WHERE id = 1; sqlite> UPDATE product SET name = '商品BB' WHERE id = 2; // 商品AAのpriceのみ1.1倍されている sqlite> SELECT * FROM product; id name price ---------- ---------- ---------- 1 商品AA 1100 2 商品BB 500 |
この例では、WHEN句の条件は更新前のpriceの値が1000以上の場合という条件にしました。
|
1 |
WHEN OLD.price >= 1000 |
これ以外にも、列の値が NULL の場合は次のように指定します。
|
1 |
WHEN OLD.price IS NULL |