数据库原理之触发器

原创

aaronthon 2022-08-22 17:22:51 博主文章分类：MySQL数据库原理 ©著作权

文章标签 触发器 mysql 数据 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者aaronthon的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、触发器概述

触发器（Trigger）是一种特殊的存储过程，它与表紧密相连，可以是定义表的一部分。当预定义的事件（如用户修改指定表或者视图中的数据）发生时，触发器将会自动执行。

触发器基于一个表创建，但是可以针对多个表进行操作。所以触发器可以用来对表实施复杂的完整性约束，当触发器所保存的数据改变时，触发器被自动激活，从而防止对数据进行不正确的修改。触发器的有点如下所述。

（1）触发器自动执行，在表的数据做了任何修改（比如手工输入或者使用程序采集的操作）之后立即激活。

（2）触发器可以通过数据库中的相关表进行层叠更改。这比直接把代码写在前台的做法更安全合理。

（3）触发器可以强制限制，这些限制比用 CHECK 约束所定义的更复杂。与 CHECK 约束不同的是，触发器可以引用其他表中的列。

二、创建触发器

因为触发器是一种特殊的存储过程，所以触发器的创建和存储过程的创建方式有很多相似之处，其基本语法如下。

create trigger trigger_name trigger_time trigger_event 
    on tb_name for

在 create trigger 语法中，各参数含义如下：

（1）trigger_name：要创建的触发器名称。

（2）tb_name：建立触发器的表名，即在哪个表上建立触发器。tb_name 必须引用永久性表。

（3）trigger_time：指定触发器触发的时机。以指明触发程序是在激活它的语句之前或之后触发。可以指定 before 或 after。

（4）trigger_event：指明激活触发程序的语句的类型。trigger_event可以是下述值之一。

① insert：将新行插入表时触发程序。例如通过 insert、load data和replace语句。

② update：更改某一行时激活触发程序。例如通过update语句。

③ delete：从表中删除某一行时激活触发程序。例如通过delete和raplace语句。

（5）for each row：触发器的执行间隔，通知触发器每隔一行执行一次动作，而不是对整个表执行一次。

（6）trigger_statement：指定触发器所执行的 SQL 语句。可以使用 BEGIN...END 作为开始和结束。

在触发器的 SQL 语句中，可以关联表中的任何列，通过使用 OLD 和 NEW 列名来标识，如OLD.col_name\NEW.col_name。OLD.col_name 关联现有行的一列在被更新或删除前的值。NEW.col_name 关联一个新行的插入或更新现有的行的一列的值。

对于 insert 语句，只有NEW 是合法的。对于delete语句，只有OLD是合法的。对于update语句，NEW和OLD可以同时使用。

例子：在gradem数据库中，当向student表添加一条学生信息时，同时还需要更新class表中的classnumber列，通过创建一个insert触发器，在用户每次向student表中添加新的学生信息时便更新响应的班级人数。这个触发器的名字为trig_classnum，其定义语句如下。

mysql> use gradem;
mysql> create trigger trig_classnum
    -> after insert on student for each now
    -> update class set number=number+1 
    -> where classno = left(new.sno,8);

为确保找到学生的班号，利用left()函数取学生学号的前八位。这样，在输入学生信息时，如果classno为空，也不会出现在student表中找不到的情况。

例：在gradem数据库的teacher表中，定义一个触发器，当一个教师的信息被删除时，把该教师的编号和姓名添加到delteacher表中。具体代码如下。

mysql> use gradem;
# 创建一个空表 delteacher，表由tno和tname两列组成。
mysql> create table delteacher select tno, tname from teacher where 1=0;
# 创建 teacher 表的触发器
mysql> create trigger trig_teacher 
    -> after delete on teacher for each row
    -> insert into delteacher (tno,tname) values(old.tno, old.tname);

例：创建一个触发器，当student表中的学生学号发生变更时，同时更新sc表中的相应的学生学号信息。

mysql> use gradem;
mysql> delimiter &&
mysql> create trigger trip_snoupdate
    -> after update on student for each row
    -> begin
    -> if new.sno != old.sno then
    ->     update sc set sno = new.sno where sno=old.sno;
    -> end if;
    -> end &&
Query OK, 0  afected
mysql> delimiter ;