In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
The following mainly brings you tutorials on using MySQL triggers. I hope these contents can bring you practical use, which is the main purpose of this article for editing tutorials on using MySQL triggers. All right, don't talk too much nonsense, let's just read the following.
MySQL includes support for triggers. A trigger is a database object related to a table operation. When a specified event occurs on the table where the trigger is located, the object will be called, that is, the execution of the trigger on the table's action event trigger publication.
Create trigger
In MySQL, the syntax for creating a trigger is as follows:
The code is as follows:
CREATE TRIGGER trigger_name
Trigger_time
Trigger_event ON tbl_name
FOR EACH ROW
Trigger_stmt
Where:
Trigger_name: identifies the trigger name, which is specified by the user
Trigger_time: identifies the trigger time. Value: BEFORE or AFTER
Trigger_event: identifies the trigger event, with values of INSERT, UPDATE, or DELETE
Tbl_name: identifies the name of the table on which the trigger is created, that is, on which table the trigger is created
Trigger_stmt: trigger program body, which can be a SQL statement or multiple statements contained in BEGIN and END.
It can be seen that six kinds of triggers can be established, namely: BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE, AFTER INSERT, AFTER UPDATE, AFTER DELETE.
Another limitation is that two triggers of the same type cannot be created on a table at the same time, so a maximum of six triggers can be created on one table.
Trigger_event detailed explanation
In addition to defining the basic operations of INSERT, UPDATE and DELETE, MySQL also defines LOAD DATA and REPLACE statements, which can also cause triggers of the above 6 types.
The LOAD DATA statement is used to load a file into a data table, which is equivalent to a series of INSERT operations.
Generally speaking, REPLACE statements are very similar to INSERT statements, except that when there is an primary key or unique index in a table, if the inserted data is the same as the original primary key or unique index, the original data will be deleted first, and then a new data will be added, that is to say, a REPLACE statement is sometimes equivalent to one.
The INSERT statement is sometimes equivalent to a DELETE statement plus an INSERT statement.
INSERT trigger: activates a trigger when a row is inserted, which may be triggered by INSERT, LOAD DATA, or REPLACE statements
UPDATE type trigger: activates a trigger when a row is changed, which may be triggered by a UPDATE statement
DELETE trigger: activate the trigger when a row is deleted, which may be triggered by DELETE or REPLACE statements.
BEGIN... END detailed explanation
In MySQL, BEGIN... The syntax of the END statement is:
BEGIN
[statement_list]
END
Where statement_list represents a list of one or more statements, and each statement in the list must end with a semicolon (;).
In MySQL, the semicolon is the identifier for the end of the statement, and the semicolon indicates that the statement has ended and the MySQL can begin execution. Therefore, the interpreter starts execution when it encounters a semicolon in statement_list, and then reports an error because no END matching BEGIN is found.
The DELIMITER command (DELIMITER is the delimiter, the meaning of the delimiter) is used, which is a command that does not require the end of the statement flag, and the syntax is:
DELIMITER new_delemiter
New_delemiter can be set to a symbol of one or more lengths, and the default is a semicolon (;), which we can change to other symbols, such as $:
DELIMITER $
After that, the statement ends with a semicolon, and the interpreter will not react until it encounters $. It is considered the end of the statement. Note that after using it, we should also remember to modify it back.
A complete example of creating a trigger
Suppose there are two tables in the system:
Class table class (class number classID, number of students in the class stuCount)
Student form student (student number stuID, class number classID)
To create a trigger to automatically update the number of students in the class table as students are added, the code is as follows:
The code is as follows: download address springmvc+mybatis+spring Integration SSM
DELIMITER $
Create trigger tri_stuInsert after insert
On student for each row
Begin
Declare c int
Set c = (select stuCount from class where classID=new.classID)
Update class set stuCount = c + 1 where classID = new.classID
End$
DELIMITER
Variable detailed explanation
DECLARE is used in MySQL to define a local variable, which can only be used in BEGIN. Used in END compound statements and should be defined at the beginning of the compound statement
That is, before other statements, the syntax is as follows:
DECLARE var_name [,...] Type [DEFAULT value]
Where:
Var_name is the variable name, and like the SQL statement, the variable name is case-insensitive; type is any data type supported by MySQL; multiple variables of the same type can be defined at the same time, separated by commas; the initial value of the variable is NULL, and if necessary, you can use the DEFAULT clause to provide the default value, and the value can be specified as an expression.
Use the SET statement to assign variables, and the syntax is as follows:
SET var_name = expr [, var_name = expr].
Detailed explanation of NEW and OLD
The NEW keyword is used in the above example, which is similar to INSERTED and DELETED in MS SQL Server. NEW and OLD are defined in MySQL to represent
In the table where the trigger is located, the row of data that triggered the trigger.
Specifically:
In INSERT triggers, NEW is used to represent new data that will be (BEFORE) or has been (AFTER) inserted.
In UPDATE triggers, OLD is used to represent the original data that will or has been modified, and NEW is used to represent the new data that will or has been modified.
In DELETE triggers, OLD is used to represent the original data that will be or has been deleted
Usage: NEW.columnName (columnName is a column name of the corresponding data table)
In addition, OLD is read-only, while NEW can use SET assignment in the trigger so that it does not trigger the trigger again, resulting in a circular call (such as adding "2013" before each student is inserted).
View trigger
Like viewing a database (show databases;) and viewing a table (show tables;), the syntax for viewing triggers is as follows:
SHOW TRIGGERS [FROM schema_name]
Where schema_name is the name of Schema, and Schema and Database are the same in MySQL, that is, you can specify the database name, so that
There is no need to USE database_name; first.
Delete trigger
Like deleting a database and deleting a table, the syntax for a delete trigger is as follows:
DROP TRIGGER [IF EXISTS] [schema_name.] trigger_name
Execution order of triggers
The database we build is generally an InnoDB database, and the tables established on it are transactional tables, that is, transaction-safe. At this point, if the execution of the SQL statement or trigger fails, MySQL rolls back the transaction, including:
① if the execution of the BEFORE trigger fails, the SQL cannot execute correctly.
When ② SQL execution fails, the AFTER type trigger does not fire.
Trigger of type ③ AFTER fails to execute and SQL rolls back
For the above tutorial on the use of MySQL triggers, you do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 209
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.