触发器:
概念:一种存储过程,与普通存储过程的区别是不需要显示地调用,而是通过事件触发后而被主动执行的。
作用:在写入数据前强制检测或者转化数据保证数据的安全;
触发器发生错误时,前面用户已经执行的操作会被撤销,类似于事务的回滚。
创建触发器的语法:
delimiter $$
create trigger 触发器名称 触发时间 触发事件
on 表名 for each row
begin
触发器的主体语句
end
delimiter ;
触发时间:
当sql指令发生时,会使得表中数据发生变化,因此每张表有两个状态:数据操作前、数据操作后
before:表中数据改变前的状态
after:表中数据改变后的状态
触发事件:
触发器是针对数据发送改变才会触发,对应的操作只有:insert\delete\update
触发器名称在每张表中都是唯一的,每种触发事件也是唯一的,因此每张表最多有6
个触发器:
before insert、before update 、before delete
after insert、after update、after delete
例子:有商品库存表和订单两种表,现在要求每往订单表中增加数据,即下订单。商品库存表的数据应该减少。
(1)创建商品表并插入数据:
create table goods(
id int primary key auto_increment,
name varchar(20) default '',
goods_num int default 100
);
insert into goods(name,goods_num)
values
('手机',100),
('电脑',200),
('游戏机',300);
创建订单表:
create table orders (
id int default 0,
order_num int not null
);
(2)如果订单表发生数据插入,对应的商品库存应该减少,减少的操作使用触发器来实现。
delimiter $$
create trigger after_insert_order after insert
on orders for each row
begin
update goods set goods_num = goods_num -1 where id = 1;
end $$
delimiter ;
(3)往orders表中增加数据,会自动调用触发器。
insert into orders value (1,1);
然后查看goods表中id为1的数量,发现数量已经少了1,说明触发器生效了。
注意:上面创建的触发器有缺陷,缺陷是在执行insert orders的操作时goods_num只会减1,这是不符合实际需求的。
删除以上的触发器,然后来解决这个问题。
drop trigger 触发器名称。
实际应用触发器:old/new.字段名
触发器针对的是数据库的每一行数据,每一行数据在操作前后都会有一个状态,触发器将没有操作前的状态保存在old关键字中,将操作后的状态保存到new关键字中。
但是old和new并不是所有的触发器都有,情况如下:
触发器类型 new和old的使用
INSERT型触发器 没有 old,只有 new,new 表示将要(插入前)或者已经增加(插入后)的数据
UPDATE型触发器 既有 old 也有 new,old 表示更新之前的数据,new 表示更新之后的数据
DELETE型触发器 没有 new,只有 old,old 表示将要(删除前)或者已经被删除(删除后)的数据
创建新的触发器:
delimiter $$
create trigger after_insert_order after insert
on orders for each row
begin
update goods set goods_num = goods_num - NEW.order_num where id = new.id;
end $$
delimiter ;
然后再次向orders表中插入数据。
insert into orders value (1,10);
插入完成后再次查看goods表中的数据,对应的数量已经减少了。
第二次操作的sql代码和结果如下:
mysql> delimiter $$
mysql> create trigger after_insert_order after insert
-> on orders for each row
-> begin
-> update goods set goods_num = goods_num - NEW.order_num where id = new.id;
-> end $$
Query OK, 0 rows affected (0.22 sec)
mysql> delimiter ;
mysql>
mysql>
mysql>
mysql> select * from goods;
+----+-----------+-----------+
| id | name | goods_num |
+----+-----------+-----------+
| 1 | 手机 | 98 |
| 2 | 电脑 | 200 |
| 3 | 游戏机 | 300 |
+----+-----------+-----------+
3 rows in set (0.00 sec)
mysql> select * from orders;
+------+-----------+
| id | order_num |
+------+-----------+
| 1 | 1 |
| 1 | 1 |
+------+-----------+
2 rows in set (0.00 sec)
mysql> insert into orders value (1,10);
Query OK, 1 row affected (0.20 sec)
mysql> select * from goods;
+----+-----------+-----------+
| id | name | goods_num |
+----+-----------+-----------+
| 1 | 手机 | 88 |
| 2 | 电脑 | 200 |
| 3 | 游戏机 | 300 |
+----+-----------+-----------+
3 rows in set (0.00 sec)