Use of MySQL triggers

Triggers can cause other SQL code to run before or after a statement is executed. Triggers can read what data was changed by the triggering statement, but there is no return value. Therefore, you can use triggers to enforce business logic constraints without writing corresponding code in the application.

From the above description, we can see that triggers can simplify the logic of the application and improve performance. This is because using triggers reduces the number of interactions between the application and the server. Also, triggers help to automatically update normalization and statistics. For example, we can use triggers to automatically count the total transaction amount, number of orders, and average order value. However, the application scenarios of MySQL triggers are also very limited. If you have used triggers from other database products, do not assume that MySQL can achieve the same functions. For example:

• Each data table can have only one trigger for a single event, which means that for an event like AFTER INSERT, there cannot be more than one trigger at the same time.
• MySQL only supports row-level triggers, which means that you can only use triggers in FOR EACH ROW mode instead of the entire SQL statement, which is inefficient for operations on large amounts of data. MySQL triggers can only be written in the following form:

CREATE TRIGGER trigger name BEFORE | AFTER trigger event ON table name FOR EACH ROW
BEGIN
    Execute statement list;
END

The execution statement list supports single or multiple statements. The following is an example of multiple statements:

DELIMITER $$
CREATE TRIGGER user_create_log AFTER INSERT ON t_users FOR EACH ROW
BEGIN
DECLARE log_info VARCHAR(40) character set utf8;
DECLARE description VARCHAR(20) character set utf8;#Later, it was found that the Chinese character encoding was garbled, so the character set was set here SET description = " is created";
SET log_info = CONCAT(NEW.user_name, description); #CONCAT function can concatenate strings INSERT INTO logs(log) values(log_info);
END $$

DELIMITER ;

• Triggers can cause the actual work performed by the server to be unpredictable, and a simple statement can cause the server to do a lot of invisible work. For example, if a trigger updates a dependent table, this may result in doubling the number of rows affected.
• Triggers are difficult to debug, and once they are introduced, performance bottlenecks are difficult to analyze.
• Triggers can cause potential lock waits and deadlocks. If the trigger fails, the source query will also fail. This type of play can be difficult to detect if you are not aware of the trigger.
  

The biggest limitation among most is the design of FOR EACH ROW, which sometimes prevents triggers from being used to maintain statistics and cache tables, as this can be slow. The main reason for using triggers is that they can consistently maintain data consistency compared to scheduled synchronous updates. Triggers also cannot guarantee atomicity. For example, a trigger that updates a MyISAM table cannot be rolled back if an error occurs in the source SQL statement. Furthermore, the triggers themselves may be faulty. If we use AFTER UPDATE based on the MyISAM data table to update another table. If the trigger has an error that causes the operation on the second table to fail, the operation on the first table will not be rolled back.

InnoDB trigger-related operations, including source statements, are all in the same transaction and therefore meet atomicity requirements. However, if you use InnoDB triggers to verify data consistency with another table, this may lead to incorrect results if you are not careful. For example, if you need to use triggers to simulate foreign keys, you can use a BEFORE INSERT trigger to verify whether a corresponding record exists in another table. However, if you do not use SELECT FOR UPDATE when the trigger reads data from another table, incorrect results may occur due to concurrency issues. Although the trigger has some flaws, it doesn't mean it can't be used. Conversely, triggers can also be useful in their own right, especially for constraints, system maintenance tasks, and keeping statistics up to date.

You can also use triggers to record changes in data rows. In this way, even records of offline manual database operations (such as repairing incorrect data) can be recorded. However, it is important to be careful when inserting data into other auto-increment primary key tables. This may cause problems for replicated statements because the auto-increment values ​​are different for two identical replicas.

Conclusion:

Triggers can play their advantages in limited situations, such as statistical data, data table change logs, etc. However, there are also some defects. For example, the update of large amounts of data will reduce efficiency due to row-by-row triggering. Also, the MyISAM engine cannot guarantee atomicity. Therefore, it depends on the application scenario whether there is a trigger.

The above is the detailed content of the use of MySQL triggers. For more information about MySQL triggers, please pay attention to other related articles on 123WORDPRESS.COM!