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: