MySQL Triggers

Support for triggers is included beginning with MySQL 5.0.2. A trigger is a named database object that is associated with a table and that is activated when a particular event occurs for the table. For example, the following statements create a table and an INSERT trigger. The trigger sums the values inserted into one of the table's columns:

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.06 sec)

Important:

MySQL triggers are activated by SQL statements only. They are not activated by changes in tables made by APIs that do not transmit SQL statements to the MySQL Server; in particular, they are not activated by updates made using the NDB API. Restrictions on Stored Routines and Triggers

Some of the restrictions noted here apply to all stored routines; that is, both to stored procedures and stored functions. Some of restrictions apply only to stored functions, and not to stored procedures.

All of the restrictions for stored functions also apply to triggers.

Stored routines cannot contain arbitrary SQL statements. The following statements are disallowed:

• The table-maintenance statements CHECK TABLES and OPTIMIZE TABLES. This restriction is lifted beginning with MySQL 5.0.17.
• The locking statements LOCK TABLES, UNLOCK TABLES.
• LOAD DATA and LOAD TABLE.

CREATE

    [DEFINER = { user | CURRENT_USER }]

    TRIGGER trigger_name trigger_time trigger_event

    ON tbl_name FOR EACH ROW trigger_stmt

This statement creates a new trigger. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. CREATE TRIGGER was added in MySQL 5.0.2. Currently, its use requires the SUPER privilege.

The trigger becomes associated with the table named tbl_name, which must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view.

When the trigger is activated, the DEFINER clause determines the privileges that apply, as described later in this section.

trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after the statement that activated it.

trigger_event indicates the kind of statement that activates the trigger. The trigger_event can be one of the following:

· INSERT: The trigger is activated whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE statements.

· UPDATE: The trigger is activated whenever a row is modified; for example, through UPDATE statements.

· DELETE: The trigger is activated whenever a row is deleted from the table; for example, through DELETE and REPLACE statements. However, DROP TABLE and TRUNCATE statements on the table do not activate this trigger, because they do not use DELETE.

It is important to understand that the trigger_event does not represent a literal type of SQL statement that activates the trigger so much as it represents a type of table operation. For example, an INSERT trigger is activated by not only INSERT statements but also LOAD DATA statements because both statements insert rows into a table.

A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger will activate for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row.

There cannot be two triggers for a given table that have the same trigger action time and event. For example, you cannot have two BEFORE UPDATE triggers for a table. But you can have a BEFORE UPDATE and a BEFORE INSERT trigger, or a BEFORE UPDATE and an AFTER UPDATE trigger.

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