乐趣区

关于数据库:数据库系统概论王珊第五章数据库完整性第四六七节约束命名子句断言和触发器

jiezi
  • pdf 下载：明码 7281
  • 专栏目录首页：【专栏必读】（考研复试）数据库系统概论第五版（王珊）专栏学习笔记目录导航及课后习题答案详解

一：完整性束缚命名子句（CONSTRAINT）

SQL 中还提供了完整性束缚命名子句 CONSTRAINT 用来对完整性约束条件进行 命名，从而能够灵便地减少、删除一个完整性约束条件

  • 还记得（数据库系统概论 | 王珊）第三章关系数据库规范语言 SQL- 第二、三节：数据定义这一节讲到批改根本表中，有些选项便是对于约束条件的，命名后后续批改时间接 应用名字即可

（1）完整性束缚命名子句

语法

演示

例如，建设学生登记表Student，要求学号在 90000~99999 之间，姓名不能取空值，年龄小于 30，性别只能是“男”或“女”

CREATE TABLE Student
(Sno NUMERIC(6) CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999),
    Sname CHAR(20) CONSTRAINT C2 NOT NULL,
    Sage NUMERIC(3) CONSTRAINT C3 CHECK(Sage < 30),
    Ssex CHAR (2) CONSTRAINT C4 CHECK(Ssex IN ('男','女')),
    CONSTRAINT StudentKey PRIMARY KEY(Sno)
);
  • 因而在 Student 表上建设了 StudentKey、C1、C2、C3、C4 这 5 个约束条件

例如，建设老师表 Teacher, 要求每个老师的应发工资不低于 3000 元。应发工资是工资列Sal 与扣除项 Deduct 之和

CREATE TABLE Teahcer
(Tno NUMERIC(4) PRIMARY KEY,
    TName CHAR(10),
    TSal NUMERIC(7,2),
    TDeduct NUMERIC(7,2),
    TDeptno NUMERIC(2),
    
    CONSTRAINT TeacherKey FOREIGN KEY(Tdeptno) REFERENCES DEPT(TDeptno),
    CONSTRAINT C1 CHECK(TSal+TDeduct >= 3000)
);
  • 因而在 Teacher 表上建设了 TeacherKey、C1 这 2 个约束条件

（2）批改表中的完整性限度

语法：这一点，咱们在（数据库系统概论 | 王珊）第三章关系数据库规范语言 SQL- 第二、三节：数据定义这一节中讲到过

  • ADD：用于减少新列，新的列级完整性约束条件和新的表级完整性约束条件
  • DROP COLUMN：用于删除表中的列
  • DROP CONSTRAINT：用于删除指定的完整性约束条件
  • ALTER COLUMN：用于批改原有的列定义

演示

例如，去除 Student 表中对性别的限度

ALTER TABLE Student
DROP CONSTRAINT C4;

例如，批改 Student 表中的约束条件，要求学号改为在 0~100 之间，年龄由小于 30 改为小于 40

  • 策略就是 先删除再减少
ALTER TABLE Student DROP CONSTRAINT C1;
ALTER TABLE Student ADD CONSTRAINT C1 CHECK(Sno BETWEEN 0 AND 100);
ALTER TABLE Student DROP CONSTRAINT C3;
ALTER TABLE Student ADD CONSTRAINT C3 CHECK(Sage < 40);

二：断言（ASSERTION）

  • 在高级语言，例如 C ++、JAVA 中就在常常应用断言assert，所以它的作用大家应该比拟相熟

在 SQL 中通过申明性断言能够指定 更具一般性的束缚（例如波及多表、汇集操作等）。创立断言后，任何对断言中所波及关系的操作都会触发 DBMS 对断言的查看，任何断言为 FALSE 的操作都会被回绝

（1）创立断言

语法：在 SQL 中，应用 CREATE ASSERTION 语句来创立断言，格局如下

演示

例如，限度数据库课程最多 60 名学生选修

CREATE ASSERTION ASS_SC_DB_NUM     CHECK
(
    60 >= 
    (SELECT count(*)
        FROM Cource,sc
        WHERE sc.Cno=Course.Cno AND Course.Cname='数据库'
    )
);

例如，限度每一门课程最多 60 名学生选修

CREATE ASSERTION ASS_SC_CNUM CHECK
(
    60 >=
    ALL
    (SELECT count(*)
        FROM SC
        GROUP BY Cno
    )
);

（2）删除断言

语法：

三：触发器（TRIGGER）

触发器（TRIGGER）：是用户定义在关系表上的一类由事件驱动的非凡过程，在满足肯定条件或达到肯定阈值时会主动触发。能够进行更为简单的检查和操作，具备更精密和更弱小的数据控制能力

（1）定义触发器

触发器又叫做 事件 - 条件 - 动作 (event- condition-action) 规定 。当 特定的零碎事件 (如对一个表的增、删、改操作，事务的完结等) 产生时，对规定的条件进行查看，如果条件成立则执行规定中的动作，否则不执行该动作。规定中的动作体能够很简单，能够波及其余表和其余数据库对象，通常是一段 SQL 存储过程

  • 就如同古老的 VB 语言就是一种以事件驱动为机制的语言，例如一些控件的CLICK（点击）事件等等

语法：SQL 应用 CREATE TRIGGER 语句定义触发器，其格局如下

  • 只有表的 创建者 才能够在表上创立触发器，并且一个表上只能创立无限数量的触发器
  • 触发器名 ：能够蕴含模式名，也能够不蕴含； 同一模式下，触发器名必须是惟一的，并且触发器名和表名必须在同一模式下
  • 表名 ： 触发器只能定义在表上，不可在视图上。当根本表的数据发生变化时，将激活定义在该表上相应触发事件的触发器
  • 触发事件 ： 能够是 INSERT、DELETE 或 UPDATE，也能够是它们的组合 ；同样也能够UPDATE OF< 列名,...,>，也即进一步指明哪些列变动时须要激活触发器；AFTER/BEFORE 是触发机会
  • 触发器类型 ： 触发器依照所触发动作的距离尺寸能够分为行级触发器（FOR EACH ROW）和语句级触发器（FOR EACH STATEMENT）；语句级会执行一次，行级执行的次数以表的具体行数而定
  • 触发条件 ：触发器被激活时，只有当 触发条件为真 时触发动作体才执行，否则触发动作体不执行；如果省略 WHEN 触发条件，则触发动作体在触发器激活后立刻执行
  • 触发动作体 ： 触发动作体既能够是一个匿名 PL/SQL 过程块，也能够是对已创立存储过程的调用。如果是 行级触发器 ，用户能够在过程体中应用NEW 和OLD援用 UPDATE/INSERT 事件之后的新值和 UPDATE/DELETE 事件之前的旧值; 如果是 语句级触发器 ，则不能在触发动作体中应用NEW 或OLD进行援用。如果触发动作体执行失败，激活触发器的事件 (即对数据库的增、删、改操作) 就会终止执行，触发器的指标表或触发器可能影响的其余对象不产生任何变动

演示：

【例如】当对 SC 表的 Grade 属性进行批改时，若分数减少了 10%，则将此次操作
记录到另一个表 SC_ U (Sno、Cno、Oldgrade、Newgrade) 中，其中Oldgrade 是批改前的分数，Newgrade 是批改后的分数

CREATE TRIGGER SC_T // 触发器名字
AFTER OF Grade ON SC // 在对 sc 表的 Grade 更新后再触发

REFERENCING
     OLDROW AS OldTuple
      NEWROW AS NewTuple
FOR EACH ROW // 行级触发器，也即每更新一次，上面规定就会执行一次
WHEN (NewTuple.Grade >= 1.1*OldTuple.Grade) // 触发条件为真才会执行
    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 和NEW TABLE, OLDTABLE示意 表中原来的内容 ，NEWTABLE 示意 表中变动后的局部

【例如】将每次对 Student 表的 插入操作所减少的学生个数 记录到StudentInsertLog 中

CREATE TRIGGER Student_Count
AFTER INSERT ON Student
REFERENCING
    NEWTABLE AS DELTA

FOR EACH STATEMENT // 语句级
    INSERT INTO StudentInsertLog(Numbers)
    SELECT COUNT(*) FROM DELTA
  • DELTA是一个关系名，其模式与 Student 雷同，蕴含的元组是 INSERT 语句减少的元组

【例如】定义一个 BEFORE 行级触发器，为老师表 Teacher 定义完整性规定“传授的工资不得低于 4000 元，如果低于 4000 元，主动改为 4000 元

CREATE TRIGGER Insert_Or_Update_Sal
BEFORE INSERT OR UPDATE ON Teacher
REFERENCING NEWROW AS NewTuple
FOR EACH ROW
BEGIN // 这是一个 PL/SQL 过程快
    IF(NewTouple.Job='传授') AND (NewTouple.Sal < 4000)
        THEN NewTouple.Sal:=4000;
    END IF
END;

（2）激活触发器

触发器的执行是由触发器事件激活的，如果同一个表上有多个触发器，激活时会依照以下程序执行

**1. 执行该表上的 BEFORE 触发器

  1. 激活触发器的 SQL 语句
  2. 执行该表上的 AFTER 触发器 **

对于同一个表上的多个 BEFORE(AFTER) 触发器，遵循“谁先创立谁先执行”的准则，即依照触发器创立的工夫先后顺序执行

（3）删除触发器

语法：

退出移动版