Triggers are used to automatically trigger the execution of other SQL code when MySQL executes insert, update, or delete statements. Other SQL code can be triggered to run either before or after the statement is executed. A trigger can read what data the triggering statement changed, but it does not return a value. Triggers can therefore be used to enforce constraints on business logic without the need to write corresponding code in the application.

As you can see from the above description, triggers simplify application logic and improve performance by reducing the number of interactions between the application and the server. At the same time, triggers help accomplish automatic update normalization and statistics. For example, we can use triggers to automatically count transaction order totals, order numbers, and average customer unit prices. However, MySQL triggers are very limited in use. If you have used triggers from other database products, do not assume that MySQL can achieve the same functionality. For example:

  • There can only be one trigger per table for a single event, which means there can be no more than one trigger at a time for events like AFTER INSERT.
  • MySQL only supports row-level triggers, meaning you can only use triggers as FOR EACH ROW rather than the entire SQL statement, which can be inefficient FOR large data operations. MySQL triggers can only be written as follows:
CREATE TRIGGERTrigger name BEFORE|AFTER eventONThe name of the tableFOR EACH ROW
BEGINExecute statement list;END
Copy the code

Execute statement lists support single or multiple statements. Here 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 setutf8; # The Chinese character encoding is found to be garbledSET description = " is created";
SET log_info =CONCAT(NEW.user_name, description); The function CONCAT concatenates stringsINSERT INTO logs(log) values(log_info);
END $$

DELIMITER ;
Copy the code
  • Triggers can cause unpredictable work actually performed by the server, and a simple statement can cause the server to do a lot of invisible work. For example, if a trigger updates an associated table, it may cause the number of affected rows to double.
  • Triggers are difficult to debug, and performance bottlenecks can be difficult to analyze once triggers are introduced.
  • Triggers cause potential lock waits and deadlocks. If the trigger fails, the source query also fails. This kind of play can be hard to spot without being aware of triggers.

Of most limitations, the biggest is the design of FOR EACH ROW, which sometimes prevents triggers from being used to maintain statistics and cache tables because this can be slow. The main reason for using triggers is that they can consistently keep data consistent rather than synchronizing updates regularly. Triggers don’t guarantee atomicity. For example, the trigger that updates the MyISAM data table cannot be rolled back if the source SQL statement fails. Also, triggers themselves can be all wrong. If we use AFTER UPDATE to UPDATE another table based on MyISAM table. If the trigger has an error that causes the second table operation to fail, the first table operation will not be rolled back.

InnoDB’s trigger-related operations, including source statements, are all in the same transaction and therefore atomic. However, using InnoDB triggers to check data consistency with another table can result in incorrect results if you are not careful. FOR example, if you need to simulate a foreign key with a trigger, you can use a BEFORE INSERT trigger to verify that a record exists FOR another table, but if you do not use SELECT FOR UPDATE when the trigger reads data from another table, you may get wrong results due to concurrency issues. Triggers have some flaws, but that doesn’t mean they can’t work. Conversely, triggers themselves can be useful, especially for constraints, system maintenance tasks, and keeping statistics up to date.

Triggers can also be used to record changes in data rows. This allows even offline manual operations on the database, such as fixing bad data, to be recorded. However, be careful about inserting data into other auto-increment primary key tables. This can be problematic for duplicative statements because auto-increment does not vary between two identical replicas.

Conclusion: Triggers can be useful in a limited number of situations, such as statistics, data table change logs, and so on. However, there are also some drawbacks, such as the efficiency of large data updates being triggered row by row. Also, the MyISAM engine cannot guarantee atomicity. Therefore, it depends on the application scenario if there are triggers.