MySQL advanced triggers

Original: MySQL Advanced trigger

A trigger is a special kind of transaction that can monitor a data operation (Insert/update/delete) and trigger related actions (Insert/update/delete).

Look at the following events:

The logic of completing 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;// updating process

These two logic can be considered as a whole , or Insert---> attract update

Use triggers to solve the above problems .

We can monitor the change of a table and trigger an action when a change occurs .

Syntax for creating triggers

Create Trigger Triggername

After/before insert/update/delete on table name

For each row # This sentence is fixed

Begin

SQL statements ; # One or more sentences , within the range of Insert/update/delete

End;

Syntax for deleting triggers :

Drop trigger Trigger name

View triggers

Show triggers

How to reference a row's value in a trigger

For insert , the new line is represented by new ,

The value of each column in the row , with new. A column name to represent .

For Delete , There was a row , which was later deleted ,

To refer to the deleted line , use old, to represent , old . Column name , You can reference the value in the deleted row .

For update ,

The line that was modified ,

Pre-modified data , old to indicate . Column names refer to values in rows before they are modified

The modified data , denoted by new , new. value in row after column name reference has been modified

the difference between after and before in a trigger

after is the first complete data increase , Delete , change the trigger ,

The trigger statement is later than the monitoring of the increase , Delete , change , can not affect the previous additions and deletions to change the action .

Before is the first to complete the trigger , then increase the deletion ,

The trigger statement is preceded by the increase , deletion , and change of the monitor, and we have the opportunity to judge and Modify the action that will occur. .

Typical cases :

For the order to be placed, judgment , If the number of orders > 5, is considered a malicious order ,

Forced to change the quantity of goods ordered to 5

To see which triggers:

MySQL advanced triggers