SQL 触发器

• 触发器（trigger）是用户定义在关系表上的一类由事件驱动的特殊过程，一旦定义，触发器将保存在数据库服务器中，任何用户对表的增，删，改操作均由服务器自动激活相应的触发器，在关系数据库管理系统核心层进行集中的完整性控制，触发器类似于约束，但是比约束更加灵活，可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力。
• 触发器SQL99之后再写入SQL标准，但是很多关系数据库管理系统很早就支持触发器，因此不同的关系数据库管理系统实现的触发器语法各不相同，互不兼容。

定义触发器

• 触发器又叫作事件-条件-动作（event-condition-action）规则，当特定的系统事件（如对一个表的增，删，改操作，事务的结束等）发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作，规则中的动作体可以很复杂，可以涉及其他表和其他数据库对象，通常是一段SQL存储过程。
• SQL使用CREATE TRIGGER命令建立触发器，其一般格式为;

CREATE TRIGGER<触发器名> /*每当触发事件发生时，该触发器被激活*/
{BEFORE|AAFTER}<触发事件> ON <表名> /*指明触发器激活的时间是在执行触发事件前或后*/
REFERENCING NEW|OLD ROW AS<变量> /*REFERENCING指出引用的变量*/
FOR EACH{ROW|STATEMENT} /*定义触发器的类型，指明动作体执行的频率*/
[WHEN<触发条件>]<触发动作体> /*仅当触发条件为真时才执行触发动作体*/

• 下面对定义触发器的各部分语法进行详细说明：

  • 触发器创建权限

    只有表的拥有者，即创建表的用户才可以才表上创建触发器，并且一个表只能创建一定数量的触发器，触发器的具体数量由具体的关系数据库管理系统在设计时确定。

  • 触发器名

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

  • 表名

    触发器只能定义在基本表上，不能定义在视图上，当基本表的数据发生变化时，将激活定义在该表上相应触发事件的触发器，因此该表也也称为触发器的目标表。

  • 触发事件

    触发事件可以时INSERT,DELETE或UPDATE，也可以是这几个事件的组合，如INSERT OR DELETE等，还可以是UPDATE OF<触发列，…>，即进一步指明修改哪些列时激活触发器，AFTER/BEFORE是触发的时机，AFTER表示在触发事件的操作执行之后激活触发器，BEFORE表示在触发事件的操作执行之前激活触发器。

  • 触发器类型

    • 触发器按照所触发动作的间隔尺寸可以分为行级触发器（FOR EACH ROW）和语句级触发器（FOR EACH STATEMENT)

    • 例如，假设在例5.11的TEACHER表上创建了一个AFTER UPDATE触发器，触发事件是UPDATE语句：

      UPDATE TEACHER SET Deptno=5;

    • 假设表TEACHER有1000行，如果定义的触发器为语句级触发器，那么执行完UPDATE语句后触发动作执行一次，如果是行级触发器，触发动作体将执行1000次。

  • 触发条件

    触发器被激活时，只有当触发条件为真时触发动作体才执行，否则触发动作体不执行，如果省略WHEN触发条件，则触发动作体在触发器激活后立即执行。

  • 触发动作体

    • 触发动作体既可以是一个匿名PL/SQL过程块，也可以是对已创建存储过程的调用，如果是行级触发器，用户可以在过程体中使用NEW和OLD引- 用UPDATE/INSERT事件之后的新值和UPDATE/DELETE事件之前的旧值，如果是语句级触发器，则不能在触发动作体中使用NEW或OLD进行引用。
    • 如果触发动作体执行失败，激活触发器的事件（即对数据库的增，删，改操作）就会终止执行，触发器的目标表或触发器可能影响的其他对象不发生任何变化。

[例5.21]：当对表SC的Grade属性进行修改时，若分数增加了10%，则将此次操作记录到另一个表SC_U(Sno,Cno,Oldgrade,Newgrade）中，其中Oldgrade是修改前的分数，Newgrade是修改后的分数。

CREATE TRIGGER SC_T  /*SC_T是触发器的名字*/
AFTER UPDATE   Grade ON [S-T].SC /*UPDATE OF Grade是触发事件，AFTER是触发的时机，表示对SC的Grade属性修改完后再触发下面的规则*/
REFERENCING
 OLDROW AS OldTuple,
 NEWROW AS NewTuple
FOR EACH ROW    /*行级触发器，即每执行一次Grade的更新，下面的规则就执行一次*/
WHEN(NewTuple.Grade>=1.1*OldTuple.Grade)    /*触发条件，只有该条件为真时才执行下面的INSERT操作*/
 INSERT  INTO SC_U(Sno,Cno,OldGrade,NewGrade)
 VALUES (OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

在本例中REFERENCING指出引用的变量，如果触发事件是UPDATE操作并且有FOR EACH ROW子句，则可以引用的变量有OLDROW和NEWROW，分别表示修改之前的元组和修改之后的元组，若没有FOR EACH ROW子句，则可以引用的变量有OLDTABLE和NEWTABLE,OLDTABLE表示表中原来的内容，NEWTABLE表示表中变化后的部分。

[例5.22]：将每次对表Student的插入操作所增加的学生个数记录到表Student-InsertLog中。

CREATE TRIGGER Student_Count
AFTER INSERT ON Student /*指明触发器激活的时间是在执行INSERT后*/
REFERENCING
NEW TABLE AS DELTA
FOR EACH STATEMENT /*语句级触发器，即执行完INSERT语句后下面的触发动作体才执行一次*/
INSERT INTO StudentInsertLog(Numbers)
SELECT COUNT(*) FROM DELTA

在本例中出现的FOR EACH STATEMENT，表示触发事件INSERT语句执行完成后才执行一次触发器中的动作，这种触发器叫做语句级触发器，而例5.21中的触发器是行级触发器，默认的触发器是语句级触发器，DELTA是一个关系名，其模式于Student相同，包含的元组是INSERT语句增加的元组。

[例5.23]：定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则"教授的工资不得低于4000元，如果低于4000元，自动改为4000元"

CREATE TRIGGER Insert_Or_Update_Sal/*对教师表插入或更新时激活触发器*/
BEFORE INSERT OR UPDATE ON Teacher/*BEFORE触发事件*/
REFERENCING
 NEW ROW AS newTuple
FOR EACH ROW    /*这是行级触发器*/
BEGIN       /*定义触发动作体，这是一个PL/SQL过程块*/
 IF(newTuple.Job='教授') AND (newTuple.Sal<4000)/*因为时行级触发器，可在过程体中*/
     THEN newTuple.Sal=4000;/*使用插入或更新操作后的新值*/
 END IF;
END; /*触发动作体结束*/

因为定义的是BEFORE触发器，在插入和更新教师记录前就可以按照触发器的规则调整教授工资，不必等插入后再检查再调整。

激活触发器

• 触发器的执行是由触发事件激活，并由数据库服务器自动执行的，一个数据表上可能定义了多个触发器，如多个BEFORE触发器，多个AFTER触发器等，同一个表上的多个触发器激活时遵循如下的执行顺序：
  • 执行该表上的BEFORE触发器。
  • 激活触发器的SQL语句。
  • 执行该表上的AFTER触发器。
• 对于同一个表上的多个BEFORE(AFTER）触发器，遵循"谁先创建谁先执行"的原则，即按照触发器创建的时间先后顺序执行，有些关系数据库管理系统是按照触发器名称的字母排序顺序执行触发器。

删除触发器

• 删除触发器的SQL语句如下：

DROP TRIGGER <触发器名> ON <表名>;

• 被删除的触发器必须是一个已经创建的触发器，并且只能由具体相应权限的用户删除。
• 触发器是一种功能强大的工具，但在使用时要慎重，因为再每次访问一个表时都可能触发一个触发器，这样会影响系统的性能。