MySQL trigger learning, mysql trigger

MySQL trigger learning, mysql trigger
Trigger: a special type of thing that can monitor certain data operations and trigger related operations (insert, update, and delete). Some data changes in the table, it is expected that other data changes may occur 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



How to run a trigger in mysql )?

A trigger, not called, is triggered.
Your trigger is for the password table and triggered after modification.
Then you can modify the price of the data column in the password table.
 
MYSQL trigger syntax

A trigger cannot be created without delimiter in the command line.
Delimiter $ create trigger insertAfterDeleteafter t_order on insertfor each rowbeginupdate goods set goods_count = goods_count-new. order_count where goods_id = new. goods_id; end; $ response