Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the principle of MySQL trigger. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

1. The concept of trigger

Trigger is a method that MySQL provides to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by the program, nor started manually, but triggered by events. For example, when an operation is performed on a table (insert,delete, update), it is activated. Baidu encyclopedia

Above is the concept of trigger given by Baidu. I understand the concept of trigger, that is, you execute a sql statement, and the execution of this sql statement automatically triggers the execution of other sql statements. It's as simple as that.

Simple description: sql1- > trigger-> sqlN. One sql triggers multiple sql

2. Four elements of trigger creation

(1) Surveillance location (table)

(2) Monitoring events (insert/update/delete)

(3) trigger time (after/before)

(4) trigger event (insert/update/delete)

3. Create a trigger

Demand: when placing an order, the inventory of the corresponding goods should be reduced accordingly, that is, the inventory will be reduced as many as you buy.

Order form: ord

Commodity list: goods

First, create the table and add a few pieces of data:

Create table goods (gid int, name varchar (20), num smallint); create table ord (oid int, gid int, much smallint); insert into goods values (1 recordable catenary par 40); insert into goods values (2 minary pigmented pencils 63); insert into goods values (3 pint pigmented page87)

Then analyze it according to the four elements created by the trigger:

Who to monitor: ord (order form)

Monitor actions: insert (insert operation)

Trigger time: after (triggered after insert operation)

Trigger event: update (trigger update operation)

Finally, create the trigger:

Create trigger T1 afterinsert on ordfor each rowbegin update goods set num=num-2 where gid = 1nterend $

Analysis: the name of the trigger is T1, the trigger time is after, the monitoring action is insert, the ord table is monitored, and for each row is finally discussing. Just remember here that trigger events are written between begin and end. Here is a update statement. It means that no matter what order I place, I will subtract 2 from the inventory of goods numbered 1.

Note: do not run the above code yet, because the end of execution flag of mysql defaults to;. If you run the above sql statement, mysql will automatically stop execution when it encounters;, and then the end statement will not be executed. So we need to change the end identifier of mysql to other characters, usually $or $, and here we choose $as the end of execution. Use the following statement to modify the end identity of MySQL execution.

Delimiter $/ / sets the MySQL execution end flag, which defaults to

4. View and delete existing triggers

(1) check the existing trigger: show triggers

(2) Delete an existing trigger: drop trigger triggerName

5. Reference row variables in triggers

(1) after performing the insert operation on the trigger target, there will be a new line. If you need to use the variable of this new line in the trigger event, you can use the new keyword to indicate it.

(2) after performing the delete operation on the trigger target, there will be an old line. If you need to use the variables of this old line in the trigger event, you can use the old keyword to indicate it.

(3) after performing the update operation on the trigger target, the original record is the old row, and the new record is the new row. You can use the new and old keywords to operate respectively.

Reduce the inventory of the corresponding items when you order now, and create a trigger:

Create trigger t2afterinsert on ordfor each rowbegin update goods set num=num-new.much where gid=new.gid;end$

When deleting an order, increase the inventory of the modified items accordingly, and create a trigger:

Create trigger t3afterdeleteon ordfor each rowbegin update goods set num=num+old.much where gid=old.gid;end$

When the purchase quantity of the update order modifies the inventory of the modified item, create a trigger:

Create trigger t4before updateon ordfor each rowbegin update goods set num=num+old.much-new.much where gid = new.gid;end$

6. The difference between after and before

The after operation, which executes the trigger event only after the monitoring action is performed

The before operation, in which the trigger event is executed before the monitoring action is performed

There is no difference between the two in general triggers, but sometimes there are differences, such as:

Demand: after the user places an order in excess of inventory, the order quantity will be modified so that the maximum value of the order quantity is the same as the inventory quantity analysis: first judge the order quantity > inventory quantity, and then change the order quantity to inventory quantity

Create a trigger:

Create trigger t5beforeinsert on ordfor each rowbegin declare restNum int; select num into restNum from goods where gid=new.gid; if new.much > restNum then set new.much = restNum; end if; update goods set num=num-new.much where gid=new.gid;end$

Note: if after is used here, an error will be reported. If after is used, the insert operation, that is, the insert order operation, will be performed first, and then the order quantity and inventory quantity will be judged to get the new order quantity, but the order operation has been executed, so the error will be reported. The before operation must be used here.

7. What does for each row do?

In oracle triggers, triggers are divided into row triggers and statement triggers.

For example:

Create trigger tnafterupdateon xxtablefor each row # each row is affected and the trigger event is executed, which is called the row trigger begin sqlN;end$

Execute:

Update xxtable set xxx=xxx where id > 100

The modification assumes 100 lines, so how many times will sqlN be triggered? Answer: it will trigger 100 times.

Expand:

In oracle, if for each row does not write, the trigger event is executed only once, no matter how many rows are affected by the update statement at a time.

For example: 1 person placed an order, bought 5 items, insert 5 times, can use line-level trigger, modify inventory 5 times; term sentence trigger trigger, insert a delivery reminder.

The above is the principle of the MySQL trigger shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.