欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL 触发器的使用及需要注意的地方

程序员文章站 2022-03-26 18:45:12
关于触发器现实开发中我们经常会遇到这种情况,比如添加、删除和修改信息的时候需要记录日志,我们就要在完成常规的数据库逻辑操作之后再去写入日志表,这样变成了两步操作,更复杂了。又比如删除一个人员信息的时候...

关于触发器

现实开发中我们经常会遇到这种情况,比如添加、删除和修改信息的时候需要记录日志,我们就要在完成常规的数据库逻辑操作之后再去写入日志表,这样变成了两步操作,更复杂了。

又比如删除一个人员信息的时候,需要将他的购物记录、收货地址、收藏夹等都删了,这个连续的操作容易出错,一致性和完整性不好保证。这时候就要使用触发器,既可以免去一堆的业务逻辑代码,又能更好的保证数据完整性。

触发器(trigger)是一种与表有关的数据库对象:在满足定义条件时触发某种操作,并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端保证数据的完整性。

它是一种与表事件相关的特殊的存储过程,当对一个表进行操作( insert,delete, update)时就会激活执行。

触发器的使用

创建触发器

创建触发器的语法如下:

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 set

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('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 set

delete类型触发器:

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。

以上就是mysql 触发器的使用及需要注意的地方的详细内容,更多关于mysql 触发器的资料请关注其它相关文章!

相关标签: MySQL 触发器