MySQL触发器

介绍

在 MySQL 中，触发器（Triggers）可以在数据表上的数据更改时自动执行特定的操作。

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。

触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。（Oracle既有行级触发器，又有语句级触发器）

触发器类型	NEW 和 OLD的使用
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

触发器注意事项

性能影响：触发器会在每次相关事件发生时自动执行，这可能会对性能产生影响，特别是当触发器逻辑复杂或者表中数据量很大时。
调试困难：触发器的错误可能不易察觉，调试时需要特别注意。
递归触发：触发器中的操作可能会导致触发器的递归调用，需要避免这种情况，以免导致无限循环。

通过合理使用触发器，可以自动化数据库操作，提高数据一致性和管理效率。

创建触发器

语法结构 :

create trigger trigger_name 

before/after insert/update/delete

on tbl_name 

[ for each row ]  -- 行级触发器

begin

    trigger_stmt ;

end;

删除触发器

语法结构 :

drop trigger [schema_name.]trigger_name

如果没有指定 schema_name，默认为当前数据库。

查看触发器

可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。

语法结构：

show triggers ;

触发器主要有以下六种执行时间和事件

1. `BEFORE INSERT`

执行时间：在插入操作发生之前。
作用：允许你在数据插入之前修改数据，或对插入操作进行验证。

2. `AFTER INSERT`

执行时间：在插入操作发生之后。
作用：可以在数据插入后执行额外的操作，如更新其他表、记录日志等。

3. `BEFORE UPDATE`

执行时间：在更新操作发生之前。
作用：允许你在数据更新之前对数据进行校验或修改。

4. `AFTER UPDATE`

执行时间：在更新操作发生之后。
作用：用于在数据更新之后执行一些后续操作，比如同步其他表的数据或执行计算。

5. `BEFORE DELETE`

执行时间：在删除操作发生之前。
作用：允许你在数据删除之前进行某些检查或处理，例如备份删除的数据或记录日志。

6. `AFTER DELETE`

执行时间：在删除操作发生之后。
作用：用于在数据删除之后执行一些操作，如清理相关数据或通知系统其他部分。

触发器的执行顺序

在一个操作中，如果涉及多个触发器，它们的执行顺序是固定的：

BEFORE INSERT 触发器在 AFTER INSERT 触发器之前执行。
BEFORE UPDATE 触发器在 AFTER UPDATE 触发器之前执行。
BEFORE DELETE 触发器在 AFTER DELETE 触发器之前执行。

每种触发器都有其适用的场景，选择合适的触发器类型可以帮助你在数据变化时执行必要的操作，确保数据的一致性和完整性。

下面是几个常见的MySQL触发器的示例：

案例1-自动记录日志

1. 插入触发器

假设我们有一个员工表 employees 和一个日志表 employee_log。每当有新的员工被插入到 employees 表时，我们希望在 employee_log 表中记录这次插入的操作。

-- 创建 employees 表
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- 创建 employee_log 表
CREATE TABLE employee_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    action_type VARCHAR(50),
    employee_id INT,
    employee_name VARCHAR(100),
    employee_position VARCHAR(100),
    employee_salary DECIMAL(10, 2)
);

-- 创建插入触发器
DELIMITER //
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log (action_type, employee_id, employee_name, employee_position, employee_salary)
    VALUES ('INSERT', NEW.id, NEW.name, NEW.position, NEW.salary);
END;
//
DELIMITER ;

2. 更新触发器

假设我们有一个产品表 products，每当产品的价格被更新时，我们希望记录这个更新的历史信息到 price_changes 表中。

-- 创建 products 表
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

-- 创建 price_changes 表
CREATE TABLE price_changes (
    change_id INT AUTO_INCREMENT PRIMARY KEY,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    product_id INT,
    old_price DECIMAL(10, 2),
    new_price DECIMAL(10, 2)
);

-- 创建更新触发器
DELIMITER //
CREATE TRIGGER before_product_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price <> NEW.price THEN
        INSERT INTO price_changes (product_id, old_price, new_price)
        VALUES (OLD.product_id, OLD.price, NEW.price);
    END IF;
END;
//
DELIMITER ;

3. 删除触发器

假设我们有一个订单表 orders 和一个已删除订单日志表 deleted_orders_log。每当订单被删除时，我们希望在 deleted_orders_log 中记录删除操作。

-- 创建 orders 表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(10, 2)
);

-- 创建 deleted_orders_log 表
CREATE TABLE deleted_orders_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    delete_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    order_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

-- 创建删除触发器
DELIMITER //
CREATE TRIGGER after_order_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO deleted_orders_log (order_id, order_date, amount)
    VALUES (OLD.order_id, OLD.order_date, OLD.amount);
END;
//
DELIMITER ;

好的，我们来创建一个 MySQL 触发器示例，确保每次修改 grades 表中的学生成绩时，students 表中的总分会自动更新。这个触发器需要处理 grades 表中的插入、更新和删除操作，以保持总分的准确性。

案例2-学生总成绩自动更新

首先，我们定义两个表：

students 表：记录学生基本信息和总分。
grades 表：记录学生的各科成绩。

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    total_score DECIMAL(5, 2) DEFAULT 0
);

CREATE TABLE grades (
    grade_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    subject VARCHAR(100),
    score DECIMAL(5, 2),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

创建触发器

为了确保总分在插入、更新和删除成绩时都能正确更新，我们需要定义三个触发器：

AFTER INSERT 触发器：在成绩插入后更新学生总分。
AFTER UPDATE 触发器：在成绩更新后更新学生总分。
AFTER DELETE 触发器：在成绩删除后更新学生总分。

1. `AFTER INSERT` 触发器

DELIMITER //

CREATE TRIGGER after_grade_insert
AFTER INSERT ON grades
FOR EACH ROW
BEGIN
    UPDATE students
    SET total_score = (
        SELECT COALESCE(SUM(score), 0)
        FROM grades
        WHERE student_id = NEW.student_id
    )
    WHERE student_id = NEW.student_id;
END //

DELIMITER ;

2. `AFTER UPDATE` 触发器

DELIMITER //

CREATE TRIGGER after_grade_update
AFTER UPDATE ON grades
FOR EACH ROW
BEGIN
    UPDATE students
    SET total_score = (
        SELECT COALESCE(SUM(score), 0)
        FROM grades
        WHERE student_id = NEW.student_id
    )
    WHERE student_id = NEW.student_id;
END //

DELIMITER ;

3. `AFTER DELETE` 触发器

DELIMITER //

CREATE TRIGGER after_grade_delete
AFTER DELETE ON grades
FOR EACH ROW
BEGIN
    UPDATE students
    SET total_score = (
        SELECT COALESCE(SUM(score), 0)
        FROM grades
        WHERE student_id = OLD.student_id
    )
    WHERE student_id = OLD.student_id;
END //

DELIMITER ;

触发器解释

DELIMITER //：改变语句结束符，以便定义多行的触发器。
CREATE TRIGGER ...：创建触发器。
AFTER INSERT / UPDATE / DELETE ON grades：指定触发器在 grades 表的插入、更新或删除操作之后触发。
FOR EACH ROW：每次操作一行记录时触发器都会执行。
BEGIN ... END：定义触发器的具体操作。
UPDATE students SET total_score = (SELECT COALESCE(SUM(score), 0) FROM grades WHERE student_id = NEW.student_id)：计算学生的总分并更新 students 表中的 total_score 字段。COALESCE 函数确保当没有成绩时总分为 0。

测试触发器

插入、更新和删除数据以测试触发器的功能：

-- 插入学生记录
INSERT INTO students (name) VALUES ('Alice');
INSERT INTO students (name) VALUES ('Bob');

-- 插入成绩记录
INSERT INTO grades (student_id, subject, score) VALUES (1, 'Math', 90);
INSERT INTO grades (student_id, subject, score) VALUES (1, 'Science', 85);
INSERT INTO grades (student_id, subject, score) VALUES (2, 'Math', 88);

-- 检查学生总分
SELECT * FROM students;

-- 更新成绩，触发器会自动更新学生的总分
UPDATE grades SET score = 95 WHERE grade_id = 1;

-- 再次检查学生总分
SELECT * FROM students;

-- 删除成绩，触发器会自动更新学生的总分
DELETE FROM grades WHERE grade_id = 2;

-- 最终检查学生总分
SELECT * FROM students;

总结

这些触发器确保了每次 grades 表中的成绩发生变化时，students 表中的总分会相应更新。这使得总分的计算保持准确，并自动同步。

Z笔记

左老师的课堂笔记

MySQL触发器

介绍

触发器注意事项

创建触发器

删除触发器

查看触发器

触发器主要有以下六种执行时间和事件

1. `BEFORE INSERT`

2. `AFTER INSERT`

3. `BEFORE UPDATE`

4. `AFTER UPDATE`

5. `BEFORE DELETE`

6. `AFTER DELETE`

触发器的执行顺序

案例1-自动记录日志

1. 插入触发器

2. 更新触发器

3. 删除触发器

案例2-学生总成绩自动更新

创建触发器

1. `AFTER INSERT` 触发器

2. `AFTER UPDATE` 触发器

3. `AFTER DELETE` 触发器

触发器解释

测试触发器

总结

介绍

触发器注意事项

创建触发器

删除触发器

查看触发器

触发器主要有以下六种执行时间和事件

1. BEFORE INSERT

2. AFTER INSERT

3. BEFORE UPDATE

4. AFTER UPDATE

5. BEFORE DELETE

6. AFTER DELETE

触发器的执行顺序

案例1-自动记录日志

1. 插入触发器

2. 更新触发器

3. 删除触发器

案例2-学生总成绩自动更新

创建触发器

1. AFTER INSERT 触发器

2. AFTER UPDATE 触发器

3. AFTER DELETE 触发器

触发器解释

测试触发器

总结

1. `BEFORE INSERT`

2. `AFTER INSERT`

3. `BEFORE UPDATE`

4. `AFTER UPDATE`

5. `BEFORE DELETE`

6. `AFTER DELETE`

1. `AFTER INSERT` 触发器

2. `AFTER UPDATE` 触发器

3. `AFTER DELETE` 触发器