MySQL Trigger summary

1. Create a new two-sheet table first

CREATE TABLE Tb_goods (

ID Int (one) not null primary key,

Goods_name varchar (+) NOT NULL,

Num INT (one) default ' 0 ');

CREATE TABLE Tb_order (

OrderID Int (one) not null primary key,

goods_id Int (11),

Onum int)

2. Trigger syntax

Create Trigger Tname

After/before Insert/update/delete on Tb_goods

For each row//This sentence is fixed

Begin

SQL statements

The new row is represented by the new column name in New.

End

3.example:

Delimiter $//encounters $ before ending avoid conflicts with semicolons

Create Trigger TR2

After insert on Tb_order

For each row

Begin

Update tb_goods set num=num-new.onum where id = new.orderid;

end$

Create Trigger TR3

After delete on Tb_order

For each row

Begin

Update tb_goods Set num = num + old.onum where id = old.goods_id; Use old to modify the previous or deleted rows

end$

Update tb_goods set num=num+old.onum-new.onum where Id=old.orderid

MySQL Trigger summary