Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


13.1.19 CREATE TRIGGER 構文

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

このステートメントは、新しいトリガーを作成します。トリガーとは、テーブルに関連付けられ、そのテーブルに対して特定のイベントが発生するとアクティブ化される名前付きデータベースオブジェクトのことです。トリガーは、tbl_name という名前のテーブルに関連付けられます。これは、永続的なテーブルを指す必要があります。トリガーを TEMPORARY テーブルまたはビューに関連付けることはできません。

トリガー名はスキーマの名前空間内に存在します。つまり、すべてのトリガーがスキーマ内で一意の名前を持つ必要があります。異なるスキーマ内のトリガーは同じ名前を持つことができます。

このセクションでは、CREATE TRIGGER 構文について説明します。詳細は、セクション20.3.1「トリガーの構文と例」を参照してください。

CREATE TRIGGER には、このトリガーに関連付けられたテーブルに対する TRIGGER 権限が必要です。このセクションのあとの方で説明されているように、DEFINER 値によっては、このステートメントに SUPER 権限も必要になる可能性があります。バイナリロギングが有効になっている場合は、セクション20.7「ストアドプログラムのバイナリロギング」で説明されているように、CREATE TRIGGERSUPER 権限が必要になることがあります。

DEFINER 句は、このセクションのあとの方で説明されているように、トリガーのアクティブ化時にアクセス権限を確認するときに使用されるセキュリティーコンテキストを決定します。

trigger_time は、このトリガーのアクション時間です。これは、トリガーが各行の変更の前またはあとにアクティブ化されることを示す BEFORE または AFTER にすることができます。

trigger_event は、このトリガーをアクティブ化する操作の種類を示します。次の trigger_event 値が許可されます。

  • INSERT: このトリガーは (たとえば、INSERTLOAD DATA、および REPLACE ステートメントを使用して) 新しい行がテーブルに挿入されると常にアクティブ化されます。

  • UPDATE: このトリガーは (たとえば、UPDATE ステートメントを使用して) 行が変更されると常にアクティブ化されます。

  • DELETE: このトリガーは (たとえば、DELETE および REPLACE ステートメントを使用して) 行がテーブルから削除されると常にアクティブ化されます。テーブルに対する DROP TABLE および TRUNCATE TABLE ステートメントは、DELETE を使用しないため、このトリガーをアクティブ化しません。また、パーティションを削除しても DELETE トリガーはアクティブ化されません。

trigger_event は、トリガーをアクティブ化する SQL ステートメントのリテラル型を表しているのではなく、テーブル操作の種類を表しています。たとえば、INSERT トリガーは、INSERT ステートメントだけでなく、LOAD DATA ステートメントでもアクティブ化されます。それは、どちらのステートメントもテーブルに行を挿入するためです。

この混乱を招く可能性がある例として、INSERT INTO ... ON DUPLICATE KEY UPDATE ... 構文があります。すべての行で BEFORE INSERT トリガーがアクティブ化されたあと、その行に重複キーが存在したかどうかに応じて、AFTER INSERT トリガーだけか、または BEFORE UPDATE トリガーと AFTER UPDATE トリガーの両方がアクティブ化されます。

注記

カスケードされた外部キーアクションはトリガーをアクティブ化しません。

特定のテーブルに対して、トリガーイベントとアクション時間が同じ複数のトリガーが存在していてはいけません。たとえば、1 つのテーブルに対して 2 つの BEFORE UPDATE トリガーを定義することはできません。ただし、BEFORE UPDATE および BEFORE INSERT トリガー、または BEFORE UPDATE および AFTER UPDATE トリガーは設定できます。

trigger_body は、トリガーがアクティブ化されるときに実行されるステートメントです。複数のステートメントを実行するには、BEGIN ... END 複合ステートメント構造構文を使用します。また、これにより、ストアドルーチン内で許可されるのと同じステートメントを使用することもできます。セクション13.6.1「BEGIN ... END 複合ステートメント構文」を参照してください。一部のステートメントは、トリガー内では許可されません。セクションD.1「ストアドプログラムの制約」を参照してください。

トリガー本体内では、エイリアス OLDNEW を使用して、対象テーブル (そのトリガーに関連付けられたテーブル) 内のカラムを参照できます。OLD.col_name は、更新または削除される前の既存の行のカラムを示します。NEW.col_name は、挿入された新しい行、または更新されたあとの既存の行のカラムを示します。

MySQL は、トリガーが作成されたときの有効な sql_mode システム変数の設定を格納し、トリガーが実行を開始したときの現在のサーバー SQL モードには関係なく、常にそのトリガー本体を強制的にこの設定で実行します。

DEFINER 句は、トリガーのアクティブ化時にアクセス権限を確認するときに使用される MySQL アカウントを指定します。user 値を指定する場合は、'user_name'@'host_name' (GRANT ステートメントで使用されるのと同じ形式)、CURRENT_USER、または CURRENT_USER() として指定された MySQL アカウントにしてください。DEFINER のデフォルト値は、CREATE TRIGGER ステートメントを実行するユーザーです。これは、明示的に DEFINER = CURRENT_USER を指定するのと同じです。

DEFINER 句を指定した場合は、次のルールによって有効な DEFINER ユーザーの値が決定されます。

  • SUPER 権限がない場合、許可される唯一の user 値は、リテラルで指定するか、または CURRENT_USER を使用して指定した自分のアカウントです。定義者をほかのアカウントに設定することはできません。

  • SUPER 権限がある場合は、構文として有効な任意のアカウント名を指定できます。そのアカウントが実際に存在しない場合は、警告が生成されます。

  • 存在しない DEFINER アカウントでトリガーを作成することはできますが、そのアカウントが実際に存在するようになるまで、このようなトリガーをアクティブ化することはお勧めできません。それ以外の権限確認に関する動作は定義されていません。

MySQL は、トリガー権限を確認するときに、DEFINER ユーザーを次のように考慮します。

  • CREATE TRIGGER の時点で、このステートメントを発行するユーザーには TRIGGER 権限が必要です。

  • トリガーのアクティブ化時、権限は DEFINER ユーザーに対して確認されます。このユーザーには、次の権限が必要です。

    • 対象テーブルに対する TRIGGER 権限。

    • テーブルカラムへの参照がトリガー本体内の OLD.col_name または NEW.col_name を使用して発生した場合は、対象テーブルに対する SELECT 権限。

    • テーブルカラムがトリガー本体内の SET NEW.col_name = value 割り当てのターゲットである場合は、対象テーブルに対する UPDATE 権限。

    • その他のどのような権限も、通常、そのトリガーによって実行されるステートメントに必要です。

トリガーのセキュリティーの詳細は、セクション20.6「ストアドプログラムおよびビューのアクセスコントロール」を参照してください。

トリガー本体内で、CURRENT_USER() 関数は、トリガーのアクティブ化時に権限を確認するために使用されるアカウントを返します。これは、そのトリガーがアクティブ化される原因となるアクションを実行したユーザーではなく、DEFINER ユーザーです。トリガー内のユーザー監査については、セクション6.3.13「SQL ベースの MySQL アカウントアクティビティーの監査」を参照してください。

LOCK TABLES を使用してトリガーを含むテーブルをロックした場合は、セクション13.3.5.2「LOCK TABLES とトリガー」で説明されているように、そのトリガー内で使用されているテーブルもロックされます。

トリガーの使用の詳細は、セクション20.3.1「トリガーの構文と例」を参照してください。


User Comments
  Posted by Jonathan Haddad on September 15, 2006
I posted a breakdown of the above trigger statements, when I learned them before I could have used the example above in a format like this. I hope it helps someone.

http://www.rustyrazorblade.com/index.php/2006/09/14/mysql-triggers-tutorial/
  Posted by Scott White on November 28, 2006
Be careful with BEFORE triggers. Constraints may occur, specifically if you are using InnoDB engine, where an insert will fail, but actions from your BEFORE trigger will succeed.

Use BEFORE triggers primarily for constraints or rules, not transactions, tweaking the NEW.* columns should be fine.

Stick with AFTER triggers for most other operations, such as inserting into a history table or updating a denormalization.
  Posted by Luciano Fantuzzi on April 16, 2007
It's not possible to perform a "STOP ACTION" into a TRIGGER. For example, if you're deleting a row and this action activates a trigger, is not possible to abort the proccess of DELETE of the row. A way to abort the current operation, is to cause a deliberated error.
  Posted by Philip Mather on April 22, 2007
If you've implemented SSL, see...

http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html

...you can use Triggers and DES_ENCRYPT to move your password encryption to the database level and enforce it in a way that stops developers forgeting to use it (or bypassing it) with the following triggers...

CREATE TRIGGER user_insert BEFORE INSERT ON `user` FOR EACH ROW SET NEW.TimeStampCreated = NOW(), NEW.Password = DES_ENCRYPT(NEW.Password);

CREATE TRIGGER user_update BEFORE UPDATE ON `user` FOR EACH ROW SET NEW.Password = DES_ENCRYPT(NEW.Password);

...you'll also notice the first one enforces auditing in a way that saves you from relying on developers getting that right as well.

You could give your dev's a nice stored proc to retrieve or comapre their submitted password but hopefully they can remember either DES_ENCRYPT/_DECRYPT or your phone number ;^).

Whilst bearing in mind that this doesn't magically make your entire system "secure" by some magic wave of a wand, given that you've implemented SSL it should be trivial to secure the link between web and database server (if there even is a gap) and then you can use HTTPS and only a little more careful thought to implement a system that is secure from submission page through to backup system in such a way that only someone physically stood at the server with the server's and Mysql's root password could decrypt the password/data.
  Posted by Nicolas LESCURE on August 13, 2007
Another way to "STOP ACTION" is to create a table (stop_action) with just a primary key(reason_to_stop). Then you pre-fill this table with some text ('do not do that', 'or that either')=> to stop action, just do an insert into this table (stop_action) with any of the pre-filled value ('do not do that').
  Posted by Anony Mous on June 20, 2008
TIP: to create a simple trigger that initializes multiple columns such as DATE type columns when a record is created:

CREATE TRIGGER mytrigger BEFORE INSERT ON TABLE_1 FOR EACH ROW SET NEW.MY_DATETIME_COLUMN = NOW(), NEW.MY_DATE_COLUMN = CURDATE()

Note no END statement nor ending delimiter.
  Posted by Martijn Korse on August 8, 2008
In response to the STOP ACTION simulations:

Luciano Fantuzzi suggested to cause a deliberate error; this might cause problems though.
It was my first approach too in a sanity-check like trigger. It calls a procedure which does the actual check and then assigns either 1 or 0 to @resultBool - 0 meaning it did not pass. In that case i wanted to prevent the INSERT by causing a deliberate error in the form of updating a non-existing table. This is what my attempt looked like:

CREATE TRIGGER sanityCheck
BEFORE INSERT ON someTable
FOR EACH ROW
BEGIN
CALL doSanityCheck(@resultBool, @resultMessage);
IF @resultBool = 0 THEN
UPDATE ThereWasAnError_Call_privilegeSanityCheck_ToViewTheError SET ThereWas='an error';
END IF;
END;//

While mysql allows the trigger even though the table doesn't exist, it will _always_ complain (throw an error) when it executes the trigger, even if @resultBool = 1. So, this will not work.

The suggestion of Nicolas LESCURE does work in combination with the IF-statement
  Posted by S Roberts on June 2, 2009
On Stop Action problems.

My solution was to create a unique/primary key on the table I want to insert into (in this case on S_ID), then if my sanity check fails I change the s_id to 0. This method will only ever create one duff record with an s_id of 0. Obviously you need to INSERT ignore!

CREATE TRIGGER sanityCheck
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
IF something THEN
SET NEW.S_ID = 0 ;
END IF;
END;
  Posted by Jiju Thomas Mathew on June 20, 2009
To invoke a shell command from a trigger I used a roundabout approach, write to a predefined folder using trigger code 'select into outfile', and famd to monitor that folder, which triggers a php script.

[http://www.php-trivandrum.org/code-snippets/invoke-shell-from-mysql-trigger.html Invoke shell from MySQL trigger]
  Posted by Pete Wilson on March 1, 2010
  Posted by Tim McCormack on November 14, 2010
Continuing on the theme of how to emulate STOP ACTION, I wrote this code:

delimiter $$

DROP TABLE IF EXISTS blocked_insert_message $$
CREATE TABLE blocked_insert_message (
unique_error_msg VARCHAR(330) NOT NULL,
UNIQUE KEY `unique_error_msg` (`unique_error_msg`)
) $$

DROP PROCEDURE IF EXISTS die_with_error $$
CREATE PROCEDURE die_with_error(msg varchar(300))
COMMENT 'Call this to STOP ACTION with a message.'
MODIFIES SQL DATA BEGIN
DECLARE ts DATETIME DEFAULT NOW();
DECLARE txt VARCHAR(300) DEFAULT msg;
DECLARE uniq VARCHAR(330) DEFAULT (SELECT CONCAT(ts, ': ', txt));
-- Run it twice to throw as an error.
INSERT INTO blocked_insert_message VALUES (uniq);
INSERT INTO blocked_insert_message VALUES (uniq);
END $$

delimiter ;

When called from a trigger with "call die_with_error('test test test test');" the result is an error like this:

ERROR 1062 (23000) at line 1: Duplicate entry '2010-11-13 21:30:02: test test test test' for key 'unique_error_msg'

The clear benefit is that the error message will be thrown reliably and contain arbitrary (though short) free text.
  Posted by Rui Da-Costa on August 25, 2011
Here's how I got it working using signals for raising the error:
delimiter //
use test//
create table trigger_test
(
id int not null
)//
drop trigger if exists trg_trigger_test_ins //
create trigger trg_trigger_test_ins before insert on trigger_test
for each row
begin
declare msg varchar(255);
if new.id < 0 then
set msg = concat('MyTriggerError: Trying to insert a negative value in trigger_test: ', cast(new.id as char));
signal sqlstate '45000' set message_text = msg;
end if;
end
//

delimiter ;
-- run the following as seperate statements:
insert into trigger_test values (1), (-1), (2); -- everything fails as one row is bad
select * from trigger_test;
insert into trigger_test values (1); -- succeeds as expected
insert into trigger_test values (-1); -- fails as expected
select * from trigger_test;
  Posted by Jon Vance on November 1, 2012
I have discovered something that can be VERY important if you don't know about it. When using INSERT IGNORE, insert triggers are STILL FIRED when a duplicate key constraint prevents new rows from being inserted.
Sign Up Login You must be logged in to post a comment.