Home > Database > Mysql Tutorial > body text

Mysql advanced triggers

高洛峰
Release: 2016-12-02 13:51:17
Original
994 people have browsed it

Trigger is a special type of transaction that can monitor certain data operations (insert/update/delete) and trigger related operations (insert/update/delete).

Mysql advanced triggers

Look at the following events:

Mysql advanced triggers

Complete the logic of placing an order and reducing inventory

Insert into o (gid,num) values ​​(2,3); // Insert statement

Update g set goods_num = goods_num - 3 where id = 2;//Update process

These two logics can be regarded as a whole, or, insert ---> leads to update

Using triggers can solve the above problem Question.

We can monitor changes in a certain table and trigger an operation when a certain change occurs.

Mysql advanced triggers

Syntax for creating a trigger

Create trigger triggerName

After/before insert/update/delete on Table name

For each row #This sentence is fixed

Begin

Sql statement; # One or more sentences, within the scope of insert/update/delete

End;

The syntax of delete trigger:

Drop trigger trigger name

View triggers

Show triggers

Mysql advanced triggers

How to reference the value of the row in the trigger

For insert, the new row is represented by new,

in the row The value of each column is represented by new. column name.

For delete, there was originally a row that was later deleted.

If you want to refer to the deleted row, use old, to represent, old. column name, you can refer to the value in the deleted row.

For update, the

modified row,

the data before modification, is represented by old, old. The column name refers to the row before it was modified. The value of

The modified data is represented by new. The value in the row after new. The column name refers to the modified value

Mysql advanced triggers

Mysql advanced triggers

The difference between after and before in the trigger

After completes the data first Add, delete, and modify before triggering. The statement triggered by

is later than the monitored addition, deletion, and modification, and cannot affect the previous addition, deletion, and modification actions.

Before is the statement that triggers first, then adds, deletes, and changes,

Before the addition, deletion, and modification of monitoring occurs, we have the opportunity to judge and modify the upcoming operation.

Typical case:

Judge the order placed. If the quantity of the order > 5, then Thinking it is a malicious order,

forcefully change the quantity of goods ordered to 5

Mysql advanced triggers

Check out which triggers:

Mysql advanced triggers

Mysql advanced triggers

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template