利用SQL语言实现数据库的动态完整性

利用SQL语言实现数据库的动态完整性

实现数据库动态完整的方法—触发器Trigger

触发器Trigger

• Create Table中的表约束和列约束基本上都是静态的约束，也基本上都是对单一列或单一元组的约束(尽管有参照完整性)，为实现动态约束以及多个元组之间的完整性约束，就需要触发器技术Trigger。

• Trigger是一种过程完整性约束(相比之下，Create Table中定义的都是非过程性约束),是一段程序，该程序可以在特定的时刻被自动触发执行，比如在一次更新操作之前执行，或在更新操作之后执行。

• 基本语法

CREATE TRIGGER trigger_name BEFORE | AFTER
{ INSERT | DELETE | UPDATE [OF colname {, colname...}] }
ON tablename [REFERENCING corr_name_def {, corr_name_def...} ]
[FOR EACH ROW | FOR EACH STATEMENT]
//对更新操作的每一条结果(前者)，或整个更新操作完成(后者) [WHEN (search_condition)] //检查条件，如满足执行下述程序
{ statement //单行程序直接书写，多行程序要用下行方式
| BEGIN ATOMIC statement; { statement;...} END }

• 触发器Trigger意义：当某一事件发生时(Before|After),对该事件产生的结果(或是每一元组，或是整个操作的所有元组), 检查条件search_condition,如果满足条件，则执行后面的程序段。条件或程序段中引用的变量可用corr_name_def来限定。

• 事件：BEFORE | AFTER { INSERT | DELETE | UPDATE …}

  • 当一个事件(Insert, Delete, 或Update)发生之前Before或发生之后After触发。

  • 操作发生，执行触发器操作需处理两组值：更新前的值和更新后的值，这两个值由corr_name_def的使用来区分。
    

• corr_name_def的定义。

{ OLD [ROW] [AS] old_row_corr_name //更新前的旧元组命别名为
| NEW [ROW] [AS] new_row_corr_name //更新后的新元组命别名为
| OLD TABLE [AS] old_table_corr_name //更新前的旧Table命别名为
| NEW TABLE [AS] new_table_corr_name //更新后的新Table命别名为
}

• corr_name_def将在检测条件或后面的动作程序段中被引用处理。
  

触发器Trigger示例

示例一

设计一个触发器当进行Teacher表更新元组时, 使其工资只能升不能降。

create trigger teacher_chgsal before update of salary
on teacher
referencing new x, old y
for each row when (x.salary < y.salary)
begin
raise_application_error(-20003, 'invalid salary on update'); 
//此条语句为Oracle的错误处理函数
end;

Integrity Constraint ::= ( O，P，A，R)

示例二

假设student(S#, Sname, SumCourse), SumCourse为该同学已学习课程的门数，初始值为0，以后每选修一门都要对其增1 。设计一个触发器自动完成上述功能。

create trigger sumc after insert on sc
referencing new row newi
for each row
begin
update student set SumCourse = SumCourse + 1
where S# = :newi.S# ; 
end;

Integrity Constraint ::= ( O，P，A，R)

示例三

假设student(S#, Sname, Sage, Ssex, Sclass)中某一学生要变更其主码S#的值，如使其原来的98030101变更为99030131, 此时sc表中该同学已选课记录的S#也需自动随其改变。设计一个触发器完成上述功能。

create trigger updS# after update of S# on student
referencing old oldi, new newi
for each row 
begin
update sc set S# = newi.S# where S# = :oldi.S# ;
end;

Integrity Constraint ::= ( O，P，A，R)

示例四

假设student(S#, Sname, SumCourse), 当删除某一同学S#时，该同学的所有选课也都要删除。设计一个触发器完成上述功能。

create trigger delS# after delete on Student
referencing old oldi
for each row 
begin
delete sc where S# = :oldi.S# ;
end;

Integrity Constraint ::= ( O，P，A，R)

示例五

假设student(S#, Sname, SumCourse), 当删除某一同学S#时，该同学的所有选课中的S#都要置为空值。设计一个触发器完成上述功能。

create trigger delS# after delete on Student
referencing old oldi
for each row 
begin
update sc set S# = Null where S# = :oldi.S# ;
end;

Integrity Constraint ::= ( O，P，A，R)

示例六

假设Dept(D#, Dname, Dean), 而Dean一定是该系教师Teacher(T#, Tname, D#, Salary)中工资最高的教师。设计一个触发器完成上述功能。

create trigger upddean before update of Dean on Dept 
referencing old oldi, new newi
for each row when ( dean not in 
(select Tname from Teacher where D# = :newi.D# 
and salary >=
all (select salary from Teacher where D# = :newi.D#))
begin
raise_application_error(-20003, 'invalid Dean on update'); 
end;

Integrity Constraint ::= ( O，P，A，R)