数据库系统概论之数据库完整性

中国地质大学信息工程学院中国地质大学信息工程学院数据库系统概论数据库系统概论An Introduction to Database System第五章第五章 数据库完整性数据库完整性7/14/20221什么是数据库的完整性什么是数据库的完整性 数据的正确性和相容性数据的正确性和相容性 防止不合语义的数据进入数据库。防止不合语义的数据进入数据库。关注关注 是否真实地反映现实世界是否真实地反映现实世界例例:学生的年龄必须是整数，取值范围为学生的年龄必须是整数，取值范围为14-2914-29；学生的性别只能是男或女；学生的性别只能是男或女；学生的学号一定是唯一的；学生的学号一定是唯一的；学生所在的系必须是学校开设的系；学生所在的系必须是学校开设的系；提纲提纲5.1 5.1 实体完整性实体完整性5.2 5.2 参照完整性参照完整性5.3 5.3 用户定义完整性用户定义完整性5.4 5.4 完整性约束命名子句完整性约束命名子句5.5 5.5 触发器触发器7/14/20222数据库的完整性与安全性数据库的完整性与安全性区别和联系区别和联系n 两个不同的概念两个不同的概念,但有一定的联系但有一定的联系;前者是为了防止数据库中存在不符合语义的数据前者是为了防止数据库中存在不符合语义的数据,防止错误信息防止错误信息的输入和输出的输入和输出,既所谓的垃圾进垃圾出既所谓的垃圾进垃圾出(Garbage in Garbage(Garbage in Garbage out)out)所造成的无效操作和错误结果所造成的无效操作和错误结果;后者是保护数据库防止恶意的破坏和非法存取后者是保护数据库防止恶意的破坏和非法存取;安全性措施防范的对象是非法用户安全性措施防范的对象是非法用户,完整性措施的防范对象是不完整性措施的防范对象是不合语义的数据合语义的数据.n 完整性控制机制完整性控制机制1.1.完整性约束条件定义机制完整性约束条件定义机制2.2.完整性检查机制完整性检查机制3.3.违约处理违约处理7/14/20223完整性约束条件定义完整性约束条件定义 完整性约束条件：数据模型的组成部分约束数据库中完整性约束条件：数据模型的组成部分约束数据库中数据的语义数据的语义 DBMSDBMS应提供定义数据库完整性约束条件，并把它们应提供定义数据库完整性约束条件，并把它们作为模式的一部分存入数据库中作为模式的一部分存入数据库中7/14/20224完整性检查机制完整性检查机制 检查用户发出的操作请求是否违背了完整性约束条件检查用户发出的操作请求是否违背了完整性约束条件 7/14/20225违约处理违约处理 如果发现用户的操作请求使数据违背了完整性约束条如果发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性。件，则采取一定的动作来保证数据的完整性。7/14/20226完整性约束条件完整性约束条件完整性约束条件作用的对象完整性约束条件作用的对象 列：对属性的取值类型、范围、精度等的约束条件列：对属性的取值类型、范围、精度等的约束条件 元组：对元组中各个属性列间的联系的约束元组：对元组中各个属性列间的联系的约束 关系：对若干元组间、关系集合上以及关系之间的关系：对若干元组间、关系集合上以及关系之间的联系的约束联系的约束7/14/20227完整性约束条件（续）完整性约束条件（续）静态静态 对静态对象的约束是反映数据库状态对静态对象的约束是反映数据库状态合理性的约束合理性的约束 动态动态 对动态对象的约束是反映数据库状态对动态对象的约束是反映数据库状态变迁的约束变迁的约束7/14/20228完整性约束条件分类完整性约束条件分类六类完整性约束条件六类完整性约束条件 静态静态列级约束约束 静态静态元组约束约束 静态静态关系约束约束 动态动态列级约束约束 动态动态元组约束约束 动态动态关系约束约束7/14/20229完整性约束条件（续）完整性约束条件（续）1.1.静态列级约束静态列级约束 静态列级约束：对的取值域的说明静态列级约束：对的取值域的说明 最常见、最简单、最容易实现的一类完整性约束最常见、最简单、最容易实现的一类完整性约束7/14/202210完整性约束条件（续）完整性约束条件（续）五类静态列级约束五类静态列级约束1)1)数据类型约束：数据的类型、长度、单位、精度数据类型约束：数据的类型、长度、单位、精度等等 例：学生姓名的数据类型为字符型，长度为例：学生姓名的数据类型为字符型，长度为8 82)2)对数据格式的约束对数据格式的约束例：例：学号：前两位表示入学年份，后四位为顺序编号学号：前两位表示入学年份，后四位为顺序编号 日期：日期：YY.MM.DDYY.MM.DD。7/14/202211补充：补充：完整性约束条件（续）完整性约束条件（续）3)3)取值范围或取值集合的约束取值范围或取值集合的约束例：规定成绩的取值范围为例：规定成绩的取值范围为0-1000-100 年龄的取值范围为年龄的取值范围为14-2914-29 性别的取值集合为性别的取值集合为 男男,女女 4)4)对空值的约束对空值的约束 空值：未定义或未知的值空值：未定义或未知的值 空值：与零值和空格不同空值：与零值和空格不同 有的列允许空值，有的则不允许，如成绩可为空值有的列允许空值，有的则不允许，如成绩可为空值5)5)其他约束其他约束例：关于列的排序说明，组合列等例：关于列的排序说明，组合列等7/14/202212补充：补充：完整性约束条件（续）完整性约束条件（续）2.2.静态元组约束静态元组约束 规定元组的各个列之间的约束关系规定元组的各个列之间的约束关系例：订货关系中发货量例：订货关系中发货量=700=700元元 静态元组约束只局限在元组上静态元组约束只局限在元组上 7/14/202213补充：补充：完整性约束条件（续）完整性约束条件（续）3.3.静态关系约束静态关系约束 关系的各个元组之间或若干关系之间存在的各种联系或约束关系的各个元组之间或若干关系之间存在的各种联系或约束 常见静态关系约束：常见静态关系约束：1)1)实体完整性约束实体完整性约束2)2)参照完整性约束参照完整性约束3)3)函数依赖约束函数依赖约束4)4)统计约束统计约束7/14/202214统计约束统计约束定义某个字段值一个关系多个元组的统计值之间的约束关系定义某个字段值一个关系多个元组的统计值之间的约束关系例：职工平均工资的例：职工平均工资的2 2倍倍=部门经理的工资部门经理的工资=原来工资原来工资 年龄只能增长年龄只能增长7/14/202217完整性约束条件（续）完整性约束条件（续）5.5.动态元组约束动态元组约束 修改元组值修改元组值:各个字段之间要满足的约束条件各个字段之间要满足的约束条件例例:职工工资调整不得低于其原来工资职工工资调整不得低于其原来工资+工龄工龄*1.51.57/14/202218完整性约束条件（续）完整性约束条件（续）6.6.动态关系约束动态关系约束 关系变化前后状态：限制条件关系变化前后状态：限制条件例：事务一致性、原子性等约束条件例：事务一致性、原子性等约束条件7/14/2022195.1 5.1 实体完整性实体完整性5.1.1 5.1.1 实体完整性定义实体完整性定义5.1.2 5.1.2 实体完整性检查和违约处理实体完整性检查和违约处理7/14/2022205.1.1 5.1.1 实体完整性定义实体完整性定义 实体完整性（实体完整性（Entity IntegrityEntity Integrity）若属性若属性A A是基本关系是基本关系R R的主属性，则属性的主属性，则属性A A不能取空值不能取空值例例SAP(SUPERVISORSAP(SUPERVISOR，SPECIALITYSPECIALITY，POSTGRADUATE)POSTGRADUATE)POSTGRADUATEPOSTGRADUATE属性为主码属性为主码（假设研究生不会重名），则其不能取空值（假设研究生不会重名），则其不能取空值7/14/2022215.1.1 5.1.1 实体完整性定义实体完整性定义关系模型必须遵守实体完整性规则的关系模型必须遵守实体完整性规则的原因原因(1)(1)实体完整性规则是针对基本关系而言的。实体完整性规则是针对基本关系而言的。(2)(2)一个基本表通常对应现实世界的一个实体集或多对多联系。一个基本表通常对应现实世界的一个实体集或多对多联系。(2)(2)现实世界中的实体和实体间的联系都是可区分的，即它现实世界中的实体和实体间的联系都是可区分的，即它们具有某种唯一性标识。们具有某种唯一性标识。(3)(3)关系模型中以主码作为唯一性标识。关系模型中以主码作为唯一性标识。(4)(4)主码中的属性即主属性不能取空值。主码中的属性即主属性不能取空值。空值就是空值就是“不知道不知道”或或“无意义无意义”的值。的值。主属性取空值，就说明存在某个不可标识的实体，即存在主属性取空值，就说明存在某个不可标识的实体，即存在不可区分的实体，这与第（不可区分的实体，这与第（2 2）点相矛盾，因此这个规则）点相矛盾，因此这个规则称为实体完整性。称为实体完整性。7/14/2022225.1.1 5.1.1 实体完整性定义实体完整性定义 关系模型的实体完整性在关系模型的实体完整性在Create table Create table 中用中用 Primary KeyPrimary Key定义。定义。对单属性的构成的码有两种说明方法：对单属性的构成的码有两种说明方法：定义为列级约束条件定义为列级约束条件 表级约束条件表级约束条件 对多个属性构成的码只有一种说明方法，即定义为表级对多个属性构成的码只有一种说明方法，即定义为表级约束条件约束条件7/14/202223 例题例题11将将StudentStudent表中的表中的SnoSno属性定义为码。属性定义为码。Create table studentCreate table student (Sno char(9)primary key (Sno char(9)primary key,/,/*在列级定义主码在列级定义主码*/Sname char(20)not null,Sname char(20)not null,Ssex char(2),Ssex char(2),Sage smallint,Sage smallint,Sdept char(20)Sdept char(20););5.1.1 5.1.1 实体完整性定义实体完整性定义7/14/2022245.1.1 5.1.1 实体完整性定义实体完整性定义或者或者Create table studentCreate table student (sno char(9),(sno char(9),Sname char(20)not null,Sname char(20)not null,Ssex char(2),Ssex char(2),Sage smallint,Sage smallint,Sdept char(20),Sdept char(20),primary key(sno),/primary key(sno),/*在表级定义主码在表级定义主码*/););7/14/2022255.1.1 5.1.1 实体完整性定义实体完整性定义 例题例题2 2 将将SCSC表中的表中的Sno,CnoSno,Cno属性组定义为码属性组定义为码 Create table SC Create table SC (Sno char(9)not null,(Sno char(9)not null,Cno char(4)not null,Cno char(4)not null,grade smallint,grade smallint,Primary key(Sno,Cno)Primary key(Sno,Cno),/,/*只能在表级定义主码只能在表级定义主码*/)；7/14/2022265.1.2 5.1.2 实体完整性检查和违约处理实体完整性检查和违约处理 用用primary key primary key 短语定义了关系的主码后，短语定义了关系的主码后，每当应用程序对基本表插入一条记录或者对主码列进行每当应用程序对基本表插入一条记录或者对主码列进行更新操作时，更新操作时，RDBMSRDBMS将自动检查：将自动检查：检查主码值是否唯一，如果不唯一则拒绝插入或修检查主码值是否唯一，如果不唯一则拒绝插入或修改。改。检查主码地各个属性是否为空，只要一个为空就拒检查主码地各个属性是否为空，只要一个为空就拒绝插入或修改绝插入或修改7/14/2022275.1.2 5.1.2 实体完整性检查和违约处理实体完整性检查和违约处理检查主码值是否唯一的方法（检查主码值是否唯一的方法（2 2种）：种）：全表扫描全表扫描keyikeyif2if2if3if3if4if4if5if5ikey1key1f21f21f31f31f41f41f51f51key2key2f22f22f32f32f42f42f52f52key3key3f23f23f33f33f43f43f53f53.7/14/2022285.1.2 5.1.2 实体完整性检查和违约处理实体完整性检查和违约处理 全表扫描缺点：全表扫描缺点：耗时耗时7/14/2022295.1.2 5.1.2 实体完整性检查和违约处理实体完整性检查和违约处理 第二种方法：建索引（如第二种方法：建索引（如B+B+树索引）树索引）255112 3066 7815 20 253 730 4168 69 71 7679 84 9351 54 65新插入记录的新插入记录的主码值主码值7/14/2022305.1.2 5.1.2 实体完整性检查和违约处理实体完整性检查和违约处理 RDBMSRDBMS核心一般都在主码上自动建立一个索引核心一般都在主码上自动建立一个索引 通过索引查找基本表中是否存在新的主码值通过索引查找基本表中是否存在新的主码值 提高效率提高效率7/14/2022315.2 5.2 参照完整性参照完整性5.2.1 参照完整性定义5.2.2 参照完整性检查和违约处7/14/2022325.2.1 5.2.1 参照完整性定义参照完整性定义 关系模型的参照完整性在关系模型的参照完整性在Create tableCreate table中用中用foreign key foreign key 短语定义哪些列为外码。短语定义哪些列为外码。7/14/2022335.2.1 5.2.1 参照完整性定义参照完整性定义 例题例题3 3 定义定义SCSC中的参考完整性。中的参考完整性。Create table scCreate table sc (sno char(9)not null,(sno char(9)not null,cno char(4)not null,cno char(4)not null,grade smallint,grade smallint,Primary key(sno,cno),/Primary key(sno,cno),/*在表级定义实体完整性在表级定义实体完整性*/foreign key(sno)references student(sno foreign key(sno)references student(sno),/),/*在表级定义在表级定义 参照完整性参照完整性*/foreign key(Cno)references student(Cno foreign key(Cno)references student(Cno),/),/*在表级定义在表级定义 参照完整性参照完整性*/)；7/14/2022345.2.2 参照完整性检查和违约处理参照完整性将两个表中的相应元组联系起来后。参照完整性将两个表中的相应元组联系起来后。当进行增删操作时有可能破坏参照完整性，必须进行检查。当进行增删操作时有可能破坏参照完整性，必须进行检查。7/14/2022355.2.2 参照完整性检查和违约处理对对SCSC和和StudentsStudents有有4 4种可能破坏参照完整性的情况：种可能破坏参照完整性的情况：SCSC表中增加一个表中增加一个元组，该元组的元组，该元组的Sno Sno 属性值在属性值在StudentsStudents中找不到一个元组，其中找不到一个元组，其SnoSno属性的值与之相属性的值与之相等。等。修改修改SCSC表表中的一个元组，修改后该元组的中的一个元组，修改后该元组的SnoSno属性在属性在StudentsStudents中找不到一个元组，其中找不到一个元组，其SnoSno属性值与之相等。属性值与之相等。从从StudentsStudents表中删除表中删除一个元组，造成一个元组，造成SCSC表中某些元组表中某些元组的的SnoSno属性值在属性值在StudentsStudents中找不到一个元组，其中找不到一个元组，其SnoSno属性值与之相等。属性值与之相等。修改修改StudentsStudents表表中的一个元组的中的一个元组的SnoSno属性，造成属性，造成SCSC表表中某些元组的中某些元组的SnoSno属性值在属性值在StudentsStudents中找不到一个元中找不到一个元组，其组，其SnoSno属性值与之相等属性值与之相等7/14/202236表5.1 可能破坏参照完整性的情况及违约处理被参照表（被参照表（StudentsStudents）参照表（例如参照表（例如SCSC）违约处理违约处理可能破坏参照完整性可能破坏参照完整性插入元组插入元组拒绝拒绝可能破坏参照完整性可能破坏参照完整性修改外码值修改外码值拒绝拒绝删除元组删除元组可能破坏参照完整性可能破坏参照完整性拒绝拒绝/级联删除级联删除/设置为空设置为空修改主码值修改主码值可能破坏参照完整性可能破坏参照完整性拒绝拒绝/级联修改除级联修改除/设置为空设置为空7/14/202237处理策略处理策略1 1、拒绝（、拒绝（No actionNo action）执行）执行不允许该操作执行。该策略一般为默认策略。不允许该操作执行。该策略一般为默认策略。2 2、级联（、级联（CascadeCascade）操作）操作当删除或修改被参照表当删除或修改被参照表(Students)(Students)的一个元组造成了与参照表的一个元组造成了与参照表（SCSC）的不一致，则删除或修改参照表中的所有造成不一致的元）的不一致，则删除或修改参照表中的所有造成不一致的元组。组。例如，删除例如，删除StudentsStudents表中的元组，表中的元组，SnoSno值为值为200215121200215121则要从则要从SCSC表中级联删除表中级联删除SC.Sno=200215121SC.Sno=200215121的所有元组。的所有元组。3 3、设置空值（、设置空值（Set-NullSet-Null）例如：学生（例如：学生（学号学号，姓名，性别，姓名，性别，专业号，年龄），年龄）专业（专业（专业号专业号，专业名），专业名）专业号可以取空值专业号可以取空值 但在学生但在学生-选课数据库中，选课数据库中，StudentsStudents关系为被参照关系，其主码为关系为被参照关系，其主码为SnoSno，SCSC为参考关系，为参考关系，SnoSno为外码。若为外码。若SCSC的的SnoSno为空值，则表明。为空值，则表明。7/14/202238处理策略处理策略若违反了参照完整性，系统选用默认策略，即拒若违反了参照完整性，系统选用默认策略，即拒绝执行。绝执行。如果想让系统采用其他策略则必须在创建表的时如果想让系统采用其他策略则必须在创建表的时候显式地加以说明。候显式地加以说明。7/14/202239 例题例题4 4 显式说明参照完整性的违约处理显式说明参照完整性的违约处理Create table SCCreate table SC (sno char(9)not null,(sno char(9)not null,cno char(4)not null,cno char(4)not null,grade smallint,grade smallint,Primary key(sno,cno),/Primary key(sno,cno),/*在表级定义实体完整性在表级定义实体完整性*/foreign key(sno)references student(sno),/foreign key(sno)references student(sno),/*在表级定在表级定义参照完整性义参照完整性*/on delete cascade/*当删除student表中的元组时，级联删除SC表中的相应元组*/on update cascade,/*当更新student表中的Sno时，级联更新SC表中的相应元组*/foreign key(sno)references courses(sno),/foreign key(sno)references courses(sno),/*在表级在表级定义参照完整性定义参照完整性*/on delete no Action/*当删除courses表中的元组时造成了与SC表不一致拒绝删除*/on update cascade )；7/14/2022405.3 用户定义完整性5.3.1 属性上的约束条件的定义5.3.2 属性上的约束条件检查和违约处理5.3.3 元组上的约束条件的定义5.3.4 元组上的约束条件检查和违约处理7/14/2022415.3.1 属性上的约束条件的定义 属性值的限制，包括：属性值的限制，包括：列值非空（列值非空（Not null Not null 短语）短语）列值唯一（列值唯一（uniqueunique短语）短语）检查列值是否满足一个布尔表达式（检查列值是否满足一个布尔表达式（Check Check 短语）短语）7/14/2022425.3.1 属性上的约束条件的定义1 1、不允许取空值、不允许取空值 例题例题5 5 在定义在定义SCSC表时，说明表时，说明Sno,Cno,GradeSno,Cno,Grade属性不允属性不允许取空值。许取空值。Create table SCCreate table SC (Sno char(9)not null,(Sno char(9)not null,Cno char(40)not null,Cno char(40)not null,Grade smallint not null,Grade smallint not null,Primary key(Sno,Cno),Primary key(Sno,Cno),)7/14/2022435.3.1 属性上的约束条件的定义2 2、列值唯一、列值唯一 例题例题6 6 建立部门表建立部门表Dept,Dept,要求部门名称要求部门名称Dname Dname 列取值唯列取值唯一，部门编号一，部门编号DeptnoDeptno列为主码。列为主码。Create table deptCreate table dept (Deptno numeric(2)(Deptno numeric(2)，Dname char(9)nuique,Dname char(9)nuique,Location char(10),Location char(10),Primary key(Deptno)Primary key(Deptno););7/14/2022445.3.1 属性上的约束条件的定义3 3、用、用Check Check 短语指定列值应该满足的条件短语指定列值应该满足的条件 例题例题7 Student 7 Student 表的表的SsexSsex只允许取只允许取“男男”或或“女女”。Create table StudentsCreate table Students(Sno char(9)primary key,(Sno char(9)primary key,Snome char(8)not null,Snome char(8)not null,Ssex char(2)check(Ssex IN(Ssex char(2)check(Ssex IN(男男，女女)，Sage smallint,Sage smallint,Sdept Char(20)Sdept Char(20)7/14/2022455.3.1 属性上的约束条件的定义 例题例题8 SC8 SC表的表的Grade Grade 的值应该在的值应该在0 0和和100100之间。之间。Create table SCCreate table SC (Sno char(9)not null,(Sno char(9)not null,Cno char(4)not null,Cno char(4)not null,Grade smallint Check(Grade=0 and Grade Grade smallint Check(Grade=0 and Grade=100),=100),primary key(Sno,Cno),primary key(Sno,Cno),Foreign key(Sno)references Student(Sno),Foreign key(Sno)references Student(Sno),Foreign key(Cno)references Course(Cno)Foreign key(Cno)references Course(Cno););7/14/2022465.3 用户定义完整性5.3.1 属性上的约束条件的定义5.3.2 属性上的约束条件检查和违约处理5.3.3 元组上的约束条件的定义5.3.4 元组上的约束条件检查和违约处理7/14/2022475.3.2 属性上的约束条件检查和违约处理 当往表中当往表中插入元组或修改属性插入元组或修改属性的值时，的值时，RDBMSRDBMS就检查就检查属属性上性上的约束条件是否被满足，否则拒绝执行。的约束条件是否被满足，否则拒绝执行。7/14/2022485.3 用户定义完整性5.3.1 属性上的约束条件的定义5.3.2 属性上的约束条件检查和违约处5.3.3 元组上的约束条件的定义5.3.4 元组上的约束条件检查和违约处7/14/2022495.3.3 元组上的约束条件的定义与属性上约束条件的定义类似，在与属性上约束条件的定义类似，在Create Create 语句中可以用语句中可以用CheckCheck短语定义元短语定义元组上的约束条件，即元组级的限制。组上的约束条件，即元组级的限制。例题例题9 9 当学生的性别是男时，其名字不能以当学生的性别是男时，其名字不能以MsMs.打头。打头。Create table Student Create table Student (Sno char(9),(Sno char(9),Sname char(8)not null,Sname char(8)not null,Ssex char(2),Ssex char(2),Sage smallint,Sage smallint,Sdept char(20),Sdept char(20),Primary key(Sno),Primary key(Sno),check(Sex=check(Sex=女女 or Sname not like Ms.%)or Sname not like Ms.%););7/14/2022505.3 用户定义完整性5.3.1 属性上的约束条件的定义5.3.2 属性上的约束条件检查和违约处5.3.3 元组上的约束条件的定义5.3.4 元组上的约束条件检查和违约处7/14/2022515.3.4 元组上的约束条件检查和违约处理 当往表中插入元组或修改属性的值时，当往表中插入元组或修改属性的值时，RDBMSRDBMS就检就检查查元组上的约束条件元组上的约束条件是否被满足，否则拒绝执行。是否被满足，否则拒绝执行。7/14/2022525.4 完整性约束命名子句1 1、完整性约束命令子句、完整性约束命令子句语法：语法：ConstraintConstraintprimary keyprimary key短语短语|foreign key|foreign key 短语短语|check|check 短语短语 7/14/2022535.4 完整性约束命名子句 例题例题10 10 建立学生登记表建立学生登记表StudentsStudents，要求学号在，要求学号在90 00099 99990 00099 999之间，姓名不之间，姓名不能取空值，年龄小于能取空值，年龄小于3030，性别只能是，性别只能是“男男”或或“女女”。Create table StudentsCreate table Students (Sno numeric(6)(Sno numeric(6)constraint C1 check(Sno between 90000 and 99999),constraint C1 check(Sno between 90000 and 99999),Sname char(20)Sname char(20)constraint C2 not null,constraint C2 not null,Sage numeric(3)Sage numeric(3)constraint C3 check(Sage30),constraint C3 check(Sage=3000)Contrant C1 check(Sal+Deduct=3000););7/14/2022555.4 完整性约束命名子句2.2.修改表中的完整性限制修改表中的完整性限制使用使用Alter tableAlter table语句语句 例题例题12 12 去掉去掉 例题例题10 Students10 Students表中对性别的限制。表中对性别的限制。Alter table studentAlter table student Drop constraint C4 Drop constraint C47/14/2022565.4 完整性约束命名子句例题13 修改表 Students表中的约束条件，要求学号改为800 000 999 999之间，年龄小于30改为45。先删除原来的约束条件，再增加新的约束条件。Alter table students Drop constraint C1;Alter table students Add constraint C1 check(Sno between 800000 and 999999),Alter table students Drop constraint C3;Alter table students Add constraint C3 check(Sage 45);7/14/2022575.5 触发器5.5.1 定义触发器5.5.2 5.5.2 激活触发器激活触发器5.5.3 5.5.3 删除触发器删除触发器7/14/2022585.5.1 定义触发器1 1、触发器（、触发器（TriggerTrigger）:定义在关系表上的一类由定义在关系表上的一类由事件驱动的特殊过程。事件驱动的特殊过程。2 2、格式、格式Create trigger Create trigger Before|After Before|After on on For each Row|Statement For each Row|Statement When When 7/14/202259 5.5.1 定义触发器举例 例题例题18 18 定义一个定义一个Before Before 行级触发器，为教师表行级触发器，为教师表TeacherTeacher定义完定义完整性规则整性规则“教授的工资不得低于教授的工资不得低于40004000元，如果低于元，如果低于40004000元，自动元，自动改为改为40004000元元”。Create trigger Create trigger Inser_or_SalInser_or_Sal Before insert or update on teacher Before insert or update on teacherFor each rowFor each rowAs begin As begin If(new.pJob=If(new.pJob=教授教授)and(new.Sal4000)Then )and(new.Sal4000)Then new.sal:=4000;new.sal:=4000;End If;End If;EndEnd；7/14/202260 5.5.1 定义触发器 例题例题19 19 定义一个定义一个After After 行级触发器，当教师表行级触发器，当教师表TeacherTeacher的工资发生变化后就自动在工资变化表的工资发生变化后就自动在工资变化表Sal_logSal_log中增加一条相应纪录。中增加一条相应纪录。Create table Sal_logCreate table Sal_log(Eno numric(4)reference teacher(eno),(Eno numric(4)reference teacher(eno),Sal numric(7,2),Sal numric(7,2),Username char(10),Username char(10),Date timestamp Date timestamp)；7/14/202261 5.5.1 定义触发器Create trigger Create trigger insert_Salinsert_Sal After insert on teacher insert on teacherFor each rowFor each rowAs begin As begin insert into Sal_log values(insert into Sal_log values(new.Eno,new.Sal,Current_user,Current_Timestamp);new.Eno,new.Sal,Current_user,Current_Timestamp);End End；Create trigger Create trigger Update_SalUpdate_Sal After update on teacher update on teacherFor each rowFor each rowAs begin As begin If(new.Salold.Sal)Then insert into Sal_log values(If(new.Salold.Sal)Then insert into Sal_log values(new.Eno,new.Sal,Current_user,Current_Timestamp);new.Eno,new.Sal,Current_user,Current_Timestamp);End If;End If;EndEnd；7/14/2022625.5.2 激活触发器 同一个表上的多个触发器的执行顺序：同一个表上的多个触发器的执行顺序：执行执行BeforeBefore触发器触发器 激活触发器的激活触发器的SQLSQL语句语句 执行执行AfterAfter触发器触发器 7/14/2022635.5.2 激活触发器 同一个表上的多个同一个表上的多个Before(After)Before(After)的执行顺序：的执行顺序：谁先创建谁先执行谁先创建谁先执行 即按创建触发器的时间先后顺序执行即按创建触发器的时间先后顺序执行7/14/2022645.5.2 激活触发器例题20 执行修改某个教师工资SQL语句，激活上述定义的触发器。Update teacher set sal=800 where Ename=Update teacher set sal=800 where Ename=陈平陈平顺序：顺序：执行触发器执行触发器Insert_Or_Update_Sal;Insert_Or_Update_Sal;执行执行Update teacher set sal=800 where Ename=Update teacher set sal=800 where Ename=陈平陈平 执行触发器执行触发器Insert_Sal;Insert_Sal;执行触发器执行触发器Update_SalUpdate_Sal。7/14/2022655.5.3 删除触发器语法：语法：Drop trigger Drop trigger ON ON ；例题例题21 21 删除教师表删除教师表Teacher Teacher 上的触发器上的触发器Insert_Sal.Insert_Sal.Drop trigger Insert_Sal on Teacher;Drop trigger Insert_Sal on Teacher;7/14/202266第五章作业P 313/164 2,5,67/14/202267演讲完毕，谢谢观看！