定义
触发器是由事件触发的某个操作,事件包括增删改事件,操作就是我们定义的触发器操作,触发条件可以是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
语句触发的表数据更新操作,不会引发触发器。
只有直接对表的增删改,才会引发监听该表的触发器。