1、了解什么是触发器?
mysql 的触发器和存储过程一样,都是嵌入到mysql的一段程序,触发器 是由事件触发某个动作,这些事件包括,插入 ,更新、删除等语句。如果定义了触发程序,触发器就会触发执行相应的操作。
触发器( trigger)是个特殊的存储过程,不同的是,执行存储过程要使用CALL语句来调用,而触发器的执行不需要使用CALL语句来调用,也不需要手工启动,只要当一个预定义的事件发生的时候,就会被 MYSQL自动调用。比如当对 fruits表进行操作( INSERT、 DELETE或 UPDATE)时就会激活它执行。触发器可以查询其他表,而且可以包含复杂的SQL语句。它们主要用于满足复杂的业务规则或要求。例如:可以根据客户当前的账户状态,控制是否允许插入新订单。本节将介绍如何创建触发器。
NEW 与 OLD 详解
上述示例中使用了NEW关键字,和 MS SQL Server 中的 INSERTED 和 DELETED 类似,MySQL 中定义了 NEW 和 OLD,用来表示
触发器的所在表中,触发了触发器的那一行数据。
具体地:
在 INSERT 型触发器中,NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
在 UPDATE 型触发器中,OLD 用来表示将要或已经被修改的原数据,NEW 用来表示将要或已经修改为的新数据;
在 DELETE 型触发器中,OLD 用来表示将要或已经被删除的原数据;
使用方法: NEW.columnName (columnName 为相应数据表某一列名)
另外,OLD 是只读的,而 NEW 则可以在触发器中使用 SET 赋值,这样不会再次触发触发器,造成循环调用(如每插入一个学生前,都在其学号前加“2013”)。
使用 触发器时需要特别注意,对于相同的表 和相同的事件,只能创建一个触发器。
及时删除不再需要的触发器.
2、查看触发器
show TRIGGERS;
3、删除触发器
drop trigger [trigger_name];
4、创建触发器
下面用一个案例讲解
4.1、创建业务统计表
CREATE TABLE `persons` (
`name` varchar(40) NOT NULL,
`num` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='业务统计表';
4.2、创建销售表
CREATE TABLE `sales` (
`name` varchar(40) NOT NULL,
`sum` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='销售额表';
4.3、创建一个触发器
CREATE TRIGGER num_sum AFTER INSERT on persons
for EACH ROW INSERT INTO sales VALUES(NEW.name,7*NEW.num);
4.4、向person表中插入记录
INSERT into persons VALUES('zhangsan',20),('laidefa',24);
5,再来复杂一点的逻辑
学生表:
create TABLE student(
stuid int not null,
classid int not null
)
班级表:
CREATE table class (
classid int not null,
stuCount int DEFAULT 0
)
业务需求:
新增学生(student)时,自动计算班级(class)总人数
CREATE TRIGGER stu_class BEFORE INSERT on student
for each row
BEGIN
DECLARE a INT DEFAULT 0;
DECLARE b INT DEFAULT 0;
set a = (select stuCount from class WHERE classid = NEW.classid);
IF a=0 or ISNULL(a) THEN
set b = (SELECT count(*) from student WHERE classid = NEW.classid);
if ISNULL(b) THEN
SET b = 0;
END if;
INSERT INTO class VALUES(NEW.classid,b);
SET a=b;
END IF;
UPDATE class c SET c.stuCount = a+1 WHERE c.classid = NEW.classid;
END