SQLiteのCREATE TRIGGERについて、INSERTしても一部しか反映されません。

Question

実現したいこと

取引テーブルと口座テーブルを作成し、取引テーブルに内容を追加した際にトリガーで口座テーブルにも自動で入出金額が追記されるような機能を実装したいのですが、トリガーがうまく動かなくて詰まってしまいました。

SQLite初心者なので基本的な部分が抜けているかもしれませんが、解決方法などわかるようでしたら教えてもらえるとありがたいです。

前提

• macos monterey 12.5.1
• sqlite3 3.37.0

発生している問題

ターミナルからSQLiteを起動し、SQLを実行しても口座テーブルに１つのレコードしか追加されませんでした。

本来であれば３つのレコードが追加されることを想定しています。

口座テーブル（Account_Log）の出力結果は以下となります。

sqlite> .read test.sql
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM Account_Log ;
ID_AL  Type  Time                 Account     Amount  TP_ID
-----  ----  -------------------  ----------  ------  -----
1      uj    2023-04-23 07:18:16  account01  1597.5  3    

該当のソースコード（test.sql）

CREATE TABLE Trading_Log (
    'ID_TL' INTEGER PRIMARY KEY,
    'Type' TEXT,
    'Time' TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    'LS' TEXT NOT NULL, 
    'Lot' REAL NOT NULL,
    'Price' REAL NOT NULL,
    'TP_ID' INTEGER, -- Take Profit
);

CREATE TABLE Account_Log (
    'ID_AL' INTEGER PRIMARY KEY,
    'Type' TEXT,
    'Time' TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    'Account' TEXT,
    'Amount' INTEGER,
    'TP_ID' INTEGER 
);

CREATE VIEW Trading_Result AS 
    SELECT
        out_rec.TP_ID,
        in_rec.Type,
        DATETIME(in_rec.Time, 'localtime') AS 'Time',
        in_rec.LS AS 'IN_LS',
        in_rec.Lot AS 'IN_Lot',
        out_rec.Lot AS 'OUT_Lot',
        in_rec.Price AS 'IN_Price',
        out_rec.Price AS 'OUT_Price',
        CASE
            WHEN in_rec.LS = 'Long' THEN ROUND(out_rec.Price - in_rec.Price, 3) * out_rec.Lot * 10000
            WHEN in_rec.LS = 'Sort' THEN ROUND(in_rec.Price - out_rec.Price, 3) * out_rec.Lot * 10000
        END AS 'Profit',
    FROM Trading_Log in_rec
    INNER JOIN Trading_Log out_rec
    ON in_rec.ID_TL = out_rec.TP_ID;

CREATE TRIGGER trg_TLtoAL INSERT ON Trading_Log
    WHEN NEW.TP_ID IS NOT NULL
    BEGIN
        INSERT INTO Account_Log(Account, Amount, TP_ID)
            SELECT
                'Account01',
                Profit,
                NEW.TP_ID
            FROM Trading_Result vw_tr WHERE vw_tr.TP_ID = NEW.TP_ID;
    END;

INSERT INTO Trading_Log('Type', LS, Lot, Price, TP_ID) VALUES('uj', 'Long', 1.5, 133.981, NULL);
INSERT INTO Trading_Log('Type', LS, Lot, Price, TP_ID) VALUES('uj', 'Short', 1.5, 134.519, 1);
INSERT INTO Trading_Log('Type', LS, Lot, Price, TP_ID) VALUES('uj', 'Long', 1.5, 134.718, NULL);
INSERT INTO Trading_Log('Type', LS, Lot, Price, TP_ID) VALUES('uj', 'Short', 0.75, 134.931, 3);
INSERT INTO Trading_Log('Type', LS, Lot, Price, TP_ID) VALUES('uj', 'Short', 0.75, 135.277, 3);

試したこと

あれこれ試してみましたが解決できず、アドバイスなどでもあれば教えてもらえると幸いです。
よろしくお願いいたします。

Comments

[ishiha]

追記です。
記事内の処理をした後に、再度2番目のINSERTを実行するとトリガーは発動して口座サーバーに追記されます。
```sql
sqlite> INSERT INTO Trading_Log('Type', LS, Lot, Price, TP_ID) VALUES('uj', 'Short', 1.5, 134.519, 1);
sqlite> SELECT * FROM Account_Log ;
ID_AL Type Time Account Amount TP_ID
----- ---- ------------------- ---------- ------ -----
1 uj 2023-04-23 07:18:16 fx_oanda01 1597.5 3
2 uj 2023-04-23 09:17:55 fx_oanda01 8070.0 1
sqlite>
```
ただ、取引テーブルのレコードが重複してしまうので最初の一回で入力させたいです。

```sql
sqlite> SELECT * FROM Trading_Log ;
ID_TL Type Time LS Lot Price TP_ID
----- ---- ------------------- ----- ---- ------- -----
1 uj 2023-04-23 07:18:16 Long 1.5 133.981
2 uj 2023-04-23 07:18:16 Short 1.5 134.519 1
3 uj 2023-04-23 07:18:16 Long 1.5 134.718
4 uj 2023-04-23 07:18:16 Short 0.75 134.931 3
5 uj 2023-04-23 07:18:16 Short 0.75 135.277 3
6 uj 2023-04-23 09:17:55 Short 1.5 134.519 1
```

参考までに、viewの出力結果も記載しておきます。

```sql
sqlite> SELECT * FROM Trading_Result ;
TP_ID Type Time IN_LS IN_Lot OUT_Lot IN_Price OUT_Price Pips Profit
----- ---- ------------------- ----- ------ ------- -------- --------- ----- ------
1 uj 2023-04-23 16:18:16 Long 1.5 1.5 133.981 134.519 0.538 8070.0
3 uj 2023-04-23 16:18:16 Long 1.5 0.75 134.718 134.931 0.213 1597.5
3 uj 2023-04-23 16:18:16 Long 1.5 0.75 134.718 135.277 0.559 4192.5
1 uj 2023-04-23 16:18:16 Long 1.5 1.5 133.981 134.519 0.538 8070.0
sqlite>
```

よろしくお願いします。

Answer 1

Triggerの起動タイミングをデフォルトのBEFOREからAFTERに修正されるとよいかと思います。

CREATE TRIGGER trg_TLtoAL AFTER INSERT ON Trading_Log

(正確な要件を理解できていないかもしれないのですが、その点はご了承ください)

Comments

[ishiha]

コードを修正したところ意図した結果を得ることができました。
回答ありがとうございました！