trigger

We may have the following requirements when using MySQL:

• Before inserting or updating data into t1 table, the data is automatically verified. The value of m1 column must be between 1 and 10. The verification rules are as follows:

  • If the value of the m1 column of the inserted record is less than 1, press 1 to insert.
  • If the value of the m1 column is greater than 10, press 10 to insert.

• After inserting a record into the t1 table, the record is automatically inserted into the t2 table.

That is, we may need to let the MySQL server automatically execute some additional statements before and after adding, deleting and modifying records in the table. This is the application scenario of the so-called trigger.

Create trigger

Let's take a look at the statement that defines the trigger:

CREATE TRIGGER Trigger Name 
{BEFORE|AFTER}
{INSERT|DELETE|UPDATE}
ON Table name
FOR EACH ROW
BEGIN
    Trigger content
END

Tips:

By braces`{}`Wrap and use vertical lines inside`|`Delimited statements indicate that a value must be selected from a given option, such as`{BEFORE|AFTER}`Indicates that you must`BEFORE`,`AFTER`Choose between the two.

Where {BEFORE|AFTER} represents the execution time of trigger content. Their meanings are as follows:

name	describe
BEFORE	Indicates that the trigger is executed before the specific statement is executed
AFTER	Indicates that the trigger is not executed until the specific statement is executed

{INSERT|DELETE|UPDATE} represents specific statements. Currently, MySQL only supports setting triggers for INSERT, DELETE and UPDATE statements.

For each row begin... End means that each record affected by a specific statement executes our customized trigger content:

• For the INSERT statement, the records affected by FOR EACH ROW are the new records we are going to INSERT.

• For DELETE statements and UPDATE statements, the records affected by FOR EACH ROW are those that meet the WHERE condition (if there is no WHERE condition in the statement, it represents all records).

Tip: if the trigger contains only one statement, you can also omit the words BEGN and END.

Because the MySQL server will call our customized trigger content successively for all records affected by a statement, we need a way to access the content in the record for each affected record. MySQL provides NEW and OLD words to represent the NEW record and the OLD record respectively. They have different meanings in different statements:

• For the trigger set by the INSERT statement, NEW represents the record to be inserted, and OLD is invalid.
• For the trigger set by the DELETE statement, OLD represents the record before deletion, and NEW is invalid.
• For the trigger set by the UPDATE statement, NEW represents the modified record and OLD represents the record before modification.

Now we can formally define a trigger:

mysql> delimiter $
mysql> CREATE TRIGGER bi_t1
    -> BEFORE INSERT ON t1
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.m1 < 1 THEN
    ->         SET NEW.m1 = 1;
    ->     ELSEIF NEW.m1 > 10 THEN
    ->         SET NEW.m1 = 10;
    ->     END IF;
    -> END $
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql>

We define a table named Bi for the t1 table_ The trigger of t1 means that before inserting a NEW record into the t1 table, the statement between begin... End will be executed for each record to be inserted. The NEW. Column name represents the value of the specified column of the current record to be inserted. There are 4 records in t1 table:

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

mysql>

Let's now execute the insert statement and look at the contents of the t1 table again:

mysql> INSERT INTO t1(m1, n1) VALUES(5, 'e'), (100, 'z');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|   10 | z    |
+------+------+
6 rows in set (0.00 sec)

mysql>

There are two records affected by this INSERT statement, namely (5, 'e') and (100, 'z'). These two records will execute our customized trigger content respectively. Obviously, (5, 'e') is successfully inserted into the t1 table, while (100, 'z') is inserted into the table and becomes (10, 'z'), which shows our bi_t1 trigger is in effect!

Tip: trigger name defined above ` bi_t1's' bi 'is the acronym of' before insert ', ` t1' is the table name. Although there are no special requirements for the naming of triggers, it is customary to suggest that you define it as the form in my above example, that is, 'bi'_ Table name `, ` bd_ Table name `, ` bu_ Table name `, ` ai_ Table name `, ` ad_ Table name `, ` au_ Form of table name '.

The above is just an example of setting BEFORE trigger for INSERT statement. The process of setting BEFORE or AFTER trigger for DELETE and UPDATE operations is similar, so I won't repeat it.

View and delete triggers

To view the statements of all triggers defined in the current database:

SHOW TRIGGERS;

To view the definition of a specific trigger:

SHOW CREATE TRIGGER Trigger Name ;

Delete trigger:

DROP TRIGGER Trigger Name ;

These commands are too simple. I won't give you examples

Precautions for use of trigger

• There cannot be a statement that outputs a result set in the trigger content.

  For example:

  mysql> delimiter $
  mysql> CREATE TRIGGER ai_t1
      -> AFTER INSERT ON t1
      -> FOR EACH ROW
      -> BEGIN
      ->     SELECT NEW.m1, NEW.n1;
      -> END $
  ERROR 1415 (0A000): Not allowed to return a result set from a trigger
  mysql>
  

  The ERROR displayed means that the result set is not allowed to be returned in the trigger content!

• In the trigger content, the value of the column NEW represents the record can be changed, and the value of the column OLD represents the record cannot be changed.

  NEW represents a newly inserted or soon modified record. Modifying the value of its column will affect the results after the execution of INSERT and UPDATE statements, while OLD represents the value before modification or deletion. We cannot modify it. For example, if we have to write this, we will report an error:

  mysql> delimiter $
  mysql> CREATE TRIGGER bu_t1
      -> BEFORE UPDATE ON t1
      -> FOR EACH ROW
      -> BEGIN
      ->     SET OLD.m1 = 1;
      -> END $
  ERROR 1362 (HY000): Updating of OLD row is not allowed in trigger
  mysql>
  

  You can see that in the prompted error, the record represented by OLD in the trigger cannot be changed.

• In the BEFORE trigger, we can use the form of SET NEW. Column name = a value to change the value of a column of the record to be inserted or updated, but this operation cannot be used in the AFTER trigger because the record has been inserted or updated when the content of the AFTER trigger is executed.

  For example, if we have to write this, we will report an error:

  mysql> delimiter $
  mysql>     CREATE TRIGGER ai_t1
      ->     AFTER INSERT ON t1
      ->     FOR EACH ROW
      ->     BEGIN
      ->         SET NEW.m1 = 1;
      ->     END $
  ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger
  mysql>
  

  You can see that the error displayed in the prompt is not allowed to change the record represented by NEW in the AFTER trigger.

• If an error is encountered during the execution of the BEFORE trigger, the specific statement corresponding to the trigger will not be executed; If an error is encountered during the execution of a specific operation statement, the contents of its corresponding AFTER trigger will not be executed.

  Tip: for tables that support transactions, if an error occurs during the execution of trigger contents or specific operation statements, all statements in the process will be rolled back. Of course, as Xiaobai, we don't know what is a transaction and what is a rollback. These advanced contents are all in how MySQL runs: understanding MySQL from the root

event

Sometimes we want the MySQL server to automatically execute some statements at a certain point in time or every other period of time. At this time, we need to create an event.

Create event

The syntax for creating events is as follows:

CREATE EVENT Event name
ON SCHEDULE
{
    AT A definite point in time| 
    EVERY Expected time interval [STARTS datetime][END datetime]
}
DO
BEGIN
    Specific statement
END

Events support two types of automatic execution:

1. At a certain point in time.

   For example:

   CREATE EVENT insert_t1_event
   ON SCHEDULE
   AT '2019-09-04 15:48:54'
   DO
   BEGIN
       INSERT INTO t1(m1, n1) VALUES(6, 'f');
   END
   

   In this event, we specify that the execution time is' 2019-09-04 15:48:54 '. In addition to directly filling in a time constant, we can also fill in some expressions:

   CREATE EVENT insert_t1
   ON SCHEDULE
   AT DATE_ADD(NOW(), INTERVAL 2 DAY)
   DO
   BEGIN
       INSERT INTO t1(m1, n1) VALUES(6, 'f');
   END
   

   Where DATE_ADD(NOW(), INTERVAL 2 DAY) indicates that the event will be executed two days after the current time.

2. Every once in a while.

   For example:

   CREATE EVENT insert_t1
   ON SCHEDULE
   EVERY 1 HOUR
   DO
   BEGIN
       INSERT INTO t1(m1, n1) VALUES(6, 'f');
   END
   

   Where EVERY 1 HOUR indicates that the event will be executed EVERY 1 HOUR. By default, this method of executing every other period of time will start from the event that created the event and continue to execute indefinitely. We can also specify the start and end time of the event:

   CREATE EVENT insert_t1
   ON SCHEDULE
   EVERY 1 HOUR STARTS '2019-09-04 15:48:54' ENDS '2019-09-16 15:48:54'
   DO
   BEGIN
       INSERT INTO t1(m1, n1) VALUES(6, 'f');
   END
   

   As shown above, this event will start from '2019-09-04 15:48:54' to '2019-09-16 15:48:54', and will be executed every 1 hour.

   Tip: in addition to hour, you can also use YEAR, QUARTER, month, day, hour, minute, WEEK, second and YEAR to indicate the event interval_ MONTH,DAY_HOUR,DAY_MINUTE,DAY_SECOND,HOUR_MINUTE,HOUR_SECOND,MINUTE_SECOND these units, select the time interval units we need according to specific needs.

After creating the event, we don't care. At the specified time, the MySQL server will help us execute it automatically.

View and delete events

Statement to view all events defined in the current database:

SHOW EVENTS;

View the definition of a specific event:

SHOW CREATE EVENT Event name;

Delete event:

DROP EVENT Event name;

These commands are too simple. I won't give you examples

Precautions for event use

By default, the MySQL server will not help us execute events unless we manually enable the function using the following statement:

mysql> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)

mysql>

Tip: event_scheduler is actually a system variable. Its value can also be set through startup parameters or configuration files when MySQL server is started_ The value of the scheduler. These so-called system variables, startup parameters and configuration files are not what Xiaobai needs to master now. Just ignore them