Mysql advanced triggers

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

Look at the following events:

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.

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

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

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

Check out which triggers: