MySQL全面瓦解17：触发器相关

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

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

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

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

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

触发器的使用创建触发器

创建触发器的语法如下：

1 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

示例（先创建一个日志表，用于触发器触发时候填入值）：

 1 /*先创建一个日志表，用于触发器触发时候填入值*/ 2 MysqL> DROP table IF EXISTS `TriggerLog`; 3 query OK,0 rows affected 4  5 MysqLtable `TriggerLog` 6 ( 7    `ID` INT auto_increment PRIMARY KEY, 8    `trigger_time` VARCHAR(30),1)"> 9    `trigger_event` 10    `memo` 200)   11 );12 query OK,1); Font-weight: bold">0 rows affected

Insert类型触发器：

 1 MysqL>  2 这边声明sql脚本的结束符为//  3 DEliMITER // 4 TRIGGER  trig_after_insert; 5 TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROWBEGIN 7   insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after',insertnew student info,ID:cast(new.studentID as char))); 8 END  9 query OK,1)">10 11 MysqL12 重置sql脚本的结束符为; 13 DEliMITER ;14 query OK,1)">15 16 MysqL17 插入一条数据 18 into students(studentname,score,classID) values(trigger1100,1)">19 query OK,1); Font-weight: bold">1 row affected20 21 22 MysqL23 查询日志表看是否有触发写入 24 select * from25 +----+--------------+---------------+------------------------+26 | ID | trigger_time | trigger_event | memo                   |27 28 |  1 | after        | insert        | new student info,ID:21 29 30 1 row in set

 Update类型触发器：

 trig_after_update;TRIGGER trig_after_update AFTER UPDATE updateupdate student info,1)">更新数据 update students set score=99 where studentname=';20 Rows matched: 1  Changed: 1  Warnings: 0查询日志表看是否更新的时候有触发写入 --+--------------+---------------+---------------------------+| memo                      21    2 update        update student info,1)">31 2 rows set 

 Delete类型触发器： 

 trig_after_delete;TRIGGER trig_after_delete AFTER DELETE delete student info,1)">cast(old.studentID  删除数据 delete from students where studentID2121 MysqL22 查询日志看删除是否有触发写入24 3 delete student info,1); Font-weight: bold">3 rows set

查看触发器查看全部触发器

1 show triggers; --语法 

> show triggers; 2 -----------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 Trigger           | Event  table    | Statement                                                                                                                                                          | Timing | Created | sql_mode                                   | definer        | character_set_clIEnt | collation_connection Database Collation  4  5 | trig_after_insert INSERT | students  6    7 END    | AFTER  NulL    | STRICT_TRANS_tableS,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci   8 | trig_after_update UPDATE  9   10 11 | trig_after_delete DELETE 12   14 15 set

查看触发器的创建语句

1 show create trigger trigger_name;   --语法

> show trigger-----------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| sql Original Statement                                                                                                                                                                                                                                           CREATE defineR=`root`@`localhost`  9 10 set

删除触发器

drop trigger trigger_name;   语法 

1 MysqL2 query OK,1)">3 4 MysqL5 1360 - Trigger does not exist 

使用时的注意点NEW / olD的使用区别

触发器针对的是数据库中的每一行记录，每行数据在 *** 作前后都会有一个对应的状态，触发器将没有 *** 作之前的状态保存到 old 关键字中，将 *** 作后的状态保存到 new 中

1 new.cname 新增行（或修改前的行）的某列数据2 old.cname 删除行（或修改后的行）的某列数据

 

需要注意的是，old 和 new 不是所有触发器都有

触发器类型	new和old的使用
INSERT型触发器	没有 old，只有 new，new 表示将要(插入前)或者已经增加(插入后)的数据
UPDATE型触发器	既有 old 也有 new，old 表示更新之前的数据，new 表示更新之后的数据
DELETE型触发器	没有 new，只有 old，old 表示将要(删除前)或者已经被删除(删除后)的数据

其实我们上面已经用过 new/old 来创建触发器并获取数据，我们根据这个表格重新来修改下那个更新触发器(trig_after_update),输出修改前和修改后的内容比较：

from:' to:更新成绩和名称 106,studentnametrigger2' 17根据old和new 对比更新前后的值 --+--------------+---------------+--------------------------------------+| memo                                 21               21            |31 |  4 | after        | update        | from:test2,101.00 to:trigger2,106.00 |32 33 4 rows set

无法触发对同表的修改

MysqL 的触发器中不能对本表进行 insert、update 和 delete *** 作，否则会报错

 7  set score = score+1 = new.studentID;插入一条数据之后因为触发了对本表的修改，所以报错了 101,1)">19 1442 - Cant 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全面瓦解17：触发器相关全部内容，希望文章能够帮你解决MySQL全面瓦解17：触发器相关所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。