MySQL Trigger Trigger Learning

Trigger:a special kind of thing. You can monitor a data operation and trigger related actions (Insert/update/delete). some of the data in the table change, hoping that the same time can cause other related data changes in demand. Role: Change their own active completion of certain statement queries, the flexibility to join the program. Create TRIGGER Syntax: #delimiter $$//by default. Delimiter is a semicolon " ; ",SQL statement with; the end of the number will be an error, no end even if it ends, use delimiter $$role istell the end of the MySQL statement to end with $, corresponding to end with end$$

Create Trigger Test1  #触发器名称 (after/before) #触发时间 (insert/update/delete)  #监视事件on table #监视地点 (table name) for each row  #mysql必须加的 begin    SQL1 ...    Sqlnend. #end $$

query an existing trigger:show triggersRemove an existing Trigger:drop trigger trigger nameNew/old:UpdateTrigger: Old represents the record before the update. New represents the updated record.Inserttrigger: Old cannot be used. New represents the inserted record.DeleteTrigger: Old represents the deleted record.

New cannot be used.

Instance operations:Goods Commodity table and Ord order form

CREATE table Goods (' goods_id ' INT (), ' name ' VARCHAR (), ' Num ' SMALLINT (4)) Engine=innodb charset=utf8create TABLE ' ord ' (' oid ' int (), ' gid ' int (), ' much ' int (ten) ') Engine=innodb Charset=utf8insert into goods VALUES (1, ' cat ', "), (2, ' dog ', (3, ' pig ', 26);

1, the requirements for each next order, goods table in the inventory corresponding to reduce the number of orders.

mysql> SELECT * FROM goods+----------+------+------+| goods_id | name | num |+----------+------+------+| 1 | cat | || 2 | dog |   - || 3 | pig |+----------+------+------+3 rows in Set (0.00 sec)mysql> select * from Ord;Empty Set (0.00 sec)

#创建触发器test1DELIMITER $ $CREATE TRIGGER Test1afterinserton ' ord ' for each rowbeginupdate goods SET num= Num-new.much WHERE goods_id = New.gid; end$$

mysql> CREATE TRIGGER test1 AfterINSERT-On ' Ord 'For each ROWBEGIN- 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 | || 2 | dog |   - || 3 | pig |+----------+------+------+3 rows in Set (0.00 sec)The dog in the goods table is changed from 26 to 24.
2, the user cancels the order after the commodity stock order merchandise number to be normal storage.

Below only write trigger trigger, the operation process does not write too much more chaotic.

3. When the user updates the order. The inventory of the goods should be updated according to the order quantity.

#触发器test3CREATE TRIGGER Test3afterupdateon ' ord ' for each rowbeginupdate goods SET num = num + old.much-new.much WHERE go ods_id = New.gid; end$$

Query triggers
Delete Trigger

MySQL Trigger Trigger Learning