本文转载自微信公众号「MySQL技术」,发器作者MySQL技术。聊聊转载本文请联系MySQL技术公众号。发器
触发器即 triggers ,聊聊它是发器与表有关的数据库对象,在满足定义条件时触发,聊聊并执行触发器中定义的发器语句集合。它的聊聊执行不是由程序调用,也不是发器手工启动,而是聊聊由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。
参考官方文档,触发器创建语法模板如下:
- CREATE
- [DEFINER = user]
- TRIGGER trigger_name
- trigger_time trigger_event
- ON tbl_name FOR EACH ROW
- [trigger_order]
- trigger_body
- trigger_time: { BEFORE | AFTER }
- trigger_event: { INSERT | UPDATE | DELETE }
- trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
触发器只能创建在永久表上,不能对临时表或视图创建触发器。触发器的名称在单个数据库内是唯一的。参考上面创建语句,触发器创建有几点要素,下面简要说明下:
trigger_time:是触发动作时间,可以是 BEFORE 或 AFTER ,表示触发器在要修改的每一行之前或之后激活。
delete:从表中删除某一行数据时激活触发器。例如 delete 和 replace 语句。表上的 DROP TABLE 和 TRUNCATE TABLE 语句不会激活此触发器,因为它们不使用 delete ,删除分区也不会激活 delete 触发器。
trigger_body:是触发器激活时要执行的语句。如果要执行多个语句,可使用 BEGIN…END 复合语句结构。在触发器主体中,可以使用 old 和 new 来引用触发器中发生变化的记录内容。
下面来看下触发器相关的具体操作:
- # 创建表 创建触发器
- mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
- Query OK, 0 rows affected (0.03 sec)
- mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
- mysql> delimiter //
- mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
- FOR EACH ROW
- BEGIN
- IF NEW.amount < 0 THEN
- SET NEW.amount = 0;
- ELSEIF NEW.amount > 100 THEN
- SET NEW.amount = 100;
- END IF;
- END;//
- mysql> delimiter ;
- # 验证触发器作用
- mysql> select * from account;
- +----------+---------+
- | acct_num | amount |
- +----------+---------+
- | 137 | 14.98 |
- | 141 | 1937.50 |
- | 97 | -100.00 |
- +----------+---------+
- 3 rows in set (0.00 sec)
- mysql> update account set amount = 114.98 where acct_num = 137;
- Query OK, 1 row affected (0.01 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- mysql> select * from account;
- +----------+---------+
- | acct_num | amount |
- +----------+---------+
- | 137 | 100.00 |
- | 141 | 1937.50 |
- | 97 | -100.00 |
- +----------+---------+
- 3 rows in set (0.00 sec)
- # 查看触发器
- mysql> show triggers;
- # 删除触发器
- mysql> drop trigger if exists upd_check;
- # 查看数据库实例中所有触发器
- SELECT
- a.TRIGGER_SCHEMA,
- a.TRIGGER_NAME,
- a.ACTION_TIMING,
- a.EVENT_OBJECT_TABLE,
- a.EVENT_MANIPULATION
- FROM
- information_schema.`TRIGGERS` a
- WHERE
- a.TRIGGER_SCHEMA NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' );
- delimiter // 设置MySQL执行结束标志,默认为;
上面展示了一些关于触发器的基本操作,其实触发器在生产环境中还是比较少见的,即使它能解决我们某些数据库需求,因为触发器的使用存在一系列的缺点,简要总结几点缺点如下:
但是触发器也并不是一无用处,比如我们不想让人删除或更新这个表的数据,可以用触发器实现,下面的一些场景可能对你有所启发:
- # 禁止删除数据 即使你有权限
- mysql> select * from student;
- +--------------+------+--------+-------+-------+
- | increment_id | s_id | s_name | s_sex | s_age |
- +--------------+------+--------+-------+-------+
- | 1 | 1001 | sdfsd | 男 | 18 |
- | 2 | 1003 | zsdfsd | 女 | 19 |
- +--------------+------+--------+-------+-------+
- 2 rows in set (0.00 sec)
- mysql> delimiter //
- mysql> CREATE TRIGGER `tri_delstu` BEFORE DELETE ON `student` FOR EACH ROW begin
- -> declare msg varchar(255);
- -> set msg="不允许删除学生信息";
- -> SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
- -> end; //
- Query OK, 0 rows affected (0.02 sec)
- mysql> delimiter ;
- mysql> delete from student where s_id = 1003;
- ERROR 1644 (HY000): 不允许删除学生信息
- # 禁止更新某个字段
- mysql> delimiter //
- mysql> CREATE TRIGGER trg__updateSid BEFORE UPDATE ON `student`
- -> FOR EACH ROW
- -> BEGIN
- -> DECLARE msg VARCHAR(100);
- -> IF NEW.s_id <> OLD.s_id THEN
- -> SET msg='不允许修改学号';
- -> SIGNAL SQLSTATE 'HY000' SET message_text = msg;
- -> END IF;
- -> END; //
- Query OK, 0 rows affected (0.06 sec)
- mysql> delimiter ;
- mysql> update student set s_id = 1002 where increment_id = 2;
- ERROR 1644 (HY000): 不允许修改学号
- # 限制修改范围
- mysql> delimiter //
- mysql> CREATE TRIGGER `tri_update_age` BEFORE UPDATE ON `student` FOR EACH ROW BEGIN
- -> DECLARE msg VARCHAR(20);
- -> IF (NEW.s_age<0) THEN
- -> set msg="年龄不能小于0";
- -> signal sqlstate 'HY000' set message_text=msg;
- -> END IF;
- -> END; //
- Query OK, 0 rows affected (0.02 sec)
- mysql> delimiter ;
- mysql> update student set s_age=10 where s_id = 1001;
- Query OK, 1 row affected (0.01 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- mysql> update student set s_age=-10 where s_id = 1001;
- ERROR 1644 (HY000): 年龄不能小于0
总结:
本篇文章简要介绍了触发器的定义及使用案例。在业务逻辑复杂或表变动比较频繁的系统还是不推荐使用触发器,当然它也是有自己的应用场景,无论怎样,触发器的逻辑总是越简单越好,我们应该让数据库做它擅长做的事,不能想着所有逻辑都在数据库层面实现。
责任编辑:武晓燕 来源: MySQL技术 MySQL触发器数据
(责任编辑:百科)
锤子科技新品发布会于10月18日在上海举办 锤子T3呼之欲出
东方空间完成4亿元A轮融资 老股东鼎和高达、天府三江资本等机构持续加持