Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Tutorial on using MySQL triggers

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.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report