数据库原理之触发器
一、触发器概述
触发器(Trigger)是一种特殊的存储过程,它与表紧密相连,可以是定义表的一部分。当预定义的事件(如用户修改指定表或者视图中的数据)发生时,触发器将会自动执行。
触发器基于一个表创建,但是可以针对多个表进行操作。所以触发器可以用来对表实施复杂的完整性约束,当触发器所保存的数据改变时,触发器被自动激活,从而防止对数据进行不正确的修改。触发器的有点如下所述。
(1)触发器自动执行,在表的数据做了任何修改(比如手工输入或者使用程序采集的操作)之后立即激活。
(2)触发器可以通过数据库中的相关表进行层叠更改。这比直接把代码写在前台的做法更安全合理。
(3)触发器可以强制限制,这些限制比用 CHECK 约束所定义的更复杂。与 CHECK 约束不同的是,触发器可以引用其他表中的列。
二、创建触发器
因为触发器是一种特殊的存储过程,所以触发器的创建和存储过程的创建方式有很多相似之处,其基本语法如下。
create trigger trigger_name trigger_time trigger_event
on tb_name for each row trigger_statement;
在 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 ;
三、管理触发器
3.1 查看触发器
查看触发器是指查看数据库中已经存在的触发器的定义、状态和语法信息等。
(1)SHOW TRIGERS
show triggers;
(2)
select * from information_schema.triggers;
3.2 删除触发器
使用 DROP TRIGGER 语句可以删除触发器。
drop trigger [dbname.]trig_name;
结束!