1.创建触发器
创建触发器的语法格式如下:
create trigger trigger_name trigger_time
trigger_event on table_name for each row
trigger_statement
说明:
(1)create trigger:创建触发器的关键词。触发器程序是与表有关的数据库对象.
(2)table_name:触发程序的相关表。不能将触发程序与temporary表或视图关联起来。
(3)trigger_time:是触发程序的动作时间。它可以是before或after,以指明触发程序是在激活它的语句之前或之后触发。
(4)trigger_event:trigger_event可以是下述值之一。
- insert:将新行插入表时激活触发程序。例如,通过insert、load data和replce语句。
- update:更改某一行时激活触发程序。例如,通过update语句。
- delete:从表中删除某一行时激活触发程序。例如,通过delete和replace语句。
(5)for each row:这个声明用来指定受触发事件影响的每一行,都要激活触发器的
动作。目前MySQL仅支持行级触发器,不支持语句级别的触发器(例如createtable
等语句)。for each row表示更新(insert、update或者delete)操作影响每一条记录都会执行一次触发程序。
(6)trigger_statement:当触发程序激活时执行的语句。如果执行多个语句,可使用begin … end复合语句结构。
(7)使用触发器时,触发器执行的顺序是before触发器、表数据修改操作、after触发器。其中,before表示在触发事件发生之前执行触发程序,after表示在触发事件发生之后执行触发器。因此严格意义上讲一个数据库表最多可以设置6种类型的触发器。
2.触发器的执行
触发器的执行,是由触发事件激活的,并由数据库服务器自动执行
一个数据表上可能定义了多个触发器,遵循如下的执行顺序:
- (1) 执行该表上的BEFORE触发器;
- (2) 激活触发器的SQL语句;
- (3) 执行该表上的AFTER触发器。
- (创建顺序或字母顺序)
触发程序中可以使用old关键字与new关键字。
- 触发程序中可以使用的所谓old关键字与new关键字,实际上是在触发器事件发生时,MySQL针对要修改数据的表,创建了与本表结构完全一样2个的临时表old和new,old表用于存放在数据修改过程中既有数据,new表用于存放在数据修改过程中将要更新的数据。
- 当向表插入新记录时,在触发程序中可以利用new关键字访问新记录,当需要访问新记录的某个字段值时,可以使用‚new.字段名‛的方
- 式访问。
- 当从表中删除旧记录时,在触发程序中可以利用old关键字访问旧记录,当需要访问旧记录的某个字段值时,可以使用‚old.字段名‛的
- 方式访问。
- 当修改表的某条记录时,在触发程序中可以使用old关键字访问修改前的旧记录、使用new关键字访问修改后的新
- 记录。当需要访问旧记录的某个字段值时,可以使用‚old.字段名‛的方式访问。当需要访问修改后的新记录
- 的某个字段值时,可以使用‚new.字段名‛的方式访问。
- old记录是只读的,只能引用,不能更改。在befor服务器托管网e触发程序中,可使用‚set new.col_name = value‛语句更改new记
- 录的值。
- 对于insert语句,只有new是合法的;对于delete语句,只有old才合服务器托管网法;而update语句可以与new或old同时使用。
3.触发器的创建和管理
满足触发器的触发条件时,数据库系统就会执行触发器中定义的程序语句。这样做可以保证某些操作之间的一致性。
【例1】创建一个触发器,当更改表course中某门课的课程号时,同时将score表课程号全部更新。
create trigger cno_update after update
on course for each row
begin
update score set courseno=new.courseno
where courseno=old.courseno;
end;
验证触发器cno_update的功能,代码和执行结果如下。
mysql> update course set courseno
=‘c07123’ where courseno=‘c08123’;mysql> select * from score where
courseno =’c07123′;
【例2】在teacher表中,定义一个触发器,当一个教师的信息被删除时,把该教师的编号和姓名添加到de_teacher表中。
# 创建一个空表de_teacher,表由tno和tname两列组成。
mysql> create table de_teacher select
teacherno,tname from teacher where 1=0;
#创建teacher表的触发器
create trigger trig_teacher
after delete on teacher for each row
insert into de_teacher(teacherno,tname)
values(old.teacherno, old.tname);
验证触发器trig_teacher 的功能,代码和执行结果如下。
mysql> delete from teacher where tname=‘时观’;
mysql> select * from de_teacher;
4.查看触发器的定义
show triggers语句
查询information_schema数据库下的triggers表来查看触发器的信息。
show triggers;
select * from information_schema.triggers;
select * from information_schema.triggers
where trigger_name='de_teacher';
5. 使用触发器
【例1】创建一个触发器,当删除student表某个人的记录时,删除score表相应的成绩记录。
create trigger stu_delete after delete
on student for each row
begin
delete from score where
studentno=old.studentno;
end;
验证触发器stu_delete的功能,代码和执行结果如下。
delete from student where s_id=‘1002’;
select * from sc where sc_sid=‘1002’;
本例中,在student执行delete事件之后,在触发器中引用的score表的studentno字段要用old. studentno表示。
6. 删除触发器
删除触发器指删除数据库中已经存在的触发器。MySQL使用drop trigger语句来删除触发器。其基本形式如下。
drop trigger [schema_name.]trigger_name
例如,删除触发器stu_score的代码如下:
drop trigger stu_score;
7.编程练习
试编写一个触发器trig,要求实现以下功能。向Course中添加新课(课号和课名)时,第三个Tno教师号字段值自动填入对应SC表中按教师分类成绩平均分最高的教师编号。请测试追加新课程“04,数据库”的效果。(切记:测试操作请放在最后进行!!!)
create trigger trigg
on Course
after insert
as
declare @topTno varchar(20);
SELECT @topTno=t3.Tno
FROM
(
SELECT t2.Tno,AVG(t1.score) AS AvgScore
FROM SC AS t1
LEFT JOIN Course AS t2 ON t1.Cno=t2.Cno
GROUP BY t2.Tno
) AS t3
ORDER BY t3.AvgScore DESC limit 0,1
UPDATE Course SET Tno=@topTno WHERE Tno in (select Tno from inserted)
GO
INSERT INTO Course(Cno,Cname) VALUES ('04','数据库')
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
机房租用,北京机房租用,IDC机房托管, http://www.fwqtg.net