Movatterモバイル変換

To create a trigger or drop a trigger, use theCREATE TRIGGER orDROP TRIGGER statement, described inSection 15.1.26, “CREATE TRIGGER Statement”, andSection 15.1.39, “DROP TRIGGER Statement”.

Here is a simple example that associates a trigger with a table, to activate forINSERT operations. The trigger acts as an accumulator, summing the values inserted into one of the columns of the table.

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));Query OK, 0 rows affected (0.03 sec)mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account       FOR EACH ROW SET @sum = @sum + NEW.amount;Query OK, 0 rows affected (0.01 sec)

TheCREATE TRIGGER statement creates a trigger namedins_sum that is associated with theaccount table. It also includes clauses that specify the trigger action time, the triggering event, and what to do when the trigger activates:

To use the trigger, set the accumulator variable to zero, execute anINSERT statement, and then see what value the variable has afterward:

mysql> SET @sum = 0;mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);mysql> SELECT @sum AS 'Total amount inserted';+-----------------------+| Total amount inserted |+-----------------------+|               1852.48 |+-----------------------+

In this case, the value of@sum after theINSERT statement has executed is14.98 + 1937.50 - 100, or1852.48.

To destroy the trigger, use aDROP TRIGGER statement. You must specify the schema name if the trigger is not in the default schema:

mysql> DROP TRIGGER test.ins_sum;

If you drop a table, any triggers for the table are also dropped.

Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.

It is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you can have twoBEFORE UPDATE triggers for a table. By default, triggers that have the same trigger event and action time activate in the order they were created. To affect trigger order, specify a clause afterFOR EACH ROW that indicatesFOLLOWS orPRECEDES and the name of an existing trigger that also has the same trigger event and action time. WithFOLLOWS, the new trigger activates after the existing trigger. WithPRECEDES, the new trigger activates before the existing trigger.

For example, the following trigger definition defines anotherBEFORE INSERT trigger for theaccount table:

mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account       FOR EACH ROW PRECEDES ins_sum       SET       @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),       @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);Query OK, 0 rows affected (0.01 sec)

This trigger,ins_transaction, is similar toins_sum but accumulates deposits and withdrawals separately. It has aPRECEDES clause that causes it to activate beforeins_sum; without that clause, it would activate afterins_sum because it is created afterins_sum.

Within the trigger body, theOLD andNEW keywords enable you to access columns in the rows affected by a trigger.OLD andNEW are MySQL extensions to triggers; they are not case-sensitive.

In anINSERT trigger, onlyNEW.col_name can be used; there is no old row. In aDELETE trigger, onlyOLD.col_name can be used; there is no new row. In anUPDATE trigger, you can useOLD.col_name to refer to the columns of a row before it is updated andNEW.col_name to refer to the columns of the row after it is updated.

A column named withOLD is read only. You can refer to it (if you have theSELECT privilege), but not modify it. You can refer to a column named withNEW if you have theSELECT privilege for it. In aBEFORE trigger, you can also change its value withSET NEW.col_name =value if you have theUPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such aSET statement has no effect in anAFTER trigger because the row change has already occurred.)

In aBEFORE trigger, theNEW value for anAUTO_INCREMENT column is 0, not the sequence number that is generated automatically when the new row actually is inserted.

By using theBEGIN ... END construct, you can define a trigger that executes multiple statements. Within theBEGIN block, you also can use other syntax that is permitted within stored routines such as conditionals and loops. However, just as for stored routines, if you use themysql program to define a trigger that executes multiple statements, it is necessary to redefine themysql statement delimiter so that you can use the; statement delimiter within the trigger definition. The following example illustrates these points. It defines anUPDATE trigger that checks the new value to be used for updating each row, and modifies the value to be within the range from 0 to 100. This must be aBEFORE trigger because the value must be checked before it is used to update the row:

mysql> delimiter //mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account       FOR EACH ROW       BEGIN           IF NEW.amount < 0 THEN               SET NEW.amount = 0;           ELSEIF NEW.amount > 100 THEN               SET NEW.amount = 100;           END IF;       END;//mysql> delimiter ;

It can be easier to define a stored procedure separately and then invoke it from the trigger using a simpleCALL statement. This is also advantageous if you want to execute the same code from within several triggers.

There are limitations on what can appear in statements that a trigger executes when activated:

See alsoSection 27.10, “Restrictions on Stored Programs”.

MySQL handles errors during trigger execution as follows:

Triggers can contain direct references to tables by name, such as the trigger namedtestref shown in this example:

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);

Suppose that you insert the following values into tabletest1 as shown here:

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

As a result, the four tables contain the following data:

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)