MySQL(18)如何创建和删除触发器？

触发器（Trigger）是在特定事件（如插入、更新或删除）发生时自动执行的数据库对象。触发器可以帮助自动化操作、维护数据完整性、记录审计日志以及实现复杂的业务逻辑。下面详细介绍如何创建和删除触发器，并结合代码示例进行说明。

创建触发器

使用 CREATE TRIGGER 语句可以创建触发器。触发器的定义包括触发时间（BEFORE 或 AFTER）、触发事件（INSERT、UPDATE 或 DELETE）和触发操作。

语法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;

示例代码

假设我们有一个 employees 表，我们将创建一个触发器，在插入新记录时自动记录操作到一个 audit_log 表中。

-- 创建数据库
CREATE DATABASE company;

-- 选择数据库
USE company;

-- 创建 employees 表
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_position VARCHAR(100),
    emp_salary DECIMAL(10, 2),
    hire_date DATE
);

-- 创建 audit_log 表
CREATE TABLE audit_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(100),
    emp_id INT,
    emp_name VARCHAR(100),
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器，在插入新记录到 employees 表时记录到 audit_log
DELIMITER //

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, emp_id, emp_name)
    VALUES ('INSERT', NEW.emp_id, NEW.emp_name);
END;
//

DELIMITER ;

使用触发器

当向 employees 表插入新记录时，触发器会自动向 audit_log 表插入一条日志记录。

示例代码

-- 插入新员工记录
INSERT INTO employees (emp_name, emp_position, emp_salary, hire_date)
VALUES ('John Doe', 'Manager', 75000.00, '2023-10-01');

-- 查询 audit_log 表中的日志记录
SELECT * FROM audit_log;

结果：

log_id | action | emp_id | emp_name | action_time
-------|--------|--------|----------|---------------------
1      | INSERT | 1      | John Doe | 2023-10-01 12:34:56

删除触发器

使用 DROP TRIGGER 语句可以删除触发器。

语法

DROP TRIGGER trigger_name;

示例代码

-- 删除触发器 after_employee_insert
DROP TRIGGER after_employee_insert;

完整触发器示例

以下示例展示了如何在 MySQL 中创建、使用和删除触发器，从创建表到定义和使用触发器的完整过程。

-- 创建数据库
CREATE DATABASE company;

-- 选择数据库
USE company;

-- 创建 employees 表
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_position VARCHAR(100),
    emp_salary DECIMAL(10, 2),
    hire_date DATE
);

-- 创建 audit_log 表
CREATE TABLE audit_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(100),
    emp_id INT,
    emp_name VARCHAR(100),
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器，在插入新记录到 employees 表时记录到 audit_log
DELIMITER //

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, emp_id, emp_name)
    VALUES ('INSERT', NEW.emp_id, NEW.emp_name);
END;
//

DELIMITER ;

-- 插入新员工记录
INSERT INTO employees (emp_name, emp_position, emp_salary, hire_date)
VALUES ('John Doe', 'Manager', 75000.00, '2023-10-01');

-- 查询 audit_log 表中的日志记录
SELECT * FROM audit_log;

-- 删除触发器 after_employee_insert
DROP TRIGGER after_employee_insert;

示例：实现复杂业务逻辑

假设我们需要在员工薪水更新时记录旧薪水和新薪水，我们可以创建一个触发器来实现这一需求。

-- 创建 salary_change_log 表
CREATE TABLE salary_change_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器，在更新员工薪水时记录薪水变化
DELIMITER //

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF OLD.emp_salary <> NEW.emp_salary THEN
        INSERT INTO salary_change_log (emp_id, old_salary, new_salary)
        VALUES (OLD.emp_id, OLD.emp_salary, NEW.emp_salary);
    END IF;
END;
//

DELIMITER ;

示例代码

-- 更新员工薪水
UPDATE employees
SET emp_salary = 80000.00
WHERE emp_id = 1;

-- 查询 salary_change_log 表中的薪水变化记录
SELECT * FROM salary_change_log;

结果：

log_id | emp_id | old_salary | new_salary | change_time
-------|--------|------------|------------|---------------------
1      | 1      | 75000.00   | 80000.00   | 2023-10-01 12:45:23

小结

通过以上代码示例，展示了如何在 MySQL 中创建、使用和删除触发器。触发器是强大的工具，可以帮助自动化操作、维护数据完整性、记录审计日志和实现复杂的业务逻辑。