CREATE [DEFINER = {user
| CURRENT_USER }] TRIGGERtrigger_name
trigger_time
trigger_event
ONtbl_name
FOR EACH ROWtrigger_stmt
このステートメントによって、新しいトリガが生成されます。トリガは名称を持つ、テーブルに付属するデータベース オブジェクトで、テーブルに対して特定イベントが発生すると有効化されます。現在、CREATE TRIGGER
には、そのトリガに添付したテーブルに対する TRIGGER
権限が必要です。(このステートメントは MySQL より前のバージョンに SUPER
権限を求めるものです。)
トリガは、tbl_name
なる名称を持つテーブルと連携するようになり、これによって、トリガはパーマネント テーブルを参照しなければならなくなります。トリガを TEMPORARY
テーブルあるいはビューと連携させる事はできません。
トリガが有効化されると、このセクションで後に述べるように DEFINER
条項によって、適用すべき権限が規定されます。
trigger_time
はトリガのアクション タイムです。それを有効化するステートメントの前か後にトリガが有効化される事を示す BEFORE
または AFTER
となる事ができます。
trigger_event
はトリガを有効化するステートメントの種類を示します。trigger_event
では、以下の中から1つを選ぶ事ができます:
INSERT
:トリガは、新しい行がテーブルに挿入されると必ず、例えば INSERT
、LOAD DATA
並びに REPLACE
の各ステートメントを通して有効化されます。
UPDATE
:トリガは、行が修正されると必ず、例えば UPDATE
ステートメントを通して有効化されます。
DELETE
:トリガは、新しい行がテーブルから削除されると必ず、例えば DELETE
ステートメント並びに REPLACE
ステートメントを通して有効化されます。しかし、テーブル上の DROP TABLE
ステートメントおよび TRUNCATE
ステートメントは DELETE
を使用していないので、このトリガを有効化 しません。パーティションをドロップすると、DELETE
トリガも有効化されません。詳しくは 項 「TRUNCATE
構文」 を参照してください。
trigger_event
は、それがテーブル 操作のタイプを表している場合には、トリガを有効化する SQL ステートメントのリテラル タイプを表さないと理解する事が重要です。例えば、ステートメントは両方共行をテーブルに挿入するので、INSERT
トリガは、INSERT
ステートメントのみならず、LOAD DATA
ステートメントによっても有効化されます。
この潜在的に紛らわしい例は、INSERT INTO ... ON DUPLICATE KEY UPDATE ...
という構文です: BEFORE INSERT
トリガは、全ての行に対して有効化され、その後、行に対して重複キーがあるか否かによって、AFTER INSERT
トリガもしくは BEFORE UPDATE
トリガと AFTER UPDATE
トリガの両方が有効化されます。
同じトリガ アクション タイムとトリガ イベントを持つテーブルに対して、2つのトリガは存在する事はできません。例えば、1個のテーブルに対して2つの
BEFORE UPDATE
トリガを持つ事はできません。しかし、BEFORE UPDATE
トリガと BEFORE INSERT
トリガもしくは BEFORE UPDATE
トリガと AFTER UPDATE
トリガを持つ事ができます。
trigger_stmt
はトリガを有効化する時に実行させるステートメントです。複数の命令を実行させたい場合には、BEGIN ... END
複合文コンストラクトを使ってください。これは、ストアド ルーチン内で許されているのと同じステートメントも使う事ができるようにします。項 「BEGIN ... END
複合ステートメント構文」 を参照してください。トリガ中に同じステートメントを使用する事は許されません。(項D.1. 「ストアド ルーチンとトリガの規制」 を参照)。
MySQL は sql_mode
システム中に、トリガが生成された時に有効であった変数設定を記憶し、現サーバの SQL モードに関係なく、この設定でトリガを実際に実行します。
注:トリガは現在、転送された外部キー アクションによって有効化されません。この制限はできるだけ早く撤廃されるでしょう。
MySQL 5.1 で、この例に示す testref
という名前のトリガのような、直接の参照を含むトリガを名称別テーブルに書き込む事ができます:
CREATE TABLE test1(a1 INT); CREATE TABLE test2(a2 INT); CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); CREATE TABLE test4( a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0 ); DELIMITER | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; | DELIMITER ; INSERT INTO test3 (a3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL); INSERT INTO test4 (a4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
以下の値をテーブル test1
に、ここに示すように挿入すると仮定します:
mysql>INSERT INTO test1 VALUES
->(1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0
結果として、4つのテーブル中のデータは以下の通りになります:
mysql>SELECT * FROM test1;
+------+ | a1 | +------+ | 1 | | 3 | | 1 | | 7 | | 1 | | 8 | | 4 | | 4 | +------+ 8 rows in set (0.00 sec) mysql>SELECT * FROM test2;
+------+ | a2 | +------+ | 1 | | 3 | | 1 | | 7 | | 1 | | 8 | | 4 | | 4 | +------+ 8 rows in set (0.00 sec) mysql>SELECT * FROM test3;
+----+ | a3 | +----+ | 2 | | 5 | | 6 | | 9 | | 10 | +----+ 5 rows in set (0.00 sec) mysql>SELECT * FROM test4;
+----+------+ | a4 | b4 | +----+------+ | 1 | 3 | | 2 | 0 | | 3 | 1 | | 4 | 2 | | 5 | 0 | | 6 | 0 | | 7 | 1 | | 8 | 1 | | 9 | 0 | | 10 | 0 | +----+------+ 10 rows in set (0.00 sec)
エイリアス OLD
とエイリアス NEW
を使う事によって、問題のテーブル(トリガに添付されたテーブル)中のカラムを参照する事ができます。OLD.col_name
は既存の行のカラムが更新または削除される前にチェックします。NEW.col_name
は挿入すべき新しい行あるいは更新された既存の行のカラムを参照します。
DEFINER
条項は、トリガの有効化においてアクセス権をチェックする時に使用すべき MySQL アカウントを指定します。user
値を附与する場合、それを 'user_name'@'host_name'
フォーマット(GRANT
ステートメントに使用したのと同じフォーマット)の中にある MySQL アカウントとするべきです。user_name
の値と host_name
の値が両方共必要です。CURRENT_USER
を CURRENT_USER()
として附与する事もできます。DEFINER
のデフォルト値は CREATE TRIGGER
ステートメントを実行するユーザです。(これは DEFINER = CURRENT_USER
と同じです。)
DEFINER
条項を特定する場合、SUPER
権限を保持していない限り、自分の値を除くいかなるアカウントにも値をセットする事はできません。これらの規則は有効な DEFINER
ユーザ値を決定します:
SUPER
権限を持っていない場合、文字によるか、CURRENT_USER
を使って規定されている user
値だけが有効なユーザ アカウントとなります。デファイナを他のアカウントに設定する事はできません。
SUPER
権限を持っている場合、構文的に有効なアカウント ネームを規定する事ができます。そのアカウントが実在しない場合、警告が生成されます。
実在しない DEFINER
値を使ってトリガを生成させる事は可能ですが、デファイナが実在するようになるまで、このようなトリガを有効化しておく事は決して良いアイデアではありません。さもなければ、権限チェックに関する挙動パターンは定義されません。
注意:MySQL 5.1.6以前のバージョンでは、MySQL は CREATE TRIGGER
を使用する為に SUPER
権限を要求するので、前の規則の2番目の規定だけが適用されます。5.1.6以降のバージョンからは、CREATE TRIGGER
は TRIGGER
権限を要求し、SUPER
権限は、DEFINER
に自身のアカウント値以外の値をセットする事を可能にする為だけに要求されます。
MySQL は、このようなトリガ権限をチェックします:
CREATE TRIGGER
時に、ステートメントを発行するユーザは TRIGGER
権限を持つ必要があります。((MySQL 前の SUPER
)
トリガを有効化する時、権限が DEFINER
ユーザの物であるかチェックされます。このユーザは、これらの権限を持っていなければなりません:
TRIGGER
権限。((MySQL 前の SUPER
)
トリガの定義の中にある OLD.col_name
あるいは NEW.col_name
を経由して照合が行われる場合に得られる問題のテーブルに対する SELECT
権限。
テーブル カラムが、SET NEW.col_name = value
トリガの定義中の値の割り当てターゲットである場合に得られる問題のテーブルに対する UPDATE
権限。
トリガによってステートメントが実行される為に一般に要求されるその他一切の権限。