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


Sql sentence ; # One or more sentences ,insert/update/delete Within the scope


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 :