Mysql advanced triggers and mysql triggers

Mysql advanced triggers and mysql triggers

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

Check the following events:

Complete the ordering and inventory reduction Logic

Insert into o (gid, num) values (2, 3); // Insert statement

Update g set goods_num = goods_num-3 where id = 2; // Update process

 

The two logics can be regarded as a whole, or insert ---> leads to update.

 

 

Trigger can be used to solve the above problem.

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

Create trigger syntax

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 range of insert/update/delete

End;

 

Syntax for deleting a trigger:

Drop trigger name

 

View triggers

Show triggers

How to reference the value of a row in a trigger

For insert, the new row is represented by new,

The value of each column in the row is represented by the new. Column name.

 

For delete, a row was originally deleted,

To reference the deleted row, use old to represent the column name of old., You can reference the value in the deleted row.

 

For update,

Modified row,

The data before the modification is represented by old, and the old. Column Name Reference is the value in the row before the modification.

The modified data is represented by new. The new. Column Name Reference is the value in the row after the modification.

Differences between after and before in triggers

After is the process of adding, deleting, modifying, and triggering data first,

The statement that is triggered is later than the addition, deletion, and modification actions of the monitor.

 

Before is triggered first, and then added and deleted,

The triggered statement is prior to the addition, deletion, and modification of monitoring. We have the opportunity to determine the upcoming modification operation.

 

 

Typical cases:

Judge the order. If the order quantity is greater than 5, it is regarded as a malicious order,

Change the number of Ordered items to 5

View which triggers: