Triggers are a special kind of transactions , Can monitor certain data operations (insert/update/delete), And trigger relevant operations (insert/update/delete).
Look at the following events ：
Complete the logic of placing orders 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 say , insert ---> Draw update
Using triggers can solve the above problems .
We can monitor the changes of a certain table , When something changes , Trigger an action .
Syntax for Creating Triggers
Create trigger triggerName
After/before insert/update/delete on Table name
For each row # This sentence is fixed
Begin
Sql sentence ; # One or more sentences ,insert/update/delete Within the scope
End;
Delete trigger syntax :
Drop trigger Trigger Name
View triggers
Show triggers
How to reference the value of a row in a trigger
about insert for , New row use new To show ,
The value of each column in the row , use new. Column name .
about delete Say , There was originally a line , Later deleted ,
Want to reference the deleted line , use old, To show , old. Column name , You can reference the value in the deleted line .
about update Say ,
Modified row ,
Data before modification , use old To show , old. The column name refers to the value in the row before it is modified
Modified data , use new To show , new. The column name refers to the value in the row after being modified
In the trigger after and before Differences between
After Is to complete the increase of data first , Delete , Change and trigger again ,
The triggered statement is later than the monitored increment , Delete , change , Cannot affect the previous addition, deletion and modification actions .
Before Is to complete the trigger first , Add, delete and modify again ,
The triggered statement precedes the monitored increment , Delete , Change occurrence , We have a chance to judge , Modify upcoming actions .
classic case :
For orders placed , Make judgment , If the quantity of the order > 5 , Consider it a malicious order ,
Force the quantity of ordered goods to 5
See which triggers ：
Technology