触发器

当某个表发生更改时需要MySQL自动处理事件就是触发器。MySQL只会响应以下语句从而自动执行一条MySQL语句:

  • DELETE
  • INSERT
  • UPDATE

创建触发器

创建触发器时，需要注意一些细节：

  • 唯一的触发器名
  • 触发器关联的表
  • 触发器应该响应的活动(DELETE,INSERT,UPDATE)
  • 触发器何时执行(处理之前或之后)

在MySQL5中同一数据库中的两个表可以用同一个名字，但是同一个表中的触发器名字必须唯一。但是在DBMS数据库中触发器名只能唯一。

可以用CREATE TRIGGER语句创建触发器。

CREATE TRIGGER test_tt AFTER DELETE ON `test`  FOR EACH ROW
BEGIN
DECLARE s VARCHAR(20) DEFAULT 'hello';
SET s = 'world';
UPDATE `member` SET `name` = s WHERE id = OLD.id;
END

当在删除test表的数据时，会在对应的member对应的id中添加name字段值为world。

每个表最多支持6个触发器。单一触发器不能与多个事件或者多个表关联，所以如果需要响应多个事件必须创建多个触发器。

删除触发器

DROP TRIGGER test_tt

触发器不能修改更新，只能删除重新创建。

使用触发器

INSERT触发器

  • 在INSERT触发器代码内，可引用一个NEW的虚拟表，访问被插入的行。
  • 在BEFORE INSERT触发器中，NEW中的值也可以被更新(允许更改被插入的值)
  • 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSRT之后包含新的自动生成值
CREATE TRIGGER newarticle AFTER INSERT ON articles FOR EACH ROW SELECT NEW.title INTO @result;

该触发器按照AFTER INSERT执行，在后续往数据库中插入新数据之后会返回新数据的title列存入到变量result中。NEW指的是插入的虚拟表。

INSERT INTO articles(id,title,body) VALUES(0,'The Boy Hunters','Go with me to the great river Mississippi.');

查询result变量

SELECT @result;

DELETE触发器

  • 在DELETE触发器代码内，可以引用一个名为OLD的虚拟表来访问被删除的行
  • OLD中的所有值都是只读的，不能更新

一般都使用BEFORE DELETE触发器,相比AFTER DELETE来说更加安全，如果后续的存入数据失败，那么DELETE本身也将被取消掉。

CREATE TRIGGER newarticle BEFORE DELETE ON articles FOR EACH ROW
BEGIN
    INSERT INTO articles(id,title,body) VALUES(0,'The Boy Hunters','Go with me to the great river Mississippi.');
END//

UPDATE触发器

  • 在UPDATE触发器代码内，可以引用OLD虚拟表来访问更新前的值，也可以引用NEW虚拟表来访问更新的值
CREATE TRIGGER updatetitle BEFORE UPDATE ON articles FOR EACH ROW SET NEW.title = Upper(NEW.title);

创建一个名为updatetitle的触发器，当数据更新前会出发该触发器将新虚拟表中的title全部改为大写。

触发器的优缺点

优点

  • 与存储过程一样，执行前服务器会先预编译所以执行效率很高
  • 业务逻辑封装性好，使用简单，方便
  • 可以使用触发器来确保数据的一致性(大小写、格式等)
  • 触发器可以用作创建审计跟踪，可以用来记录操作日志
  • 可以基于数据库的值使用户具有操作数据库的某种权利。可以基于时间限制用户的操作，在特定时间内不能修改数据库。

缺点

  • 因为MySQL触发器不能使用CALL语句，所以在对于存储过程的复用性兼容很差，重复的存储过程必须重新写。
  • 由于触发器封装完整，所以看不到详细过程，一旦出了错将很难排查。
  • 占用服务器端太多的资源，对服务器造成很大的压力