( Using triggers

Info Catalog ( DROP TRIGGER ( Triggers
 20.3 Using Triggers
 Support for triggers is included beginning with MySQL 5.0.2.  Currently,
 trigger support is rudimentary, so there are certain limitations on
 what you can do with them.  This section discusses how to use triggers
 and what the current limitations are.
 A trigger is a named database object that is associated with a table,
 and that activates when a particular event occurs for the table.  Some
 uses for triggers are to perform checks of values to be inserted into a
 table or to perform calculations on values involved in an update.
 A trigger is associated with a table and is defined to activate when an
 `INSERT', `DELETE', or `UPDATE' statement for the table executes.  A
 trigger can be set to activate either before or after the triggering
 statement.  For example, you can have a trigger activate before each
 row that is deleted from a table or after each row that is updated.
 To create a trigger or drop a trigger, use the `CREATE TRIGGER' or
 `DROP TRIGGER' statement.  The syntax for these statements is described
 Here is a simple example that associates a trigger with a table for
 `INSERT' statements.  It acts as an accumulator to sum the values
 inserted into one of the columns of the table.
 The following statements create a table and a trigger for it:
      mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
      mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
          -> FOR EACH ROW SET @sum = @sum + NEW.amount;
 The `CREATE TRIGGER' statement creates a trigger named `ins_sum' that
 is associated with the `account' table.  It also includes clauses that
 specify the trigger activation time, the triggering event, and what to
 do with the trigger activates:
    * The keyword `BEFORE' indicates the trigger action time.  In this
      case, the trigger should activate before each row inserted into
      the table.  The other allowable keyword here is `AFTER'.
    * The keyword `INSERT' indicates the event that activates the
      trigger.  In the example, `INSERT' statements cause trigger
      activation.  You can also create triggers for `DELETE' and
      `UPDATE' statements.
    * The statement following `FOR EACH ROW' defines the statement to
      execute each time the trigger activates, which occurs once for
      each row affected by the triggering statement  In the example, the
      triggered statement is a simple `SET' that accumulates the values
      inserted into the `amount' column.  The statement refers to the
      column as `NEW.amount' which means "the value of the `amount'
      column to be inserted into the new row."
 To use the trigger, set the accumulator variable to zero, execute an
 `INSERT' 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 the `INSERT' statement has
 executed is `14.98 + 1937.50 - 100' or `1852.48'.
 To destroy the trigger, use a `DROP TRIGGER' statement.  The trigger
 name must include the table name:
      mysql> DROP TRIGGER account.ins_sum;
 Because a trigger is associated with a particular table, you cannot have
 multiple triggers for a table that have the same name.  You should also
 be aware that the namespace for triggers might change in the future
 from table to database.  That is, the requirement that trigger names be
 unique for a given table might be strengthened to the requirement that
 they be unique within the database.  For better forward compatibility
 with future development, try to use trigger names that do not overlap
 within a database.
 In addition to the requirement that trigger names be unique for a table,
 there are other limitations on the types of triggers you can create.
 In particular, you cannot have two triggers for a table that have the
 same activate time and activation event.  For example, you cannot
 define two `BEFORE INSERT' triggers or two `AFTER UPDATE' triggers for
 a table.  This should rarely be a significant limitation, because it is
 possible to define a trigger that executes multiple statements by using
 the `BEGIN ... END' compound statement construct after `FOR EACH ROW'.
 (An example appears later in this section.)
 There are also limitations on what can appear in the statement that the
 trigger executes when activated:
    * The trigger cannot refer directly to tables by name, including the
      table with which the table is associated.  You can, however, use
      the keywords `OLD' and `NEW'.  `OLD' refers to an existing row to
      be deleted or a row to be updated before the update occurs.  `NEW'
      refers to a new row to be inserted or an updated row after the
      update occurs.
    * The trigger cannot invoke stored procedures using the `CALL'
      statement.  (This means, for example, that you cannot get around
      the prohibition on referring to tables by name by invoking a
      stored procedure that refers to the tables.)
    * The trigger cannot use statements that begin or end a transaction
 The `OLD' and `NEW' keywords enable you to access columns in the rows
 affected by a trigger.  (`OLD' and `NEW' are not case sensitive.)  In
 an `INSERT' trigger, only `NEW.COL_NAME' can be used; there is no old
 row. In a `DELETE' trigger, only `OLD.COL_NAME' can be used; there is
 no new row.  In an `UPDATE' trigger, you can use `OLD.COL_NAME' to
 refer to the columns of a row before it is updated and `NEW.COL_NAME'
 to refer to the columns of the row after it is updated.
 A column named with `OLD' is read-only. You can refer to it but not
 modify it.  A column named with `NEW' can be referred to if you have
 the `SELECT' privilege for it. In a `BEFORE' trigger, you can also
 change its value with `SET NEW.COL_NAME = VALUE' if you have the
 `UPDATE' privilege for it.  This means you can use a trigger to modify
 the values to be inserted into a new row or that are used to update a
 `OLD' and `NEW' are MySQL extensions to triggers.
 By using the `BEGIN ... END' construct, you can define a trigger that
 executes multiple statements. Within the `BEGIN' block, you also can
 use other syntax that is allowed within stored routines such as
 conditionals and loops. However, just as for stored routines, when you
 define a trigger that executes multiple statements, it becomes
 necessary to redefine the statement delimiter if you are entering the
 trigger with the `mysql' program so that you can use the `;' character
 within the trigger definition.  The following example illustrates these
 points. It defines an `UPDATE' 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 a `BEFORE' trigger because the value
 needs to 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 might occur to you that it would be easier to define a stored
 procedure separately and then invoke it from the trigger using a simple
 `CALL' statement.  That would also be advantageous if you wanted to
 invoke the same routine from within several triggers. However, a
 limitation on triggers is that `CALL' cannot be used. You have to write
 out the compound statement in each `CREATE TRIGGER' statement where you
 want to use it.
Info Catalog ( DROP TRIGGER ( Triggers
automatically generated byinfo2html