数据库的完整性与触发器

数据库完整性

数据库完整性(DBIntegrity)是指DBMS应保证的DB的一种特性--在任何情况下的正确性、有效性和一致性。

• 广义完整性：语义完整性、并发控制、安全控制、DB故障恢复等
• 狭义完整性：专指语义完整性，DBMS通常有专门的完整性管理机制与程 序来处理语义完整性问题。(本讲专指语义完整性)

完整性涉及到的：

• 实体完整性
• 参照完整性
• 用户自定义完整性

为什么会引发数据库完整性的问题呢

• 不正当的数据库操作，如输入错误、操作失误、程序处理失误等
• 防止和避免数据库中不合理数据的出现

DBMS自动保证完整性：

• DBMs允许用户定义一些完整性约束规则（SQL——DDL来定义）
• 当有DB更新操作时，DBMS自动按照完整性约束条件进行检查，以确保更 新操作符合语义完整性

完整性约束的一般形式：

• Integrity Constraint :: = (O, P, A, R);
• O：作用对象，列，多列，元组集合
• P：谓词条件，什么样的约束
• A：出发条件，什么时候检查
• R：响应动作，不满足时怎么办

SQL语言实现静态完整性

静态约束

• 列完整性—域完整性约束
• 表完整性--关系完整性约 动态约束
• 触发器

Create Table

列约束

示例：

xxxxxxxxxx
Create  TableStudent (
    Snum char(8) not null unique, Snamechar(10), 
    Ssex char(2)  constraint  ctssexcheck  (Ssex=‘男’or   Ssex=‘女’),
    Sage integer check (Sage>=1  and Sage<150),
    Dnum char(2) references Dept(D#) on delete cascade,
    Sclasschar(6));

在定义时，后面加上约束条件

• UNIQUE：列值是唯一
• PRIMARY KEY：列为主键
• CHECK (search_cond)：列值满足条件,条件只能使用列当前值
• REFERENCES tablename [(colname)] [ON DELETE { CASCADE|SET NULL}]

引用另一表tablename的列colname的值，如有ON DELETE CASCADE 或ON DELETE SET NULL语句，则删除被引用表的某列值v时，要将本表该列值为v的记录删除或列值更新为 null；缺省为无操作。

示例:

xxxxxxxxxx
Create TableStudent (
    Snum char(8)not null unique,
    Snamechar(10),
    Ssexchar(2) constraint ctssexcheck (Ssex=‘男’ or Ssex=‘女’), 
    Sageinteger check (Sage>=1 and Sage<150),
    Dnum char(2) references Dept(D#) on delete cascade,
    Sclass char(6)
    );

表约束

示例：

xxxxxxxxxx
Create Table Student (
    Snum char(8) not null unique, 
    Sname char(10),  
    Ssex char(2) constraint ctssexcheck (Ssex=‘男’ or Ssex=‘女’),
    Sage integer check (Sage>1  and Sage<150),
    Dnum char(2) references Dept(D#) on delete cascade, 
    Sclass char(6), primary key(Snum) //仅将snum设为主键
    );

check中的条件可以是Select-From-Where内任何Where后的语句，包含子查询。

xxxxxxxxxx
Create Table SC (
    Snum char(8) check (Snum in (select S# from student)),
    C# char(3) check (C# in (select  C#  from course)),
    Score float(1) constraint ctscorecheck (Score>=0.0 and Score<=100.0);

sql中定义的表约束和列约束可以进行更改

Create Table中定义的表约束或列约束可以在以后根据需要进行撤消或追加。撤消或追加约束的语句是Alter Table(不同系统可能有差异)

示例：撤消SC表的ctscore约束(由此可见，未命名的约束是不能撤消)

xxxxxxxxxx
Alter Table SC DROP CONSTRAINT ctscore;

若要再对SC表的score进行约束，比如分数在0~150之间，则可新增加一个约束。

xxxxxxxxxx
Alter Table SC Modify (Score float(1) constraint nctscorecheck (Score>=0.0 and Score<=150.0));

有些DBMS支持独立的追加约束,注意书写格式可能有些差异

xxxxxxxxxx
Alter Table SC Add Constraint nctscorecheck (Score>=0.0 and Score<=150.0));

触发器

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

基本语法：

xxxxxxxxxx
CREATE TRIGGER trigger_name BEFORE | AFTER 
    {INSERT | DELETE | UPDATE [OFcolname{, 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的使用来区分

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

xxxxxxxxxx
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;

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

xxxxxxxxxx
create trigger sumc after lisert on sc
    referencing new row newi
    for each row
        begin
            update student set SumCourse = SumCourse + 1;
            where Snum = newi.Snum;
        end;

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

xxxxxxxxxx
create trigger updsnum after update of Snum on student
    referincing old oddi, new newi
    for each row
        begin
            update sc set Snum = newi.Snum where Snum = oldi.Snum;
        end;

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

xxxxxxxxxx
create trigger delS# after update on Student
    referincing old oldi
    for each row
        begin
            delete sc where Snum = oldi.Snum;
        end;

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

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