MySQL触发器trigger的使用

2023年 8月 15日 64.4k 0

触发器(trigger)是数据库的一类对象,与表关联,当表中指定的事件发生时,比如插入数据,更新数据,删除数据,触发器就会被激活,来执行一些SQL语句。

  • 触发器不能建立在临时表或者视图上。
  • 触发器的名称在schema的命名空间里,也就是说一个数据库里面的触发器名称必须唯一。
  • 创建触发器的用户需要有TRIGGER权限。

1. 触发器创建语法

创建触发器使用create trigger语法,如下:

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_name,触发器名称。
  • trigger_time,触发时机,可选值为BEFORE和AFTER,表示行数据被修改之前还是之后触发。
  • trigger_event,激活触发器的操作类型,可选值为INSERT,UPDATE和DELETE。drop table,truncate table 以及删除分区表并不会触发DELETE类型的触发器。
  • trigger_order,触发器执行顺序,如果一个表创建多个相同操作类型的触发器,那么这些触发器会按照被创建的顺序依次触发。可以在创建触发器时,指定FOLLOWS或者PRECEDES来明确多个触发器的执行顺序。
  • trigger_body,触发器执行体,当触发器被激活时,要执行的语句。执行体中,用NEW标识新的行数据,OLD标识旧的行数据。

2. 查看触发器

SHOW TRIGGERS\G
SHOW TRIGGERS IN db_name\G
SHOW TRIGGERS IN db_name like '%xxx%'\G

3. 删除触发器

DROP TRIGGER trigger_name;
DROP TRIGGER db_name.trigger_name;

4. 触发器使用案例

案例一

有一张表account,往这个表插入数据时,希望能够计算所有插入记录的amount数量之和。采用触发器实现如下:

# 创建表
CREATE TABLE account (id INT, amount DECIMAL(10,2), primary key(id));

# 创建触发器
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;

# 计算插入数据之和
SET @sum = 0;
INSERT INTO account VALUES(1,14.98),(2,1937.50),(3,-100.00);
SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+

案例二

使用触发器做数据校验,更新account表中的amount字段,amount字段取值只能在0~100之间,其余的值都是无效值,小于0的值自动纠正为0,大于100的值自动纠正为100,采用触发器实现如下:

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount 100 THEN
SET NEW.amount = 100;
END IF;
END;//
mysql> delimiter ;

案例三

使用触发器实现影子表,对account表的所有操作,都会同步到影子表account_shadow中。

# 创建影子表
mysql> create table account_shadow like account;

mysql> delimiter //

# 创建insert触发器
mysql> CREATE TRIGGER trg_insert after INSERT ON account
FOR EACH ROW
BEGIN
INSERT INTO account_shadow SET id=NEW.id, amount=NEW.amount;
END;

# 创建update触发器
CREATE TRIGGER trg_update after UPDATE ON account
FOR EACH ROW
BEGIN
update account_shadow SET id=NEW.id, amount=NEW.amount where id=OLD.id;
END;

# 创建delete触发器
CREATE TRIGGER trg_delete after DELETE ON account
FOR EACH ROW
BEGIN
delete from account_shadow where id=OLD.id;
END;//

mysql> delimiter ;

5. 触发器可能导致的问题

  1. 触发器对数据库性能一定的损耗,建议使用前进行压测,判断性能不影响正常业务时再使用。
  2. 触发器可能会导致死锁,在高并发业务下,过多地使用触发器,将会大大增加死锁的概率。触发器实现在MySQL内部,对客户端不可见,导致死锁时,也更不容易被发现。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论