MySQL学习笔记之触发器

定义

触发器是由事件触发的某个操作，事件包括增删改事件，操作就是我们定义的触发器操作，触发条件可以是before/after insert/delete/update。

建表

CREATE TABLE test_trigger (
    id INT PRIMARY KEY auto_increment,
    t_note VARCHAR(30)
);

CREATE TABLE test_trigger_log (
    id INT PRIMARY KEY auto_increment,
    t_log VARCHAR(30)
);

创建触发器

before触发器

创建触发器：

CREATE TRIGGER before_insert_test_trigger # 触发器名
BEFORE INSERT ON test_trigger # 定义触发条件(插入新数据之前)，监听的表
FOR EACH ROW # 定义触发动作
BEGIN
    INSERT INTO test_trigger_log (t_log) VALUES (
        'before_insert'
    );
END;

执行触发器对应的SQL语句：

INSERT INTO test_trigger (t_note) values ("a"), ("b");

查看触发器执行结果：

SELECT * from test_trigger_log;

after触发器

创建触发器：

CREATE TRIGGER after_insert_test_log
AFTER INSERT ON test_trigger # 定义触发条件(插入新数据之后)
FOR EACH ROW
BEGIN
    INSERT INTO test_trigger_log (t_log) VALUES (
        'after_insert'
    );
END;

执行触发器对应的SQL语句：

INSERT INTO test_trigger (t_note) values ("c"), ("d");

查看触发器执行结果：

SELECT * from test_trigger_log;

案例

定义触发器，添加新员工前，如果其工资高于其领导工资，则报错：

CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    DECLARE mgr_sal DOUBLE;
    select salary into mgr_sal from employees
    where employees.employee_id = NEW.manager_id;
    # 插入的新数据为NEW，删除的老数据为OLD，NEW和OLD均为内置关键字

    IF NEW.salary > mgr_sal THEN
        SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = "员工工资高于领导工资";    
        # 触发异常，定义异常状态和异常信息
    END IF;
END;

插入数据进行测试：

INSERT INTO employees (employee_id, last_name, email, hire_date, job_id, salary, manager_id)
VALUES (300, 'Jason', 'Jason@qq.com', CURDATE(), 'AD_VP', 4000, 103);

SELECT * FROM employees;

INSERT INTO employees (employee_id, last_name, email, hire_date, job_id, salary, manager_id)
VALUES (301, 'Mike', 'Mike@qq.com', CURDATE(), 'AD_VP', 8000, 103);

删除触发器

DROP TRIGGER salary_check_trigger;

查看触发器

查看全部触发器

SHOW TRIGGERS;

查看某个触发器的创建信息

SHOW CREATE TRIGGER salary_check_trigger;

查看全部触发器的详细信息

SELECT * FROM information_schema.`TRIGGERS`;

优缺点

优点

• 保证数据的完整性；
• 帮助我们记录日志；
• 帮助我们在插入新数据前进行合法性检查。

缺点

• 可读性差；
• 隐蔽性太强，有时会影响排错效率。

注意事项

由外键指定的on update/delete cascade/set null语句触发的表数据更新操作，不会引发触发器。
只有直接对表的增删改，才会引发监听该表的触发器。