MySQL触发器_mysql创建触发器当增加记录时,自动减少数量

MySQL触发器

一、基本概念

触发器是一种特殊类型的存储过程，它不同于存储过程，主要是通过事件触发而被执行的，即不是主动调用而执行的；而存储过程则需要主动调用其名字执行

触发器：trigger，是指事先为某张表绑定一段代码，当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发代码并执行。

二、作用

可在写入数据前，强制检验或者转换数据(保证护数据安全)
触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚

三、创建触发器

注意:创建触发器不能有返回结果集 也就是不能在触发器内进行SELECT xx;

基本语法

delimiter  &&  -- 修改结束符
create trigger 触发器名字 触发行为  触发事件 on 表 for each row
begin
    -- 触发器内容主体，每行用分号结尾
end &&
delimiter ;  -- 修改结束符
1
2
3
4
5
6

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 不是所有触发器都有

对于 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 ;
1
2
3
4
5
6
7

执行修改语句

 update t_orders set id_goods = 10 where id = 1;
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;
1
2
3
4
5
6
7

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);

1
2
3
4
5

订单表

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;
1
2
3
4
5
6

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');

1
2
3
4
5

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 ;

1
2
3
4
5
6
7
8
9
10

然后插入数据试试效果

INSERT INTO t_orders (id_goods,user_name) VALUES
(2,'小小a'),(2,'小小b'),(2,'小小c');
1
2

查询商品表看看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)
1
2
3
4
5
6
7
8
9
10

可以发现电视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 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

然后你一直添加直到商品小于或者等于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;
1
2
3
4
5
6
7

创建效验触发器

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 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

我们进行测试

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);  -- 正确
1
2
3
4
5
6

至于触发器的更多组合用法自己以后慢慢研究 …

点赞 -收藏加 -关注 便于以后复习和收到最新内容 有其他问题在评论区讨论-或者私信我-收到会在第一时间回复 感谢,配合,希望我的努力对你有帮助^_^