Mysql tutorial-triggers

Trigger 1. mysql trigger scenario description scenario settings. what will happen when we click buy? There are two existing table item table numbers (id) name (name) price (price) stock (stock) 1F2 fighter 100001002 Farah... "> <LINKhref =" http://www.php100.com/

 

Trigger

1. description of mysql triggers

Scenario settings. what will happen when we click buy?

　　

 

The following two tables are available:

Item table

Id name price stock)

1F2 fighter 10000100

2 Ferrari 80070

3 Aircraft carriers 500020

4 Sanqi transportation 100050

Order table

No. (id) Item No. (tid) purchase quantity (num) order time (order_time)

We want to buy five F2 fighters now. what do we need to do next order?

Traditional practices:

Insert into ord (tid, num) values (1, 5 );

Update traffic set stock = stock-5 where id = 1;

New method:

We can use a trigger to trigger a trigger !!

2. mysql trigger usage: trigger

　　

 

2.1 Trigger four elements:

Location: (table, table ),

Monitored events: (insert, delete, update)

Time: (before/after)

Events triggered: (insert, delete, update)

2.2 Syntax for trigger creation:

　　

 

Note: the mysql separator to be changed before writing the trigger.

How do I pass a value between a monitoring event and a trigger event?

Requirement: now we want to purchase 10 Ferrari vehicles. the trigger in the commodity table should be written as follows:

# Product table triggers

Delimiter $

Create triggter tg1

After // event triggered after the order is placed

Insert // monitor insert events

On order // monitor order table

For each row

Begin

Update traffic set stock = stock-new, num where id = new id;

End $

Operations on the order table:

Insert into (tid, num) values (2, 10 );

The following analysis is applicable to the order table.

For insert,

　　

 

New and old relationships

New indicates a newly inserted row,

How do I reference tid and num?

New. tid

New. num

For delete:

　　

 

For update

　　

 

Requirement: I purchased 10 Ferrari vehicles first, then changed the number to 5, and wrote the trigger;

# Product table triggers

Mysql> delimiter $

Mysql> create trigger tg3

-> After

-> Update

-> On ord

-> For each row

-> Begin

-> Update traffic set stock = stock + old. num-new. num where id = new. tid;

-> End $

[About before]

Is there any before condition.

After: triggered After a monitoring event occurs. the triggered event is later than the monitoring event.

Before: triggered Before a monitoring event occurs. the triggering event is earlier than the monitoring event.

Requirement: if the number of orders exceeds 10, it is regarded as a malicious order and only allows it to buy 10 orders.

# Product table triggers

Mysql> delimiter $

Mysql> create trigger tg4

-> Before

-> Insert

-> On ord

-> For each row

-> Begin

-> If new. num> 10 then

-> Set new. num = 10;

-> End if;

-> Update traffic set stock = stock-new. num where id = new. tid;

-> End $

3. trigger application scenarios

1. when adding or deleting records to or from a table, you must perform synchronization in the relevant table.

For example, when an order is generated, the inventory of the purchased goods decreases accordingly.

2. when the value of a column in the table is related to the data in other tables.

For example, when a customer pays off the arrears, you can use the design trigger when generating the order to determine whether the customer's accumulated arrears have exceeded the maximum.

3. when a table needs to be tracked.

For example, when a new order is generated, you need to notify the relevant personnel to handle it in a timely manner. in this case, you can design and add a trigger in the order table for implementation.