MySql transaction trigger (Instance)

MySql transaction trigger (Instance)

1. Connection Query
Union connects multiple select statements.
Join query:
When the expected results are no longer the same table, we need to use join queries.
Syntax:
A field in table1 join table2 on table1 = a field in table2
Condition: product category id in the item table = Category id in the category table

When a table is used for multiple times in a query statement or the table name is particularly long, we recommend that you add an alias to the table.
Mysql> selectgoods_name, shop_price, cat_name from ecs_goods as g join ecs_catego
Ry as c on g. cat_id = c. cat_id whereg. shop_price >=1000;

Join classification:
Left join query leftjoin: the query result is displayed as long as there are records on the left, and null is not displayed on the right.
Right join query rightjoin
Innerjoin query: the query result is data of both tables.


Transaction:
What is a transaction?
A transaction is a logical group of operations that constitute each unit of this group of operations. If it fails or fails, it is a transaction.
Note: mysql DATA supports transactions, but must be an innoDB Storage engine.
Solve this problem:
Mysql transactions solve this problem. Because of the transaction characteristics of mysql, this set of operations is required, either completely successful or all failed, which avoids the success of an operation. Conducive to data security

How to use:
(1) Before executing an SQL statement, start transaction is enabled for the transaction;
(2) normal execution of our SQL statements
(3) When the SQL statement is executed, there are two situations:
1. All are successful. We need to submit the impact of SQL statements on the database to the database, committ
2. If some SQL statements fail, we execute rollback to quickly cancel database operations.
Php code:



After the transaction is started, as long as no commit is executed, the SQL statement will not affect the real database.
The actual database will be affected only after the commit is executed.

Trigger:
A trigger is a database program that monitors a behavior of a data table. Once the behavior of a data table occurs, the corresponding SQL statement is executed immediately.

Syntax structure of the trigger:
Name of the create trigger: name of the table on which the trigger event is listened for eachrow

Trigger event composition:; consists of two parts:
Trigger event occurrence time ----- is the behavior of the monitored table after before is commonly used after
Trigger execution content: add, delete, modify

When creating an order table, note that order is sorted by a keyword in mysql. To avoid errors, we can add backquotes to indicate that this is not a keyword.

How to Use triggers:
Case study:
Once an order is generated, the corresponding inventory table must be subtracted from the corresponding data.
(1) create a commodity table and an order table


Order table


(2) bind a trigger to the order table. Once an order is added to the order table, execute the SQL statement immediately and subtract the corresponding data from the inventory table.


Now there is a problem:
After each order is placed, the inventory table is replaced by the corresponding quantity, but our requirement is: the number of orders placed by the user, the inventory table minus several inventories

How to Use trigger data in a trigger?
What is the trigger data: the amount of data that the user purchases in the order table
The new Keyword indicates the number of new orders in the order table.
The old keyword indicates the old record (past record, number of canceled orders)
Use of new:


Use of old:
I bought a few items in the old history but didn't want them. I canceled the order.
The quantity to be added to the inventory after the order is canceled


Use of new and old in update
The requirements are as follows:
I have purchased 5 samsung mobile phones. Now I want to buy 10 phones. I need to update the order form.
At this time, the trigger needs to listen to the update action.
Cancel the previous order and place the order again.


What is a trigger?
It is used to listen to a behavior (insert, delete, update) of a data table. Once this behavior occurs, the corresponding SQL statement is executed immediately.
 
Delete trigger:
Name of the drop trigger