【MySQL】MySQL创建触发器及相关资料

72 阅读 0 评论 48 点赞

我是靠谱客的博主飞快大门，最近开发中收集的这篇文章主要介绍【MySQL】MySQL创建触发器及相关资料，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

MySql触发器是指当一个表格发生某种操作之后，会引发触发器中定义的操作。而这些操作可以是如下任意一种：

INSERT: 当表的内容中被插入内容的时候触发定义的触发器，有befor和after两种事件；
UPDATE: 当表的某条记录被更新的时候触发定义的触发器，有befor和after两种事件；
DELETE: 当表的某条记录被删除的时候触发定义的触发器。但是当使用DROP TABLE 和TRUNCATE TABLE 的时候，该触发器不会被触发，因为这两个函数没使用delete函数进行操作。请看参考资料三种操作的区别。

标准的创建触发器语法如下：

CREATE
    [DEFINER = { user | CURRENT_USER }] #定义使用者，数据库有很多用户
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body

例子：

mysql> delimiter $$
mysql> create trigger my_test_trigger after DELETE on my_tst
    -> for each row
    -> begin
    -> delete from foreign_test where foreign_test.mytest_id = OLD.id;
    -> END
    -> $$ 
 mysql> delimiter $$;

上面的例子创建了一个删除的触发器，在删除my-test表格中的内容的时候，自动删除foreign_test表中的内容。foreign_test中的一列mytest_id为my_test的主键，注意并不是外键，在这里没显示定义外键。

其中定义触发器需要注意的地方是：

1：delimiter $$; 它的作用是在其语句之后所有的mysql可以识别的分号功能给取消，其实就是平时编程语言中的转义符。因为触发器中肯定会有sql语句，sql语句必须以分号结尾。而如果你使用了分号被mysql当作本次sql语句的结束而不是触发器中的内容，导致语法错误。

2：既然使用了转义字符，则必须声明转义的结束位置，所以有了END后的双美元符号。而另外还有解除转义所以还必须有显式的接触语句：mysql> delimiter $$;

最后对于创建索引中使用的OLD.id进行相应的解释：

另外还有一个和OLD使用相同场合的关键词NEW。对于OLD与NEW对大小不敏感，它们的作用是引用被触发器影响的列。

对于插入创建的触发器来说只能使用NEW.col_name，因为被插入之前是不存在旧的列。

对于删除只能使用OLD.col_name，比如面的例子，因为不会产生新的列，所以无从谈起对新的列的引用。

对于更新操作是稍微麻烦的，你可以使用 OLD.col_name来引用未更新之前的记录，也可以使用NEW.col_name来引用更新之后的记录。感觉其实性质是一样的，但是具体效果没做确认。各位看到请针对具体情况进行测试使用。

查看当前触发器 show triggers；

删除触发器则是 DROP TRIGGER trigger_name；

参考资料：

不同点:
1. truncate和 delete只删除数据不删除表的结构(定义)
drop语句将整个表的所用信息都删除。包括表的结构，被依赖的约束(constrain),触发器(trigger),索引(index); 而依赖于该表的
存储过程/函数将保留,但是变为invalid状态.

2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger, 执行的时候将被触发.
truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

3.delete语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动
显然drop语句将表所占用的空间全部释放
truncate 语句缺省情况下将空间释放到minextents个extent,除非使用reuse storage; truncate会将高水线复位(回到最开始).

4.速度,一般来说: drop> truncate > delete

对于都是删除数据的truncate与delete来讲，如果将表所有数据都删除，truncate其实是将整个不表都是删除然后重建，而对于delete则是一行一行的删除，在删除的时候肯定要竞争锁，所以比较慢。

5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及.
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.
想删除表,当然用drop
想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete.
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据/