Detailed explanation of insert trigger (insert) in MySQL

INSERT Trigger

After having the previous basic knowledge, we now look at each trigger type supported and their differences.

INSERT triggers execute before or after the INSERT statement is executed. You need to know the following points:

1. In the INSERT trigger code, you can reference a virtual table named NEW to access the inserted row;

2 .In the BEFORE INSERT trigger, the value in NEW can also be updated (allowing the inserted value to be changed);

3. For the AUTO_INCREMENT column, NEW contains 0 before INSERT is executed and contains 0 after INSERT is executed. New automatically generated value.

Here is an example (a practical and useful example). AUTO_INCREMENT columns have values ​​automatically assigned by MySQL. way to determine the newly generated value, but here is a better way:

Input:

create trigger neworder after insert on orders for each row select new .order_num;

Analysis: This code creates a trigger named neworder that follows AFTER INSERT ON orders execution. When inserting a new order into the orders table, MySQL generates a new order number and saves it in order_num. The trigger gets this value from NEW.order_num and returns it. This trigger must execute AFTER INSERT because the new order_num has not been generated before the BEFORE INSERT statement is executed. Using this trigger will always return a new order number for each insertion into orders.

To test this trigger, try inserting a new row as follows:

Input:

insert into orders(order_date,cust_id) values(now(),10001);

Output:

Analysis: orders contains 3 columns. order_date and cust_id must be given, order_num is automatically generated by MySQL, and now order_num is automatically returned.

BEFORE or AFTER? Typically, BEFORE is used for data validation and purification (the purpose is to ensure that the data inserted into the table is indeed the required data). This tip also applies to UPDATE triggers.

[Related recommendations]

1. mysql free video tutorial

2. Introduction to mysql triggers and how to create and delete triggers

3. MySQL character set and collation sequence tutorial

4. Introduction to MySQL character set and collation sequence

5. MySQL transaction-detailed explanation of using retention points and changing the default commit behavior

The above is the detailed content of Detailed explanation of insert trigger (insert) in MySQL. For more information, please follow other related articles on the PHP Chinese website!