MySQL触发器：深入理解触发器的语法和示例

码上一元

于 2024-09-04 08:00:00 发布

阅读量367

点赞数 5

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/yqq962464/article/details/141865614

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

概念

触发器是数据库管理系统中的一个特性，它允许用户定义在特定数据库事件发生时自动执行的代码。在MySQL中，触发器可以响应INSERT、UPDATE、DELETE等事件，并且可以在事件之前（BEFORE）或之后（AFTER）执行。
触发器的用途包括对要插入表中的值进行检查或对更新中涉及的值进行计算等。

基本使用方式

创建触发器

CREATE
    [DEFINER = user]
    TRIGGER [IF NOT EXISTS] 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

DEFINER = user：指定触发器的所有者，通常为创建触发器的用户；
IF NOT EXISTS：如果同名触发器已存在，则不创建新触发器；
trigger_name：触发器的名称；
trigger_time：触发器是在事件发生之前（BEFORE）还是之后（AFTER）执行；
trigger_event：触发器响应的事件，可以是INSERT、UPDATE或DELETE；
tbl_name：触发器所属的表名；
trigger_order：触发器的执行顺序，可以是FOLLOWS或PRECEDES；
trigger_body：触发器执行的SQL语句块；

示例代码：

DELIMITER //

CREATE TRIGGER before_insert_customer
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = NOW();
    END IF;
END;//

DELIMITER ;

在这个例子中，我们创建了一个名为before_insert_customer的触发器，它在向customers表插入新记录之前自动设置created_at字段为当前时间。

查询触发器

SHOW TRIGGERS LIKE 'table_name';

或者

SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'database_name' AND EVENT_OBJECT_TABLE = 'table_name';

删除触发器

DROP TRIGGER IF EXISTS trigger_name;

多个触发器

可以为给定的表定义多个具有相同触发事件和执行时间的触发器。
例如，可以为表设置两个BEFORE UPDATE触发器。默认情况下，具有相同触发事件和执行时间的触发器会按它们被创建的顺序激活。
为了影响触发器的顺序，在FOR EACH ROW之后指定一个子句，该子句指示FOLLOWS或PRECEDES以及也具有相同触发事件和执行时间的现有触发器的名称。使用FOLLOWS时，新触发器在现有触发器之后激活。使用PRECEDES时，新触发器在现有触发器之前激活。

 CREATE TRIGGER ins_transaction BEFORE INSERT ON account
       FOR EACH ROW PRECEDES ins_sum
       SET
       @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
       @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);

这个触发器ins_transaction与ins_sum类似，它分别累加存款和取款。它有一个PRECEDES子句，这导致它在ins_sum之前被激活；没有这个子句，它将在ins_sum之后被激活，因为它是在ins_sum之后创建的。

使用场景

数据验证：在数据插入或更新前进行格式、范围或依赖关系的验证。
自动更新：在更新一个表时，自动更新另一个相关表。
审计日志：记录数据变更的历史，用于审计和监控。
复杂计算：执行复杂的计算，如计算字段的默认值或汇总数据。

下面有一个自动求和的例子：触发器充当累加器，对表中某一列中插入的值进行求和。

表结构

CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

触发器

CREATE TRIGGER ins_sum BEFORE INSERT ON account
       FOR EACH ROW SET @sum = @sum + NEW.amount;

插入数据

mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+