使用MySQL 触发器时需要注意哪些事项
创始人
2024-12-29 06:04:03
0次
触发器的使用
创建触发器
创建触发器的语法如下：
CREATE TRIGGER trigger_name trigger_time trigger_event ON t_name FOR EACH ROW trigger_stmt
说明：
trigger_name：触发器名称
tirgger_time：触发执行时间点，数据操作前(BEFORE) 或者 数据操作后(AFTER)
trigger_event：触发事件，增(INSERT)、删(DELETE)、改(UPDATE)
t_name：指的是在 t_name 这张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
触发器只能创建在永久表（Permanent）上，不能创建在临时表（Temporary）上
FOR EACH ROW 固定表达式，表示任何一条记录上的操作满足触发事件都会触发该触发器
tirgger_time有两种类型，trigger_event有三种类型，所以他们一共有6种组合：BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE,AFTER INSERT,AFTER DELETE,AFTER UPDATE
示例（先创建一个日志表，用于触发器触发时候填入值）：
/*先创建一个日志表，用于触发器触发时候填入值*/ mysql> DROP TABLE IF EXISTS `TriggerLog`; Query OK, 0 rows affected mysql> CREATE TABLE `TriggerLog` ( `id` INT auto_increment PRIMARY KEY, `trigger_time` VARCHAR(30), `trigger_event` VARCHAR(30), `memo` VARCHAR(200) ); Query OK, 0 rows affected
Insert类型触发器：
mysql>
/*这边声明SQL脚本的结束符为// */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_insert;
CREATE TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW
BEGIN
insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','insert',concat('new student info,id:',cast(new.studentid as char)));
END //
Query OK, 0 rows affected
mysql>
/*重置SQL脚本的结束符为; */
DELIMITER ;
Query OK, 0 rows affected
mysql>
/*插入一条数据 */
insert into students(studentname,score,classid) values('trigger1',100,0);
Query OK, 1 row affected
mysql>
/*查询日志表看是否有触发写入 */
select * from `TriggerLog`;
+----+--------------+---------------+------------------------+
| id | trigger_time | trigger_event | memo |
+----+--------------+---------------+------------------------+
| 1 | after | insert | new student info,id:21 |
+----+--------------+---------------+------------------------+
1 row in setUpdate类型触发器：
mysql>
/*这边声明SQL脚本的结束符为// */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_update;
CREATE TRIGGER trig_after_update AFTER UPDATE ON students FOR EACH ROW
BEGIN
insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('update student info,id:',cast(new.studentid as char)));
END //
Query OK, 0 rows affected
mysql>
/*重置SQL脚本的结束符为; */
DELIMITER ;
Query OK, 0 rows affected
mysql>
/*更新数据 */
update students set score=99 where studentname='trigger1';
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
/*查询日志表看是否更新的时候有触发写入 */
select * from `TriggerLog`;
+----+--------------+---------------+---------------------------+
| id | trigger_time | trigger_event | memo |
+----+--------------+---------------+---------------------------+
| 1 | after | insert | new student info,id:21 |
| 2 | after | update | update student info,id:21 |
+----+--------------+---------------+---------------------------+
2 rows in setDelete类型触发器：
mysql>
/*这边声明SQL脚本的结束符为// */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_delete;
CREATE TRIGGER trig_after_delete AFTER DELETE ON students FOR EACH ROW
BEGIN
insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('delete student info,id:',cast(old.studentid as char)));
END //
Query OK, 0 rows affected
mysql>
/*重置SQL脚本的结束符为; */
DELIMITER ;
Query OK, 0 rows affected
mysql>
/* 删除数据 */
delete from students where studentid=21;
Query OK, 1 row affected
mysql>
/*查询日志看删除是否有触发写入*/
select * from `TriggerLog`;
+----+--------------+---------------+---------------------------+
| id | trigger_time | trigger_event | memo |
+----+--------------+---------------+---------------------------+
| 1 | after | insert | new student info,id:21 |
| 2 | after | update | update student info,id:21 |
| 3 | after | update | delete student info,id:21 |
+----+--------------+---------------+---------------------------+
3 rows in set查看触发器
查看全部触发器
show triggers; --语法
mysql> show triggers;
+-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+
| trig_after_insert | INSERT | students | BEGIN
insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','insert',concat('new student info,id:',cast(new.studentid as char)));
END | AFTER | NULL | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci |
| trig_after_update | UPDATE | students | BEGIN
insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('update student info,id:',cast(new.studentid as char)));
END | AFTER | NULL | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci |
| trig_after_delete | DELETE | students | BEGIN
insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('delete student info,id:',cast(old.studentid as char)));
END | AFTER | NULL | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci |
+-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+
3 rows in set查看触发器的创建语句
show create trigger trigger_name; --语法
mysql> show create trigger trig_after_insert;
+-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation |
+-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| trig_after_insert | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW
BEGIN
insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','insert',concat('new student info,id:',cast(new.studentid as char)));
END | utf8 | utf8_general_ci | latin1_swedish_ci |
+-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set删除触发器
drop trigger trigger_name; --语法
mysql> drop trigger trig_after_insert; Query OK, 0 rows affected mysql> show create trigger trig_after_insert; 1360 - Trigger does not exist
使用时的注意点
NEW / OLD的使用区别
触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中
new.cname --新增行（或修改前的行）的某列数据 old.cname --删除行（或修改后的行）的某列数据
需要注意的是，old 和 new 不是所有触发器都有
| 触发器类型 | new和old的使用 |
|---|---|
| INSERT型触发器 | 没有 old，只有 new，new 表示将要(插入前)或者已经增加(插入后)的数据 |
| UPDATE型触发器 | 既有 old 也有 new，old 表示更新之前的数据，new 表示更新之后的数据 |
| DELETE型触发器 | 没有 new，只有 old，old 表示将要(删除前)或者已经被删除(删除后)的数据 |
其实我们上面已经用过 new/old 来创建触发器并获取数据，我们根据这个表格重新来修改下那个更新触发器(trig_after_update),输出修改前和修改后的内容比较：
mysql>
/*这边声明SQL脚本的结束符为// */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_update;
CREATE TRIGGER trig_after_update AFTER UPDATE ON students FOR EACH ROW
BEGIN
insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('from:',old.studentname,',',old.score,' ','to:',new.studentname,',',new.score));
END //
Query OK, 0 rows affected
mysql>
/*重置SQL脚本的结束符为; */
DELIMITER ;
Query OK, 0 rows affected
mysql>
/*更新成绩和名称 */
update students set score=106,studentname='trigger2' where studentid=17;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
/*根据old和new 对比更新前后的值 */
select * from `TriggerLog`;
+----+--------------+---------------+--------------------------------------+
| id | trigger_time | trigger_event | memo |
+----+--------------+---------------+--------------------------------------+
| 1 | after | insert | new student info,id:21 |
| 2 | after | update | update student info,id:21 |
| 3 | after | update | delete student info,id:21 |
| 4 | after | update | from:test2,101.00 to:trigger2,106.00 |
+----+--------------+---------------+--------------------------------------+
4 rows in set无法触发对同表的修改
MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作，否则会报错
mysql>
/*这边声明SQL脚本的结束符为// */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_insert;
CREATE TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW
BEGIN
update students set score = score+1 where studentid= new.studentid;
END //
Query OK, 0 rows affected
mysql>
/*重置SQL脚本的结束符为; */
DELIMITER ;
Query OK, 0 rows affected
mysql>
/*插入一条数据之后因为触发了对本表的修改，所以报错了 */
insert into students(studentname,score,classid) values('trigger2',101,0);
1442 - Can't update table 'students' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.小结
1、触发器可以通过数据库中的关联表实现级联更改，即一张表数据的改变会影响其他表的数据，如数据修改，数据统计，数据复制等。
2、可以保证数据安全，并进行安全校验，限制用户具有操作数据库的某种权利。
3、可以对复杂逻辑的实现进行数据完整性检查和约束。
4、使用触发器的要应需而用，如果过分依赖触发器，会影响数据库的结构，增加数据库的执行和维护成本。
5、触发器有BEFORE触发器和AFTER触发器的却别，所以它的执行步骤是先执行 BEFORE触发器，再执行业务脚本，最后执行AFTER触发器。需要注意的是 某一个步骤错误后就不再执行了， 如果是事务表，会被回滚，如果是非事务表，那么就无法回滚了，数据可能有不一致性。
触发器的两种限制
1、触发程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL语句，但是允许存储程序通过参数将数据返回触发程序，也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。
2、不能再触发器中使用以显示或隐式方式开始或结束事务的语句，如START TRANS-ACTION,COMMIT或ROLLBACK。
相关内容
热门资讯
空调连续工作多久需要停机休息
空调连续工作的时间取决于多个因素，包括空调的型号、功率、使用环境和维护情况等。一般来说，空调可以连续...
空调哪个符号是除湿的
空调除湿功能通常用一个水滴形状的符号表示。不同品牌和型号的空调遥控器可能会有所差异，但一般来说，除湿...
格力空调多长时间加一次氟利昂
1、一般情况下，空调对氟利昂的使用量是非常小的，所以在空调使用了四年左右的时候再考虑加也没问题。2、...
空调用多长时间需要加氟一次
一般情况下是五年。当出现以下这几种情况时，空调才需要加氟。1、空调的室内机和室外机各有两个接口，当接...
格调生活·C标思享荟：卡萨帝在...
9月12日的郑州龙湖金融岛，夜风轻拂，灯光璀璨。“C标时刻共传奇——卡萨帝思享荟暨20周年品牌盛典郑...
品牌价值破千亿、高端份额持续领...
9月12日，“C标时刻共传奇——卡萨帝思享荟暨20周年品牌盛典郑州站”在郑州龙湖金融岛举办。2026...
空调主板坏了修800还是换新的
空调主板坏了是修还是换新，需要考虑多个因素，以下是一些建议：1. 维修成本：如果维修费用较低，比如 ...
空调开多久需要休息多长时间
问题：空调开多久需要休息多长时间回答：1、在使用空调的过程中，一般空调开7~8个小时左右需要休息2~...
中央空调多长时间清洗一次
1、一般空调开机前清洗一次即可，空调开机中间时段再清洗一次，空调关机时清洗一次，这样比较合理。2、空...
中央空调氟利昂多久加一次
看空调制热和制冷程度，很多人的观点认为氟利昂必须一年放一次，其实这个观点是错误的，理解上面有点偏差，...