SQLite トリガー（Trigger）

SQLite トリガー（Trigger）データベースのコールバック関数であり、指定されたデータベースイベントが発生したときに自動的に実行/呼び出されます。以下は SQLite のトリガー（Trigger）に関する要点です：

  • SQLite のトリガー（Trigger）は、特定のデータベーステーブルで DELETE、INSERT、または UPDATE が発生したとき、または1つ以上の指定されたテーブルの列が更新されたときにトリガーされるように指定できます。

  • SQLite は FOR EACH ROW トリガー（Trigger）のみをサポートしており、FOR EACH STATEMENT トリガー（Trigger）はありません。したがって、FOR EACH ROW を明示的に指定することはオプションです。

  • WHEN 句とトリガー（Trigger）アクションは、次の形式を使用してNEW.column-nameとOLD.column-name挿入、削除、または更新された行要素を参照できます。ここで column-name は、トリガーに関連付けられたテーブルの列の名前です。

  • WHEN 句が指定されている場合、WHEN 句が真である指定された行に対してのみ SQL 文が実行されます。WHEN 句が指定されていない場合は、すべての行に対して SQL 文が実行されます。

  • BEFORE または AFTER キーワードは、トリガーアクションをいつ実行するかを決定し、関連する行の挿入、変更、または削除の前か後かにトリガーアクションを実行するかを決定します。

  • トリガーが関連付けられているテーブルが削除されると、トリガー（Trigger）も自動的に削除されます。

  • 変更するテーブルは、トリガーがアタッチされているテーブルまたはビューと同じデータベースに存在する必要があり、次のものだけを使用する必要がありますtablenameではなくdatabase.tablename。

  • 特別な SQL 関数 RAISE() を使用すると、トリガープログラム内で例外を発生させることができます。

構文

作成トリガー（Trigger）の基本的な構文は次のとおりです：

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- 触发器逻辑....
END;

ここで、event_name前述のテーブルに対するtable_name上のINSERT、DELETE、および UPDATEデータベース操作です。テーブル名の後に FOR EACH ROW を指定することもできます。

以下は、UPDATE 操作でテーブルの1つ以上の指定された列にトリガー（Trigger）を作成する構文です：

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
 -- 触发器逻辑....
END;

例

新しく作成された COMPANY テーブル（既に存在する場合は削除して再作成）に挿入されるすべてのレコードについて監査証跡を保持する場合を想定しましょう：

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

監査証跡を保持するために、AUDIT という名前の新しいテーブルを作成します。COMPANY テーブルに新しいレコードが追加されるたびに、ログメッセージがそのテーブルに挿入されます：

sqlite> CREATE TABLE AUDIT(
    EMP_ID INT NOT NULL,
    ENTRY_DATE TEXT NOT NULL
);

ここで、ID は AUDIT レコードの ID、EMP_ID は COMPANY テーブルの ID、DATE は COMPANY でレコードが作成された時刻のタイムスタンプを保持します。それでは、COMPANY テーブルに次のようにトリガーを作成しましょう：

sqlite> CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;

次に、COMPANY テーブルにレコードを挿入します。これにより、AUDIT テーブルに監査ログレコードが作成されます。それでは、COMPANY テーブルに次のようにレコードを作成しましょう：

sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

これにより、COMPANY テーブルに次のようなレコードが作成されます：

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

同時に、AUDIT テーブルにもレコードが作成されます。このレコードは、COMPANY テーブルの INSERT 操作に対して作成したトリガー（Trigger）の結果です。同様に、必要に応じて UPDATE および DELETE 操作に対してトリガー（Trigger）を作成できます。

EMP_ID      ENTRY_DATE
----------  -------------------
1           2013-04-05 06:26:00

トリガー（TRIGGERS）を一覧表示

次のsqlite_masterテーブルからすべてのトリガーを一覧表示できます。次のとおりです：

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger';

上記の SQLite ステートメントは、次のように1つのエントリのみを一覧表示します：

name
----------
audit_log

特定のテーブル上のトリガーを一覧表示する場合は、AND 句を使用してテーブル名を接続します。次のとおりです：

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';

上記の SQLite ステートメントは、次のように1つのエントリのみを一覧表示します：

name
----------
audit_log

トリガー（TRIGGERS）を削除

以下は、既存のトリガーを削除するために使用できる DROP コマンドです：

sqlite> DROP TRIGGER trigger_name;
その他の拡張