MySQL trigger,

MySQL trigger,
Trigger

In Mysql, a trigger is a database object related to table operations. When a specified event occurs on the table where the trigger is located, this object is called, that is, the Operation Event of the table triggers the execution of the trigger on the table.

Sometimes, when changing some data in a table, we also hope that the data in other related tables will also change, and the trigger can meet this requirement. Unfair use of triggers can simplify programs and increase program flexibility.

The trigger creation syntax is as follows:

Create trigger name (trigger name)

After/befor (trigger time)

Insert/update/delete (trigger event. If either of them is entered, other operations will not activate the trigger)

On)

For each row (row-Level Trigger)

Begin

(For SQL statements, you can write multiple statements for the data you want to change. Each statement must be used; it must end)

End;

The preceding SQL statement will be executed. If the trigger is not written, an error will be reported!

In this case, the DELIMITER command is used (delimiter is the DELIMITER), which is a command and does not need to end the statement identifier. Syntax:

DELIMITER; (you can set the end mark of mysql as another symbol, such as; or $ can also be set to multiple lengths)

In the subsequent statements, the trigger ended with a semicolon does not respond, and is considered to be a concluding sentence only when; is encountered. Note: after use, remember to modify it back!

 

Assume that the system has two tables:
Item table goods (item ID gid, item name goods_name, item quantity goods_num)
Order table ord (Order oid, product ID gid, order quantity goods_much)
To create a trigger to automatically update the number of items in the commodity table with the order quantity in the order table, the Code is as follows:

Delmiter $
# Determine whether the inventory is sufficient
Create trigger t1
Before
Insert
On ord
For each row
Begin
# Declare Variables
Declare
Goods_num int;
Select num into goods_num from goods where gid = new. gid;
# Judgment
If new. much> goods_num then
Set new. much = goods_num;
End if;
Update goods set num = num-new. much where gid = new. gid
End $
Delmiter;

MySQL uses declare to define a local variable... The END compound statement must be defined at the beginning of the compound statement,

The NEW keyword is used in the example above, similar to INSERTED and DELETED in the ms SQL Server, MySQL defines NEW and OLD to indicate the table where the trigger is located, the row of data that triggers the trigger.
Specifically:
In an INSERT trigger, NEW indicates the NEW data to be inserted (BEFORE) or AFTER;
In the UPDATE trigger, OLD is used to indicate the original data to be or has been modified, and NEW is used to indicate the NEW data to be or has been modified;
In the DELETE trigger, OLD is used to indicate the original data to be deleted or deleted;