MySQL触发器
一、基本概念
触发器是一种特殊类型的存储过程,它不同于存储过程,主要是通过事件触发而被执行的,即不是主动调用而执行的;而存储过程则需要主动调用其名字执行
触发器:trigger,是指事先为某张表绑定一段代码,当表中的某些内容发生改变(增、删、改)的时候,系统会自动触发代码并执行。
二、作用
可在写入数据前,强制检验或者转换数据(保证护数据安全)
触发器发生错误时,前面用户已经执行成功的操作会被撤销,类似事务的回滚
三、创建触发器
注意:创建触发器不能有返回结果集 也就是不能在触发器内进行SELECT xx;
基本语法
delimiter && -- 修改结束符
create trigger 触发器名字 触发行为 触发事件 on 表 for each row
begin
-- 触发器内容主体,每行用分号结尾
end &&
delimiter ; -- 修改结束符
on 表 for each
:触发对象,触发器绑定是表中的所有行,因此当每一行发生指定改变时,触发器就会发生
触发行为
当 SQL 指令发生时,会令行中数据发生变化,而每张表中对应的行有两种状态:数据操作前和操作后
before:表中数据发生改变前的状态
after:表中数据发生改变后的状态
如果 before(在…之前) 触发器失败或者语句本身失败,将不执行 after(在…之后) 触发器(如果有的话)
触发事件
触发器是针对数据发送改变才会被触发,对应的操作只有
- INSERT
- DELETE
- UPDATE
注意事项
在 MySQL 5 中,触发器名必须在每个表中唯一,但不是在每个数据库中唯一,即同一数据库中的两个表可能具有相同名字的触发器
每个表的每个事件每次只允许一个触发器,因此,每个表最多支持 6 个触发器,
[before/after] insert 、 [before/after] delete、[before/after] update
四、查看和删除已有的触发器
(1)查看已有触发器:show triggers
(2)删除已有触发器:drop trigger triggerName
查看触发器的创建语句
语法:show create trigger 触发器名字;
五、激活触发器
激活不是手动触发的,而是在对应的事件发生后才会触发。比如我们创建的触发器,只有在对订单表进行数据操作的时候,触发器才会执行
六、获取触发器参数
触发器针对的是数据库中的每一行记录,每行数据在操作前后都会有一个对应的状态,
触发器将没有操作之前的状态(数据)保存到 old 关键字中,将操作后的状态(数据)保存到 new 中
语法:old/new.字段名
需要注意的是,old 和 new 不是所有触发器都有
触发器类型 | new和old的使用 |
---|---|
INSERT型触发器 | 没有 old,只有 new,new 表示插入前或者插入后的数据 (依据before或者after) |
UPDATE型触发器 | 既有 old 也有 new,old 表示更新之前的数据,new 表示更新之后的数据 |
DELETE型触发器 | 没有 new,只有 old,old 表示将要(删除前)或者已经被删除(删除后)的数据 (依据before或者after) |
对于 auto_increment 列,new 在 insert 执行之前包括 0,在 insert 执行之后包括新的自动生成的值
八、触发器不能对当前触发器所在的表操作
MySQL的触发器不能对当前触发器所在的表进行 insert、update 和 delete 操作,否则会报错
delimiter &&
DROP trigger IF EXISTS ordersUp &&
create trigger ordersUp before update on orders for each row
begin
update orders set goods_id = 10 where id = new.id;
end &&
delimiter ;
执行修改语句
update t_orders set id_goods = 10 where id = 1;
1442 - Can’t update table ‘t_orders’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
时间: 0.006s
上面这条触发器创建是成功的但是 我在对orders进行修改数据时候就会报错
九、优缺点
优点
- 触发器可以通过数据库中的关联表实现,联动更改,即一张表数据的改变会影响其他表的数据
- 可以保证数据安全,并进行安全校验
缺点
- 过分依赖触发器,影响数据库的结构,增加数据库的维护成本
触发器例子
多表联动
例子1: 添加订单表表数据的同时减少商品表商品的个数
1.首先创建两张表,商品表和订单表
商品表
CREATE TABLE `t_goods` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`good_name` varchar(255) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
`count` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gb2312;
INSERT INTO `t_goods` (`id`, `good_name`, `price`, `count`) VALUES (1, '冰箱', 1000.00, 10);
INSERT INTO `t_goods` (`id`, `good_name`, `price`, `count`) VALUES (2, '电视', 3000.00, 22);
INSERT INTO `t_goods` (`id`, `good_name`, `price`, `count`) VALUES (3, '电脑', 3000.00, 5);
INSERT INTO `t_goods` (`id`, `good_name`, `price`, `count`) VALUES (4, '玩具', 200.00, 110);
订单表
CREATE TABLE `t_orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_goods` varchar(255) DEFAULT NULL,
`user_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gb2312;
INSERT INTO `t_orders` (`id`, `id_goods`, `user_name`) VALUES (1, '1', 'hu');
INSERT INTO `t_orders` (`id`, `id_goods`, `user_name`) VALUES (2, '1', 'an');
INSERT INTO `t_orders` (`id`, `id_goods`, `user_name`) VALUES (3, '2', 'min');
INSERT INTO `t_orders` (`id`, `id_goods`, `user_name`) VALUES (4, '4', 'hao');
2.如果订单表发生数据插入,对应的商品库存应该减少。因此这里对订单表创建触发器
delimiter &&
DROP trigger if EXISTS after_insert_order&&
-- 创建触发器 名称为 after_insert_order 当执行插入语句后在执行
create trigger after_insert_order after insert on t_orders for each row
begin
-- 更新商品表的库存,这里只指定了更新第一件商品的库存
update t_goods set count = count - 1 where id = new.id_goods;
end &&
delimiter ;
然后插入数据试试效果
INSERT INTO t_orders (id_goods,user_name) VALUES
(2,'小小a'),(2,'小小b'),(2,'小小c');
查询商品表看看id=2的商品数量被改变了没
mysql> SELECT * FROM t_goods;
+----+-----------+---------+-------+
| id | good_name | price | count |
+----+-----------+---------+-------+
| 1 | 冰箱 | 1000.00 | 10 |
| 2 | 电视 | 3000.00 | 19 |
| 3 | 电脑 | 3000.00 | 5 |
| 4 | 玩具 | 200.00 | 110 |
+----+-----------+---------+-------+
4 rows in set (0.04 sec)
可以发现电视count 从之前的22变为现在的19了
3.当然我们还需要考虑一种情况:如果此时商品的库存不够了,该怎么处理?
delimiter &&
DROP trigger if EXISTS before_insert_order&&
-- 创建触发器
create trigger before_insert_order before insert on t_orders for each row
begin
DECLARE num DOUBLE DEFAULT 0.0; -- 创建局部变量
-- 取出 goods 表中对应 id 的库存
select count into num from t_goods where id = new.id_goods ;
-- 用即将插入的 orders 表中的库存和 goods 表中的库存进行比较
-- 如果库存不够,中断操作
if num <=0 then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '报错了O(∩_∩)O哈哈~'; -- 固定写法
end if;
end &&
delimiter ;
然后你一直添加直到商品小于或者等于0时候就会抛出错误
1644 - 报错了O(∩_∩)O哈哈~
时间: 0s
如果在触发器中出现错误,那么前面的已经执行的操作也会全部清空 (也就是说执行的插入语句无效)
插入效验
例子2: 插入语句效验
创建user表
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`sex` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=gb2312;
创建效验触发器
delimiter $$
DROP trigger if EXISTS trigger_user $$
create trigger trigger_user before INSERT on t_user for each row
begin
IF new.age>100 OR new.age<0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '年龄不对'; -- 固定写法
END IF;
IF new.sex != '男' AND new.sex != '女' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '性别不对'; -- 固定写法
END IF;
end $$
delimiter ;
我们进行测试
INSERT INTO `t_user` ( `name`, `sex`, `age`) VALUES ('hu', '哈希', 22); -- 错误 性别不对
INSERT INTO `t_user` ( `name`, `sex`, `age`) VALUES ('hu', '男', -1); -- 错误 年龄不对
INSERT INTO `t_user` ( `name`, `sex`, `age`) VALUES ('hu', '男', 110); -- 错误 年龄不对
INSERT INTO `t_user` ( `name`, `sex`, `age`) VALUES ('hu', '女', 20); -- 正确
至于触发器的更多组合用法自己以后慢慢研究 …