mysql-09-触发器

触发器

 

触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在DBMS核心层进行集中的完整性约束。触发器类似于约束，但是比约束更加灵活，可以实施比foreign key约束、check约束更加复杂的检查和操作，具有更精细和更强大的数据控制能力。

 

 

1.定义触发器

a) 语法：create trigger 触发器名

Before| after 触发事件(insert,delete,update) on 表名

For each row|statement(注意：mysql目前只支持for each row)

[when 触发条件]

触发动作体

b) 表的拥有者即创建表的用户才能在表上创建触发器，并且只可以建立一定数量的触发器。

c) 触发器名可以包含模式名，也可以不包含模式名。同一模式下，触发器名必须是唯一的； 并且触发器名和表名必须在同一模式下。

d) 触发事件： 可以是insert、delete和update，也可以是这几个事件的组合，例如insert or delete等。Update后面还可以接 of 触发列 ,来进一步指明修改哪些列时激活触发器。

e) 触发器类型： 分为行级触发器(for each row) 和语句级触发器(for each statement)。 例如一个表有1000行，在上面定义一个语句级触发器，执行完语句后，触发动作只发生一次；如果是行级触发器，触发动作将执行1000次。Mysql暂时只支持行级触发器！

f) 触发动作体：既可以是一个匿名的PL/SQL过程块，也可以是对已创建的存储过程的调用。如果是行级触发器，在两种情况下，都可以使用new和old关键字引用update/insert事件之后的新值（一行记录）和update/delete之前的旧值(一行)。如果是语句级触发器，则不能使用这两个关键字。

g) 几个例子：

i. 定义一个before行级触发器，为教师表teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，则修改为4000”

Create trigger insert_or_update 

Before insert or update on teacher

For each row

As

Begin

If (new.job=’教授’) and (new.sal<4000) then

New.sal:=4000;

End if;

End;

ii. 定义after行级触发器，当教师表teacher的工资发生变化后就自动在sal_log日志中增加一条记录

Create table sal_log

(

Eno int references teacher(eno),

Sal decimal(10,2),

Username char(10),

);

 

Create trigger insert_sal

After insert on teacher

For each row

[Begin] //如果触发器执行体只有一条简单语句，则可以不包含在begin...end; 块中

Insert into sal_log values(new.eno, new.sal, new.username);

[End;]

 

Create trigger update_sal

After update on teacher 

For each row

Begin

If(new.sal<>old.sal) then

Insert into sal_log values(new.eno, new.sal, new.username);

End if;

End;

 

 

 

 

2.激活触发器

触发器的执行，是由触发事件激活的，并由数据库服务器自动执行的。一个数据表上可能定义了多个触发器，比如多个before触发器，多个after触发器等（注意：mysql目前只支持对一类事件定义一个触发器）。同一个表上的多个触发器激活时遵循以下顺序：

i. 执行before触发器

ii. 激活触发器的sql语句

iii. 执行after触发器

而对于多个before（after）触发器，则按创建的先后顺序执行。

 

 

3.删除触发器

Drop trigger 触发器名 on 表名；

 

 

 

4.查看触发器

1.show triggers;  查看数据库中的触发器

2.Show create trigger 触发器名；  查看触发器的创建语句

 

 

 

思考题？

如果在一个表上对insert、update、delete事件都定义了触发器，下面两条语句会触发哪些触发器呢？

1.insert into .... On duplicate key update ....    插入时主键冲突的情况

2.Repalce into ......