Detailed explanation of MySQL trigger trigger instance

Time:2022-1-3

MySQL seems to have supported triggers since version 5.0.2. This blog will introduce triggers. First, let’s talk about conceptual things:

What is a trigger

A trigger is a database object related to a table. It is triggered when the defined conditions are met and executes the statement set defined in the trigger. This feature of trigger can help the application to ensure the integrity of data on the database side.

For example, you now have two tables [user table] and [log table]. When a user is created, you need to insert the created log into the log table. If you don’t use triggers, you need to write program language logic to implement it, but if you define a trigger, The function of trigger is to help you insert a log information in the log table after you insert a piece of data in the user table. Of course, triggers can not only insert, but also modify and delete.

Create trigger

The syntax for creating triggers is as follows:

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_ Name: the name of the trigger
tirgger_ Time: trigger time, before or after
trigger_ Event: trigger event, which is insert, delete or update
tb_ Name: indicates the table on which the trigger is created
trigger_ Stmt: the program body of the trigger, which can be an SQL statement or multiple statements contained in begin and end
Therefore, MySQL can create the following six triggers:
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE

The trigger name parameter refers to the name of the trigger to be created

The before and after parameters specify when execution is triggered, before or after the event

For each row means that the trigger event will be triggered if the operation on any record meets the trigger event

Create a trigger with multiple execution statements

Create trigger trigger name before | after trigger event
On table name for each row
BEGIN
  Execution statement list
END

The execution statement list parameter between begin and end represents multiple statements to be executed, and different statements are separated by semicolons

tips:In general, MySQL defaults to; As an end execution statement, it conflicts with the branch required in the trigger

To solve this problem, you can use delimiter, such as delimiter |, which can turn the end symbol into||

When the trigger is created, delete can be used; To turn the end symbol into;


mysql> DELIMITER ||
mysql> CREATE TRIGGER demo BEFORE DELETE
  -> ON users FOR EACH ROW
  -> BEGIN
  -> INSERT INTO logs VALUES(NOW());
  -> INSERT INTO logs VALUES(NOW());
  -> END
  -> ||
Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER ;

In the above statement, the end symbol is defined as 𞓜, and a trigger is defined in the middle. Once there is a deletion operation that meets the conditions

It will execute the statements in begin and end, and then end with |

Finally, use delimiter; Restore end symbol

tigger_event:

The load data statement inserts the contents of the file into the table, which is equivalent to an insert statement. In general, the replace statement is similar to an insert statement. However, if there is a primary or unique index in the table, if the inserted data is the same as the original primary key or unique, the original data will be deleted and a new data will be added, So sometimes executing a replace statement is equivalent to executing a delete and insert statement.

Triggers can be one SQL statement or multiple SQL code blocks. How to create them?

Delimiter $# changes the delimiter of the statement to$
BEGIN
sql1;
sql2;
...
sqln
END $
DELIMITER ; # Change the statement separator back to the original semicolon ";"
In begin Variables can also be defined in the end statement, but only in begin End internal use:
DECLARE var_ name var_ Type [default value] # defines a variable to specify a default value
SET var_ Name = value # assigns a value to a variable
Use of new and old:

According to the above table, you can use the following format to use the corresponding data:

NEW. Columnname: a column data of a new row
OLD. Columnname: delete a column of data in a row
Having said so much, now let’s create a trigger!

There is now a table as follows:

Users table


CREATE TABLE `users` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
 `add_time` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `name` (`name`(250)) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1;

Logs table:

CREATE TABLE `logs` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `Log ` varchar (255) default null comment 'log description',
 PRIMARY KEY (`Id`)
)Engine = InnoDB default charset = utf8mb4 comment = 'log table';

The requirement is that when a piece of data is inserted into users, a log information will be generated in logs.

Create trigger:

DELIMITER $
CREATE TRIGGER user_log AFTER INSERT ON users FOR EACH ROW
BEGIN
DECLARE s1 VARCHAR(40)character set utf8;
DECLARE s2 VARCHAR(20) character set utf8;# Later, it is found that the Chinese character code is garbled. Set the character set here
SET s2 = " is created";
SET s1 = CONCAT(NEW.name,s2);   # The concat function concatenates strings
INSERT INTO logs(log) values(s1);
END $
DELIMITER ;

Here I use Navicat:

View trigger show triggers statementview trigger information

Tip:

The Navicat I used above is created directly. If you use MySQL front and name, there will be a difference. Let’s delete the trigger just now and test it in MySQL front

drop trigger user_ log;# Delete trigger

Open MySQL front:

 

MySQL front does not need to define the end separator when compiling SQL. The modified SQL can be directly as follows:

#DELIMITER $
CREATE TRIGGER user_log AFTER INSERT ON users FOR EACH ROW
BEGIN
DECLARE s1 VARCHAR(40)character set utf8;
DECLARE s2 VARCHAR(20) character set utf8;
SET s2 = " is created";
SET s1 = CONCAT(NEW.name,s2);   # The concat function concatenates strings
INSERT INTO logs(log) values(s1);
END #$
#DELIMITER ;

Here are a few more words:

tips:The show triggers statement cannot query the specified trigger

View trigger information in the triggers table

SELECT * FROM information_schema.triggers;

The results show the details of all triggers, and the method can query the details of the trigger

SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME=’user_log’;

tips:All trigger information is stored in information_ In the triggers table under the schema database

You can use the select statement to query. If there is too much trigger information, you’d better use trigger_ The name field specifies the query

Back to the above, we have created the trigger, continue to insert data in users and view the data:

Insert into users (name, add_time) values (‘zhou Botong ‘, now());

OK, let’s check the logs table again!

Through the above example, you can see that you only need to insert the user’s information in users, and the log will be automatically recorded in the logs table. This may be the convenience brought to me by the trigger!

Limitations and precautions

Triggers have the following two limitations:

1. The trigger program cannot call the stored program that returns the data to the client, nor can it use the dynamic SQL statement with call statement, but the stored program is allowed to return the data to the trigger program through parameters, that is, it is possible for the stored procedure or function to return the data to the trigger through out or inout parameters, but it cannot call the process that directly returns the data.

2. Statements that start or end a transaction in a display or implicit manner, such as start trans-action, commit or rollback, can no longer be used in triggers.

Note: MySQL triggers are executed in the order of before trigger, row operation and after trigger. If an error occurs in any step, the remaining operations will not be executed. If an error occurs in the operation on the transaction table, it will be rolled back. If an operation on the non transaction table, it will not be rolled back, and the data may be wrong.

summary

Triggers are triggered based on rows, so deletion, addition or modification may activate triggers. Therefore, do not write overly complex triggers or add excessive triggers, which will have a serious impact on data insertion, modification or deletion, and poor portability, Therefore, we must consider it when designing triggers.

Trigger is a special stored procedure. It triggers execution when inserting, deleting or modifying data in a specific table. It has finer and more complex data control ability than the standard functions of the database itself.

Database triggers have the following functions:

1. Safety. You can give users some right to operate the database based on the value of the database.

#You can limit user actions based on time, for example, you are not allowed to modify database data after work and holidays.

#The user’s operation can be limited based on the data in the database. For example, the rise of stock price is not allowed to exceed 10% at a time.

2. Audit. You can track user operations on the database.

#Audit the statements of users operating the database.

#Write the user’s updates to the database into the audit table.

3. Implement complex data integrity rules

#Implement non-standard data integrity checks and constraints. Triggers can produce more complex constraints than rules. Unlike rules, triggers can reference columns or database objects. For example, a trigger can roll back any attempt to eat more futures than its own margin.

#Provide variable defaults.

4. Implement complex non-standard database related integrity rules. Triggers can update related tables in the database in a series. For example, in the authors table, author_ The delete trigger on the code column causes matching rows in other tables to be deleted accordingly.

#Cascade modification or deletion of matching rows in other tables when modifying or deleting.

#When modifying or deleting, set the matching rows in other tables to null values.

#When modifying or deleting, cascade the matching rows in other tables to the default value.

#Triggers can reject or roll back changes that destroy the integrity of the relevant data and cancel transactions that attempt to update the data. This trigger works when an external key that does not match its main key is inserted. For example, in books author_ An insert trigger is generated on the code column if the new value is the same as auths author_ When a value in the code column does not match, the insertion is fallback.

5. Copy the data in the table synchronously and in real time.

6. Automatically calculate the data value. If the data value meets certain requirements, carry out specific processing. For example, if the fund on the company’s account is less than 50000 yuan, send warning data to the financial personnel immediately.

This is the end of this article about the detailed explanation of MySQL trigger instances. For more information about MySQL trigger instances, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!