MySQL trigger learning _ MySQL

Trigger: a special type of thing that can monitor a certain data operation and trigger the relevant operation (insertupdatedelete). some data changes in the table can also cause other data changes. Function: automatically queries certain statements based on changes and adds flexible triggers for programs: a special type of things can monitor certain data operations and trigger related operations (insert/update/delete ); some data changes in the table, hoping to cause other data changes at the same time. Function: automatically completes certain statement queries and adds program flexibility. Trigger creation syntax: # delimiter $ // by default, delimiter is a semicolon" ;",An error will be reported at the end of an SQL statement with a; sign. if the SQL statement does not reach the end, the end is ended with $. the end of the mysql statement is ended with $.

Create trigger test1 # trigger name (after/before) # trigger time (insert/update/delete) # monitoring event on table # monitoring location (table name) for each row # begin sql1 required for mysql... sqlNend; # end $

Query existing trigger: show triggers delete existing trigger: drop trigger name new/old: update trigger: old indicates the record before the update. New indicates that the updated record. insert is triggered: old cannot be used. New indicates the inserted record. delete trigger: old indicates the deleted record. New cannot be used.
Instance operation: goods product table and ord order table

CREATE TABLE goods(`goods_id` INT(10),`name` VARCHAR(20),`num` SMALLINT(4))ENGINE=INNODB CHARSET=utf8CREATE TABLE `ord`(`oid` INT(10),`gid` INT(10),`much` INT(10))ENGINE=INNODB CHARSET=utf8INSERT INTO goods VALUES(1, 'cat', 26),(2, 'dog', 26),(3, 'pig', 26);

1. Each order is required, and the inventory in the goods table is reduced accordingly. Mysql> select * from goods + ---------- + ------ + | goods_id | name | num | + ---------- + ------ + | 1 | cat | 26 | 2 | dog | 26 | 3 | pig | 26 | + ---------- + ------ + 3 rows in set (0.00 sec) mysql> select * from ord; Empty set (0.00 sec)

# CREATE a TRIGGER test1DELIMITER $ create trigger test1AFTERINSERTON 'ord 'for each rowbeginupdate goods SET num = num-new. much WHERE goods_id = new. gid; END $

Mysql> create trigger test1-> AFTER-> INSERT-> ON 'ord '-> for each row-> BEGIN-> UPDATE goods SET num = num-new. much WHERE goods_id = new. gid;-> END $ Query OK, 0 rows affected (0.00 sec) mysql> insert into 'ord 'VALUES (1, 2, 2) $ Query OK, 1 row affected( 0.03 sec) mysql> select * from ord $ + ------ + | oid | gid | much | + ------ + | 1 | 2 | 2 | + ------ + ------ + 1 row in set (0.00 sec) mysql> select * from goods $ + ---------- + ------ + | goods_id | name | num | + ---------- + ------ + | 1 | cat | 26 | 2 | dog | 24 | 3 | pig | 26 | + ---------- + ------ + 3 rows in set (0.00 sec) the dog in the goods table is changed from 26 to 24.
2. after the user cancels the order, the number of items in the product inventory order should be properly placed into the database. The following code only writes trigger, and the operation process is not too messy.

# TRIGGER test2CREATE TRIGGER test2AFTERDELETEON 'ord 'for each rowbeginupdate goods SET num = num + old. much WHERE goods_id = old. gid; END $

3. when the user updates the order, the product inventory should be updated according to the order quantity.

# TRIGGER test3CREATE TRIGGER test3AFTERUPDATEON 'ord 'for each rowbeginupdate goods SET num = num + old. much-new. much WHERE goods_id = new. gid; END $

Query trigger
Delete trigger