MySQL trigger details

Trigger.

It can be before the event is triggered, or after the world is triggered.

Trigger creation Syntax:

Official definition:

CREATE    [DEFINER = { user | CURRENT_USER }]    TRIGGER trigger_name trigger_time trigger_event    ON tbl_name FOR EACH ROW trigger_body

You cannot understand it. Here is an intuitive one:

Create trigger
Triggername

After/before insert/update/delete on
Table Name

For each row
// This statement is fixed. MySQL triggers only support the row level.

Begin

SQL statement to be executed

End;

The trigger only supports addition, deletion, and modification.

This is a commodity table, num, indicating inventory.

Mysql> select * from goods; + ---- + -------- + ------- + ----- + | ID | Name | cat_id | price | num | + ---- + -------- + ------- + ----- + | 1 | Apple | 1 | 4999 | 7 | 2 | nexus4 | 2 | 1999 | 5 | 3 | Nokia | 3 | 1222 | 20 | 4 | glory 2 | 2 | 1888 | 5 | 5 | n93 | 3 | 2500 | 10 | 6 | Samsung | 2 | 3000 | 2 | + ---- + -------- + ------- + ----- + 6 rows in Set

This is the order table, num, the number of purchased items.

mysql> select * from orders;+----+---------+---------+-----+| id | user    | good_id | num |+----+---------+---------+-----+|  1 | gaotong |       1 |   1 |+----+---------+---------+-----+1 row in set

What needs to be done now is that the inventory of the goods table changes accordingly when the orders table is added, deleted, and modified.

Since we run the SQL language as early as in trigger, it ends. You must change the delimiter first.

New and Old rows are referenced respectively. Let's see the example:

mysql> delimiter $mysql> create trigger onInsertTrigger    -> after insert on orders    -> for each row    -> Begin    -> update goods set num = num + new.num where id = new.good_id;    -> End$Query OK, 0 rows affected

In this way, the data in the goods table changes accordingly when data is inserted.

Delete and update. Likewise!

Let's take a look at how to use before.

We hope to make some judgment before inserting data. If the number of inserted orders is greater than 10, it is invalid.

Change the order quantity to 5 and then insert it.

First, delete the original trigger.

mysql> drop trigger onInsertTrigger$Query OK, 0 rows affected

mysql> create trigger checkInsert    -> before insert on orders    -> for each row    -> Begin    ->  if new.num > 10 then    ->     set new.num = 5;    ->  end if;    -> update g set num = num-new.num where id = new.good_id;    -> end$Query OK, 0 rows affected