SQLite-トリガー

SQLite *トリガー*はデータベースコールバック関数で、指定されたデータベースイベントが発生すると自動的に実行/呼び出されます。 以下は、SQLiteトリガーに関する重要なポイントです-

  • SQLiteトリガーは、特定のデータベーステーブルのDELETE、INSERT、またはUPDATEが発生したとき、またはテーブルの1つ以上の指定された列でUPDATEが発生したときに起動するように指定できます。
  • 現時点では、SQLiteはFOR EACH STATEMENTトリガーではなく、FOR EACH ROWトリガーのみをサポートしています。 したがって、FOR EACH ROWを明示的に指定することはオプションです。
  • WHEN句とトリガーアクションの両方は、 NEW.column-name および OLD.column-name の形式の参照を使用して、挿入、削除、または更新される行の要素にアクセスできます。column-nameはトリガーが関連付けられているテーブルの列。
  • WHEN句が指定されている場合、指定されたSQLステートメントは、WHEN句がtrueである行に対してのみ実行されます。 WHEN句が指定されていない場合、SQLステートメントはすべての行に対して実行されます。
  • BEFOREまたはAFTERキーワードは、関連付けられた行の挿入、変更、または削除に関連してトリガーアクションがいつ実行されるかを決定します。
  • トリガーは、関連付けられているテーブルが削除されると自動的に削除されます。
  • 変更するテーブルは、トリガーがアタッチされるテーブルまたはビューと同じデータベースに存在する必要があり、 database.tablename ではなく tablename のみを使用する必要があります。
  • 特別なSQL関数RAISE()をトリガープログラム内で使用して、例外を発生させることができます。

構文

以下は、*トリガー*を作成する基本的な構文です。

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

ここで、 event_name は、前述の表 table_name に対する_INSERT、DELETE、_、および_UPDATE_データベース操作です。 オプションで、テーブル名の後にFOR EACH ROWを指定できます。

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

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
   -- Trigger logic goes here....
END;

COMPANYテーブルに挿入されるすべてのレコードの監査トライアルを保持したい場合を考えてみましょう。次のように新しく作成します(既にある場合はCOMPANYテーブルを削除します)。

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

監査トライアルを続けるために、新しいレコードのCOMPANYテーブルにエントリがあるたびに、ログメッセージが挿入されるAUDITという新しいテーブルを作成します。

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;

ここで、実際の作業を開始します。AUDITテーブルに監査ログレコードを作成する必要があるCOMPANYテーブルにレコードを挿入しましょう。 次のようにCOMPANYテーブルに1つのレコードを作成します-

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

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

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

同時に、AUDITテーブルに1つのレコードが作成されます。 このレコードは、COMPANYテーブルのINSERT操作で作成したトリガーの結果です。 同様に、要件に基づいてUPDATEおよびDELETE操作でトリガーを作成できます。

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

トリガーのリスト

次のように 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

トリガーの削除

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

sqlite> DROP TRIGGER trigger_name;