触发器trigger：某条数据改变，希望其他数据也改变(一张表的数据改变，另一张表的数据也变)。监测insert,update,delete.能够监测增删改并出发增删改。监测点（table）监测事件（insert,update,delete）触发时间（after,before）触发事件（insert,update，delete）.当下一个订单的时候，对应的商品库存减少。监视table order,监视动作insert,触发时间after,触发事件updatecreate trigger t1//触发器名字 after//之后触发insert//监测插入操作on order//监测order表for each rowbegin//触发执行的n条语句update goods set num=num-2 where gid=1;update goods set...endcreate table goods(//创建商品表gid int,name varchar(20),num smallint//库存);create table order(//创建订单表oid int,gid int,//哪个商品的订单much smallint//买了几个商品);insert into goods values (1,'猫',34),(2,'马',65),(3,'猪',21);insert into orde values (1,1,2);delimiter $  //begin end中间有语句后面有分号，语句最后也有分号，所以区分不了，这里告诉mysql以$符号作为最后结束符。create trigger t1afterinserton ordefor each rowbeginupdate goods set num=num-2 where gid=1;end$    //现在结束符已经是$了，要手动修改过来，delimiter ;mysql> show triggers;+---------+--------------------+| Trigger | Event  | Table | Statement | Timing | Created  | sql_mode | character_set_client | collation_connection | Database Collation |+---------+--------------------+| t1      | INSERT | orde  | beginupdate goods set num=num-2 where gid=1;end | AFTER  | 2017-12-27 21:47:13.47 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | skip-grants user@skip-grants host | utf8                 | utf8_general_ci      | utf8_general_ci    |+---------+--------+-------+-----+1 row in setmysql> insert into orde values (3,3,3);//然后goods表就更新了。mysql> truncate orde$  //清空表mysql> drop trigger t1$ //删除触发器//插入订单触发器,添加的叫做newmysql> delimiter $create trigger t2afterinserton ordefor each rowbeginupdate goods set num=num-new.num where gid=new.gid;//new是插入orde的那个新行end$mysql> insert into orde values (1,3,21)$  //goods表就自动更新了//删除订单触发器, 被删除的叫oldmysql> delimiter $create trigger t3afterdeleteon ordefor each rowbeginupdate goods set num=num-old.num where gid=old.gid;//old是删除orde的那个新行end$mysql> delete from orde where gid=1$  //goods表就自动更新了//改订单触发器,改之前叫做old改之后叫做newmysql> delimiter $create trigger t4beforeupdate on ordefor each rowbeginupdate goods set num=new.num + old.num where gid=old.gid;//old是删除orde的那个新行end$mysql> update orde set num=5 where gid=1$//如果库存只有3头猪，客户要买10头猪，能否在much>num时候，把much改为num,mysql> delimiter $create trigger t5before  //不能用after,inserton ordefor each rowbegindeclarernum int;//声明变量rnum为int型,为订单的数量，也就是要删除的数量，select num into rnum from goods where gid=new.gid;//new.gid要插入订单的新行，num是库存量，if rnum < new.num then    set new.num=1;end if;update goods set num=num-new.num where gid=new.gid;//new是插入orde的那个新行end$mysql> insert into orde values(1,2,77)$//for each row:触发器分为语句级触发器，行级触发器，比如create trigger tnafter updateon xxtablefor each rowbegin    sql...end$执行：update xxtable set xxx=xxx where id>100;//修改了100行那么sql会被触发100次。mysql> create trigger t6    after     update    on orde    for each row   //每一行受影响，触发器都执行，    begin    insert into ceshi values (1);    end$    mysql> update orde set num=10 where gid=3$  //插入了4次//oracle中for each row 不写，无论影响了多少行，都只执行一次，//比如，有人下一个订单，买了5件商品，可以用行触发器insert5次商品，用语句触发器，insert1条发货提醒。然而mysql不支持语句级触发器，所以for each row不能省略。oracle可以不写for each row成为语句级触发器。