MySQL触发器

MySQL触发器

一、基本概念

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

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

二、作用

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

三、创建触发器

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

基本语法

delimiter  &&  -- 修改结束符
create trigger 触发器名字 触发行为  触发事件 onfor 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);  -- 正确

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

胡安民

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值