How to use MySQL triggers correctly

The following articles mainly describe how to use MySQL triggers correctly. MySQL Databases reference MySQL triggers in Versions later than 5.0, sometimes, you can use related triggers to maintain data integrity. For example, I have a table ge_element. This table has a region_id that corresponds to the id in the ge_region table. However,

The following articles mainly describe how to use MySQL triggers correctly. MySQL Databases reference MySQL triggers in Versions later than 5.0, sometimes, you can use related triggers to maintain data integrity. For example, I have a table ge_element. This table has a region_id that corresponds to the id in the ge_region table. However,

The following articles mainly describe how to use MySQL triggers correctly. MySQL Databases reference MySQL triggers in Versions later than 5.0, sometimes, you can use related triggers to maintain data integrity. For example, I have a table ge_element.

This table has a region_id that corresponds to the id in the ge_region table. However, region_id can be empty, so foreign key constraints should not be set. When I delete records in the ge_region table, we hope to set the region_id of the record referenced in the ge_element table to 0. Because there is no foreign key constraint on the database, I can only operate in the program, but I do not want to operate through the program, because the table that references region_id may not only have one ge_element, MySQL triggers may be used to set region_id to 0 When deleting records in the ge_region table.

1. Create a trigger statement:

CREATE TRIGGER <触发器名称> <--

{BEFORE | AFTER}

{INSERT | UPDATE | DELETE}

ON <表名称>

FOR EACH ROW

<触发器sql语句>

The trigger must have a name and a maximum of 64 characters. It may be followed by a separator. It is similar to the naming method of other objects in MySQL.

Here I have a habit: using the table name + '_' + the abbreviation of the trigger type. therefore, for table t26, the trigger is BEFORE the event UPDATE (see the following points (2) and (3) (BEFORE), then its name is t26_bu.

You can directly create a trigger in EMS.

2. trigger time:

Before indicates that the MySQL trigger is executed Before the event occurs, and After indicates that the trigger is executed After the event occurs;

3. trigger event:

Three events: insert, update, and delete

4. Relationship between triggers and tables:

A trigger belongs to a table. When the insert, update, and delete operations are performed on the table, the corresponding trigger is activated;

Two different triggers cannot be created for the same operation of the same table.

5. Trigger interval:

The for each row clause notifies the MySQL trigger to execute an action every ROW instead of executing the entire table.

6. SQL statements triggered:

The trigger contains the SQL statement to be triggered: The statement here can be any legal statement, including compound statements, but the statements here are subject to the same restrictions as the functions.

The compound statement (BEGIN/END) is valid.

The Flow-of-control statement (IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE) is also valid.

Variable Declaration (DECLARE) and assignment (SET) are legal.

Allow condition declaration.

Exception Handling statement is also allowed.

However, remember that a function has a restriction: you cannot access a table in the function. Therefore, the following statement is invalid in the function.

7. Permission to create a trigger:

You must have considerable permissions to create a MySQL trigger. When creating a trigger, I am prompted that a super privilege is required;