Triggers can trigger other SQL code to run before or after a statement is executed. The trigger can read what data has been changed by the trigger statement, but returns no value. Triggers can therefore be used to enforce the constraints of the business logic without having to write the corresponding code in the application.

As you can see from the above description, triggers can simplify application logic and improve performance because using triggers reduces the number of interactions between the application and the server. At the same time, triggers help to automatically update the normalized and statistical data. For example, we can use triggers to automatically count the total value of a transaction order, the number of orders, and the average customer price. However, MySQL triggers have very limited applications. If you have used triggers from other database products, don’t assume that MySQL can do the same thing. For example:

There can be only one trigger per table event, which means that for an event such as an AFTER INSERT, there cannot be more than one trigger at a time. MySQL only supports row-level triggers, which means you can only use triggers as FOR EACH ROW instead of the entire SQL statement, which can be inefficient FOR large data operations. MySQL TRIGGER can only write according to the following: CREATE name BEFORE the TRIGGER flip-flop | AFTER triggers ON the table name FOR EACH ROW of the BEGIN

Execute statement list;

The END execution statement list supports a single or multiple statement. Here is an example of a multiple statement:

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 setwww.sangpi.com utf8; SET description = “is created”; SET log_info = CONCAT(NEW.user_name, description); INSERT INTO logs(log) values(log_info); END $$

DELIMITER ; Triggers can make it unpredictable what the server is actually doing, and a simple statement can cause the server to do a lot of invisible work. For example, if a trigger updates a related table, it may double the number of affected rows. Triggers are difficult to debug and, once they are introduced, difficult to analyze performance bottlenecks. Triggers cause potential lock waits and deadlocks. If the trigger fails, the game source query also fails. This type of play can be difficult to spot without being aware of the existence of triggers. Of most of the limitations, the biggest is the design FOR EACH ROW, which sometimes prevents triggers from being used to maintain statistics and cache tables because it can be slow. The main reason for using triggers is that they keep data consistent compared to timed synchronous updates. Nor does a trigger guarantee atomicity. For example, a trigger that updates a MyISAM data table cannot be rolled back after the source SQL statement fails. Also, the triggers themselves may be just errors. If we use AFTER UPDATE to UPDATE another table based on MyISAM data 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 flip-flop-related operations, including source statements, are all in the same transaction, so they are atomic. However, if InnoDB’s triggers are used to verify data consistency with another table, this can lead to incorrect results if you are not careful. For example, if a trigger needs to be used to simulate a foreign key, a BEFORE INSERT trigger can be used to verify the existence of a corresponding record FOR another table, but if the trigger does not use SELECT FOR UPDATE when reading data from another table, the result may be incorrect due to concurrency issues. Just because the trigger has some drawbacks doesn’t mean it can’t be used. Conversely, triggers themselves can be useful, especially for constraints, system maintenance tasks, and keeping statistics up to date.

Triggers can also be used to log changes in data rows. This allows records to be recorded even when manually manipulating the database offline, such as fixing erroneous data. However, be careful when inserting data into other self-incrementing primary key tables, which can be problematic for replicative statements because the value of the self-incrementing is not the same for two copies.

Triggers can be useful for limited purposes, such as statistics, table change logs, and so on. However, there are some drawbacks, such as large data volume updates that are triggered row by row, which can be inefficient. Also, the MyISAM engine does not guarantee atomicity. Therefore, it depends on whether the application scenario should have a trigger.