当前位置：首页 > article >正文

MySQL触发器的使用详解

article 2025/2/6 0:49:06

MySQL触发器的使用详解

MySQL触发器是一种特殊的存储过程，它与表操作紧密相关，并且在特定事件（如INSERT、UPDATE或DELETE）发生时自动执行。触发器的主要目的是确保数据完整性、实现复杂的业务逻辑以及记录审计信息。它们可以在事件发生之前（BEFORE）或之后（AFTER）执行，并且针对每一行数据进行操作。下面我们将详细介绍如何创建和管理触发器，以及提供一些实用的示例。

1. 创建触发器

创建触发器的基本语法如下：

CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body;

trigger_name: 触发器的名字。
trigger_time: 指定触发器是在事件之前(BEFORE)还是之后(AFTER)执行。
trigger_event: 定义触发事件类型，可以是INSERT、UPDATE或DELETE。
tbl_name: 触发器关联的表名。
FOR EACH ROW: 表明这是一个行级触发器，意味着对于每一行数据的操作都会触发一次。
trigger_order: 可选参数，用于定义多个触发器之间的执行顺序，使用FOLLOWS或PRECEDES指定。
trigger_body: 触发器的具体执行内容，可以是一条或多条SQL语句，如果有多条语句，则需要用BEGIN...END包裹起来。

示例：插入触发器

当向work表中插入新记录时，会自动向time表中添加当前时间戳：

CREATE TRIGGER trig1 AFTER INSERT ON work FOR EACH ROW
INSERT INTO time VALUES(NOW());

示例：更新触发器

每当account表中的金额字段被更新时，检查并限制其值不超过100元：

DELIMITER //
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//
DELIMITER ;

示例：删除触发器

当从student表中删除一条记录时，不仅减少学生总数表中的计数，还会增加删除学生总数表中的计数：

DELIMITER //
CREATE TRIGGER tri_delete_stu AFTER DELETE ON student
FOR EACH ROW
BEGIN
    UPDATE tj SET count = count - 1;
    UPDATE delete_stu SET count = count + 1;
END//
DELIMITER ;

2. 使用`NEW`和`OLD`关键字

在触发器内部，可以通过NEW和OLD两个伪记录来访问受影响的行。NEW代表即将插入的新记录或更新后的新值；而OLD则指向即将被替换或删除的旧记录。这使得我们能够在触发器中对这些值进行处理，例如验证、转换或记录变更日志。

NEW.columnName: 引用新插入或更新后的列值。
OLD.columnName: 引用即将被更新或删除的原有列值。

3. 查看和删除触发器

要查看系统中存在的所有触发器，可以使用SHOW TRIGGERS命令。若要删除某个特定的触发器，则可以使用DROP TRIGGER命令。需要注意的是，删除一个表的同时也会删除该表上的所有触发器。

-- 查看所有触发器
SHOW TRIGGERS;

-- 删除名为'trig1'的触发器
DROP TRIGGER IF EXISTS trig1;

4. 触发器的应用场景

触发器广泛应用于各种数据库操作中，以下是几个典型的应用场景：

自动化任务：比如自动记录表数据变化的日志、生成统计信息等。例如，在用户表中添加一条记录时，同时在日志表中记录这条记录的信息。

CREATE TABLE user_info (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE user_operation_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    operation_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_id INT,
    operation_data JSON
);

-- 插入触发器
CREATE TRIGGER user_insert AFTER INSERT ON user_info FOR EACH ROW
BEGIN
    INSERT INTO user_operation_log(operation_type, user_id, operation_data)
    VALUES ('INSERT', NEW.user_id, JSON_OBJECT('user_id', NEW.user_id, 'username', NEW.username, 'email', NEW.email));
END;