MySQL 触发器创建

 一、触发器创建语法

基本语法：

CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name FOR EACH ROW
trigger_body

参数详解：

二、BEFORE 类型触发器实例分析

实例1：工资求和触发器

 创建BEFORE INSERT触发器

CREATE TRIGGER SumOfSalary
BEFORE INSERT ON tb_emp8
FOR EACH ROW
SET @sum = @sum + NEW.salary;

 使用示例

SET @sum = 0;
INSERT INTO tb_emp8 VALUES(1,'A',1,1000),(2,'B',1,500);
SELECT @sum; -- 输出: 1500

关键点分析：

1. `BEFORE INSERT`: 在插入操作执行前触发

2. `NEW.salary`: 访问即将插入的新数据的salary字段值

3. `@sum`: 用户变量，用于存储累加结果

4. 行级触发: 插入两行数据，触发器执行两次

三、AFTER 类型触发器实例分析

实例2：双倍工资备份触发器

 创建AFTER INSERT触发器

CREATE TRIGGER double_salary
AFTER INSERT ON tb_emp6
FOR EACH ROW
INSERT INTO tb_emp7 
VALUES (NEW.id, NEW.name, NEW.deptId, 2 * NEW.salary);

 使用示例

INSERT INTO tb_emp6 VALUES (1,'A',1,1000),(2,'B',1,500);

关键点分析：

1. `AFTER INSERT`: 在插入操作完成后触发

2. `NEW` 虚拟表: 访问刚插入的数据

3. 数据同步: 将数据同步到另一个表，并进行转换（工资翻倍）

4. 注意: 原示例中 `deptId` 应为 `NEW.deptId`，否则会报错

四、触发器的实际应用场景扩展

1. 数据验证（BEFORE 触发器）

 验证邮箱格式

CREATE TRIGGER validate_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.email NOT LIKE '%@%' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '邮箱格式不正确';
    END IF;
END;

2. 审计日志（AFTER 触发器）

 记录数据变更日志

CREATE TRIGGER audit_employee_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (
        table_name, 
        action_type, 
        record_id, 
        old_value, 
        new_value, 
        changed_by, 
        change_time
    ) VALUES (
        'employees',
        'UPDATE',
        OLD.id,
        CONCAT('Name:', OLD.name, ', Salary:', OLD.salary),
        CONCAT('Name:', NEW.name, ', Salary:', NEW.salary),
        CURRENT_USER(),
        NOW()
    );
END;

3. 数据同步（AFTER 触发器）

同步到统计表

CREATE TRIGGER sync_department_stats
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    UPDATE department_stats 
    SET employee_count = employee_count + 1,
        total_salary = total_salary + NEW.salary
    WHERE department_id = NEW.deptId;
END;

五、触发器的管理和查看

查看触发器：

 查看所有触发器

SHOW TRIGGERS;

 查看特定数据库的触发器

SHOW TRIGGERS FROM database_name;

 查看触发器定义

SHOW CREATE TRIGGER trigger_name;

 从信息模式查看

SELECT * FROM information_schema.TRIGGERS 
WHERE TRIGGER_SCHEMA = 'your_database';

删除触发器：

DROP TRIGGER [IF EXISTS] trigger_name;

六、触发器的限制和注意事项

1. 每个表的触发器限制：

    每个表最多支持6个触发器（BEFORE/AFTER × INSERT/UPDATE/DELETE）

    同一时刻同一事件只能有一个触发器

2. 性能考虑：

    触发器会增加数据库操作的开销

    复杂的触发器逻辑可能影响性能

3. 递归触发：

    避免创建可能导致无限循环的触发器

    例如：A表的触发器修改B表，B表的触发器又修改A表

4. 错误处理：

    BEFORE触发器中的错误会阻止操作执行

    AFTER触发器中的错误会导致整个操作回滚（对于事务表）

七、NEW 和 OLD 虚拟表的使用规则

使用示例：

 UPDATE触发器示例

CREATE TRIGGER track_salary_changes
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF OLD.salary != NEW.salary THEN
        SET NEW.last_salary_change = NOW();
    END IF;
END;

八、最佳实践建议

1. 保持简单：触发器逻辑应尽量简单明了

2. 文档记录：记录每个触发器的用途和业务逻辑

3. 性能测试：在生产环境使用前进行性能测试

4. 错误处理：使用 `SIGNAL` 语句提供清晰的错误信息

5. 避免过度使用：只在必要时使用触发器，避免复杂的依赖关系

九、完整示例：完整的员工管理系统触发器

 1. 插入前验证数据

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
     --验证工资范围
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '工资不能为负数';
    END IF;
    
     --自动设置创建时间
    SET NEW.created_at = NOW();
END;

 2. 更新后记录日志

CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF OLD.salary != NEW.salary THEN
        INSERT INTO salary_change_log (
            employee_id, 
            old_salary, 
            new_salary, 
            change_date, 
            changed_by
        ) VALUES (
            OLD.id, 
            OLD.salary, 
            NEW.salary, 
            NOW(), 
            CURRENT_USER()
        );
    END IF;
END;

 3. 删除前备份数据

CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_archive 
    SELECT *, NOW(), CURRENT_USER() 
    FROM employees 
    WHERE id = OLD.id;
END;