mysql之触发器trigger的使用例子
为什么要使用触发器
触发器的优点
1，触发器的"自动性"
对程序员来说，触发器是看不到的，但是他的确做事情了，如果不用触发器的话，你更新了user表的name字段时，你还要写代码去更新其他表里面的冗余字段，我举例子，只是一张表，如果是几张表都有冗余字段呢，你的代码是不是要写很多呢，看上去是不是很不爽呢。
2，触发器的数据完整性
触发器有回滚性，举个例子，我发现我很喜欢举子，就是你要更新五张表的数据，不会出现更新了二个张表，而另外三张表没有更新。
但是如果是用php代码去写的话，就有可能出现这种情况的，比如你更新了二张表的数据，这个时候，数据库挂掉了。你就郁闷了，有的更新了，有的没更新。这样页面显示不一致了，变有bug了。
关于触发器(trigger)的例子
触发器(trigger)：监视某种情况，并触发某种操作。
触发器创建语法四要素：1.监视地点(table) 2.监视事件(insert/update/delete) 3.触发时间(after/before) 4.触发事件(insert/update/delete)
语法：
create trigger triggerName after/before insert/update/delete on 表名 for each row #这句话在mysql是固定的 begin sql语句; end;
注：各自颜色对应上面的四要素。
首先我们来创建两张表：
#商品表
create table g
(
id int primary key auto_increment,
name varchar(20),
num int
);
#订单表
create table o
(
oid int primary key auto_increment,
gid int,
much int
);
insert into g(name,num) values('商品1',10),('商品2',10),('商品3',10);
如果我们在没使用触发器之前：假设我们现在卖了3个商品1，我们需要做两件事
1.往订单表插入一条记录 insert into o(gid,much) values(1,3); 2.更新商品表商品1的剩余数量 update g set num=num-3 where id=1;
现在，我们来创建一个触发器：
需要先执行该语句：delimiter $(意思是告诉mysql语句的结尾换成以$结束)
create trigger tg1 after insert on o for each row begin update g set num=num-3 where id=1; end$ 这时候我们只要执行： insert into o(gid,much) values(1,3)$
会发现商品1的数量变为7了，说明在我们插入一条订单的时候，触发器自动帮我们做了更新操作。
但现在会有一个问题，因为我们触发器里面num和id都是写死的，所以不管我们买哪个商品，最终更新的都是商品1的数量。比如：我们往订单表再插入一条记录：insert into o(gid,much) values(2,3),执行完后会发现商品1的数量变4了，而商品2的数量没变，这样显然不是我们想要的结果。我们需要改改我们之前创建的触发器。
我们如何在触发器引用行的值，也就是说我们要得到我们新插入的订单记录中的gid或much的值。
对于insert而言，新插入的行用new来表示，行中的每一列的值用new.列名来表示。
所以现在我们可以这样来改我们的触发器
create trigger tg2 after insert on o for each row begin update g set num=num-new.much where id=new.gid;(注意此处和第一个触发器的不同) end$
第二个触发器创建完毕，我们先把第一个触发器删掉
drop trigger tg1$
再来测试一下，插入一条订单记录：insert into o(gid,much) values(2,3)$
执行完发现商品2的数量变为7了，现在就对了。
现在还存在两种情况：
1.当用户撤销一个订单的时候，我们这边直接删除一个订单，我们是不是需要把对应的商品数量再加回去呢？
2.当用户修改一个订单的数量时，我们触发器修改怎么写?
我们先分析一下第一种情况：
监视地点：o表
监视事件：delete
触发时间：after
触发事件：update
对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示，old.列名可以引用被删除的行的值。
那我们的触发器就该这样写：
create trigger tg3 after delete on o for each row begin update g set num = num + old.much where id = old.gid;(注意这边的变化) end$
创建完毕。
再执行delete from o where oid = 2$
会发现商品2的数量又变为10了。
第二种情况：
监视地点：o表
监视事件：update
触发时间：after
触发事件：update
对于update而言：被修改的行，修改前的数据，用old来表示，old.列名引用被修改之前行中的值；
修改的后的数据，用new来表示，new.列名引用被修改之后行中的值。
那我们的触发器就该这样写：
create trigger tg4 after update on o for each row begin update g set num = num+old.much-new.much where id = old/new.gid; end$
先把旧的数量恢复再减去新的数量就是修改后的数量了。
我们来测试下：先把商品表和订单表的数据都清掉，易于测试。
假设我们往商品表插入三个商品，数量都是10，
买3个商品1：insert into o(gid,much) values(1,3)$
这时候商品1的数量变为7；
我们再修改插入的订单记录: update o set much = 5 where oid = 1$
我们变为买5个商品1，这时候再查询商品表就会发现商品1的数量只剩5了，说明我们的触发器发挥作用了。
如果再看不到我们接着看
在MySQL Server里面也就是对某一个表的一定的操作，触发某种条件（Insert,Update,Delete 等），从而自动执行的一段程序。从这种意义上讲触发器是一个特殊的存储过程。下面通过MySQL触发器实例，来了解一下触发器的工作过程吧！
一、创建MySQL实例数据表：
在mysql的默认的测试test数据库下，创建两个表t_a与t_b：
下载: Create_SQL.sql
/*Table structure for table `t_a` */ DROP TABLE IF EXISTS `t_a`; CREATE TABLE `t_a` ( `id` smallint(1) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) DEFAULT NULL, `groupid` mediumint(8) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1; /*Data for the table `t_a` */ LOCK TABLES `t_a` WRITE; UNLOCK TABLES; /*Table structure for table `t_b` */ DROP TABLE IF EXISTS `t_b`; CREATE TABLE `t_b` ( `id` smallint(1) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) DEFAULT NULL, `groupid` mediumint(8) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=latin1; /*Data for the table `t_b` */ LOCK TABLES `t_b` WRITE; UNLOCK TABLES;
在t_a表上分创建一个CUD（增、改、删）3个触发器，将t_a的表数据与t_b同步实现CUD，注意创建触发器每个表同类事件有且仅有一个对应触发器，为什么只能对一个触发器，不解释啦，看MYSQL的说明帮助文档吧。
二、创建MySQL实例触发器：
在实例数据表t_a上依次按照下面步骤创建tr_a_insert、tr_a_update、tr_a_delete三个触发器
1、创建INSERT触发器trigger_a_insert：
下载: trigger_a_insert.sql
DELIMITER $$ USE `test`$$ --判断数据库中是否存在tr_a_insert触发器 DROP TRIGGER /*!50032 IF EXISTS */ `tr_a_insert`$$ --不存在tr_a_insert触发器，开始创建触发器 --Trigger触发条件为insert成功后进行触发 CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `tr_a_insert` AFTER INSERT ON `t_a` FOR EACH ROW BEGIN --Trigger触发后，同时对t_b新增同步一条数据 INSERT INTO `t_b` SET username = NEW.username, groupid=NEW.groupid; END; $$ DELIMITER;
2、创建UPDATE触发器trigger_a_update：
下载: trigger_a_update.sql
DELIMITER $$ USE `test`$$ --判断数据库中是否存在tr_a_update触发器 DROP TRIGGER /*!50032 IF EXISTS */ `tr_a_update`$$ --不存在tr_a_update触发器，开始创建触发器 --Trigger触发条件为update成功后进行触发 CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `tr_a_update` AFTER UPDATE ON `t_a` FOR EACH ROW BEGIN --Trigger触发后，当t_a表groupid,username数据有更改时，对t_b表同步一条更新后的数据 IF new.groupid != old.groupid OR old.username != new.username THEN UPDATE `t_b` SET groupid=NEW.groupid,username=NEW.username WHEREusername=OLD.username AND groupid=OLD.groupid; END IF; END; $$ DELIMITER ;
3、创建DELETE触发器trigger_a_delete：
下载: trigger_a_delete.sql
DELIMITER $$ USE `test`$$ --判断数据库中是否存在tr_a_delete触发器 DROP TRIGGER /*!50032 IF EXISTS */ `tr_a_delete`$$ --不存在tr_a_delete触发器，开始创建触发器 --Trigger触发条件为delete成功后进行触发 CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `tr_a_delete` AFTER DELETE ON `t_a` FOR EACH ROW BEGIN --t_a表数据删除后，t_b表关联条件相同的数据也同步删除 DELETE FROM `t_b` WHERE username=Old.username AND groupid=OLD.groupid; END; $$ DELIMITER ;
三、测试MySQL实例触发器：
分别测试实现t_a与t_b实现数据同步CUD(增、改、删)3个Triggers
1、测试MySQL的实例tr_a_insert触发器：
在t_a表中新增一条数据，然后分别查询t_a/t_b表的数据是否数据同步，测试触发器成功标志，t_a表无论在何种情况下，新增了一条或多条记录集时，没有t_b表做任何数据insert操作，它同时新增了一样的多条记录集。
下面来进行MySQL触发器实例测试：
--t_a表新增一条记录集
INSERT INTO `t_a` (username,groupid) VALUES ('sky54.net',123)
--查询t_a表
SELECT id,username,groupid FROM `t_a`
--查询t_b表
SELECT id,username,groupid FROM `t_b`2、测试MySQL的实例tr_a_update、tr_a_delete触发器：
这两个MySQL触发器测试原理、步骤与tr_a_insert触发器一样的，先修改/删除一条数据，然后分别查看t_a、t_b表的数据变化情况，数据变化同步说明Trigger实例成功，否则需要逐步排查错误原因。
世界上任何一种事物都其其优点和缺点，优点与缺点是自身一个相对立的面。当然这里不是强调“世界非黑即白”式的“二元论”，“存在即合理”嘛。当然MySQL触发器的优点不说了，说一下不足之处，MySQL Trigger没有很好的调试、管理环境，难于在各种系统环境下测试，测试比MySQL存储过程要难，所以建议在生成环境下，尽量用存储过程来代替MySQL触发器。
