数据库原理-第五章.ppt

数据库原理数据库原理主讲人：王子健主讲人：王子健12第五章第五章 数据库完整性数据库完整性v 实体完整性实体完整性v 参照完整性参照完整性v 用户自定义的完整性用户自定义的完整性v完整性约束命名子句完整性约束命名子句v触发器触发器35.6 触发器触发器n定义触发器定义触发器n激活触发器激活触发器n删除触发器删除触发器nSqlserver2005中的触发器中的触发器4n定义触发器定义触发器触发器是什么？触发器是什么？触发器是用户定义在关系表中的一类由触发器是用户定义在关系表中的一类由事件驱动事件驱动的的特殊过程特殊过程。表中数据的增、删、改操作是激活触发器的事件。表中数据的增、删、改操作是激活触发器的事件。触发器可以实施比触发器可以实施比foreign key约束、约束、check约束更为复杂的检查约束更为复杂的检查和操作，具有更精细和更强大的数据控制能力。和操作，具有更精细和更强大的数据控制能力。触发器的定义触发器的定义CREATE TRIGGER语法格式语法格式 CREATE TRIGGER ON FOR EACH WHEN 5n定义触发器定义触发器触发器的定义触发器的定义语法详解语法详解创建者：表的创建者：表的拥有者拥有者触发器名：为触发器命名触发器名：为触发器命名表名：触发器的目标表的名字表名：触发器的目标表的名字触发事件：触发事件：INSERT、DELETE、UPDATE或它们的组合或它们的组合触发器的类型：行级触发器和语句级触发器触发器的类型：行级触发器和语句级触发器触发条件：触发器被激活时，根据该条件判断是否执行触发条件：触发器被激活时，根据该条件判断是否执行触发动作条：是一个匿名过程块，或是对以创建存储过程的调用。如触发动作条：是一个匿名过程块，或是对以创建存储过程的调用。如果是行级触发器，用户可以在过程体中使用果是行级触发器，用户可以在过程体中使用NEW和和OLD引用引用UPDATE/INSERT事件之后的新值和事件之后的新值和UPDATE/DELETE事件之前的事件之前的旧值。如果是语句级触发器则不能在动作体中使用旧值。如果是语句级触发器则不能在动作体中使用NEW或或OLD进行进行引用。引用。6n定义触发器定义触发器触发器的定义触发器的定义例例18 定义一个定义一个BEFORE行级触发器，为教师表行级触发器，为教师表Teacher定义定义完整性规则完整性规则“教授的工资不得低于教授的工资不得低于4000元，如果低于元，如果低于4000元，自元，自动改为动改为4000元元”。CREATE TRIGGER Insert_Or_Update_Sal BEFORE INSERT OR UPDATE ON Teacher FOR EACH ROW AS BEGIN IF(new.Job=教授教授)AND(new.Sal 4000)THEN new.Sal:=4000;END IF;END;7例例19定义定义AFTER行级触发器，当教师表行级触发器，当教师表Teacher的工资发生变化后就的工资发生变化后就自动在工资变化表自动在工资变化表Sal_log中增加一条相应记录中增加一条相应记录 首先建立工资变化表首先建立工资变化表Sal_log CREATE TABLE Sal_log (Eno NUMERIC(4)references teacher(eno)Sal NUMERIC(7，2)，Username char(10)，Date TIMESTAMP )；CREATE TRIGGER Insert_Sal AFTER INSERT ON Teacher FOR EACH ROW AS BEGIN INSERT INTO Sal_log VALUES(new.Eno，new.Sal，CURRENT_USER，CURRENT_TIMESTAMP);END;8例例19续续 CREATE TRIGGER Update_Sal AFTER UPDATE ON Teacher FOR EACH ROW AS BEGIN IF(new.Sal old.Sal)THEN INSERT INTO Sal_log VALUES(new.Eno，new.Sal，CURRENT_USER，CURRENT_TIMESTAMP);END IF;END;95.6 触发器触发器n定义触发器定义触发器n激活触发器激活触发器n删除触发器删除触发器nSqlserver2005中的触发器中的触发器10n激活触发器激活触发器触发器的执行，是由触发事件激活的，并由数据触发器的执行，是由触发事件激活的，并由数据库服务器自动执行。库服务器自动执行。一个数据表上可能定义了多个触发器同一个表上一个数据表上可能定义了多个触发器同一个表上的多个触发器激活时遵循如下的执行顺序：的多个触发器激活时遵循如下的执行顺序：（1）执行该表上的执行该表上的BEFORE触发器；触发器；（2）激活触发器的激活触发器的SQL语句；语句；（3）执行该表上的执行该表上的AFTER触发器。触发器。同一个表上的多个同一个表上的多个BEFORE（AFTER）触发器的执行）触发器的执行顺序：顺序：遵循遵循“谁先创建，谁先执行谁先创建，谁先执行”的原则的原则按字母顺序按字母顺序11n激活触发器激活触发器例题例题例例20执行修改某个教师工资的执行修改某个教师工资的SQL语句，激活上语句，激活上述定义的触发器。述定义的触发器。UPDATE Teacher SET Sal=800 WHERE Ename=陈平陈平;执行顺序是：执行顺序是：执行触发器执行触发器Insert_Or_Update_Sal执行执行SQL语句语句“UPDATE Teacher SET Sal=800 WHERE Ename=陈平陈平;”执行触发器执行触发器Insert_Sal；执行触发器执行触发器Update_Sal 12n删除触发器删除触发器删除触发器的删除触发器的SQL语法：语法：DROP TRIGGER ON;触发器必须是一个已经创建的触发器，并且只能由具有触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除。相应权限的用户删除。例例21 删除教师表删除教师表Teacher上的触发器上的触发器Insert_Sal DROP TRIGGER Insert_Sal ON Teacher;135.6 触发器触发器n定义触发器定义触发器n激活触发器激活触发器n删除触发器删除触发器nSqlserver2005中的触发器中的触发器14nSqlserver2005中的触发器中的触发器触发器是什么？触发器是什么？触发器是用户定义在关系表中的一类由触发器是用户定义在关系表中的一类由事件驱动事件驱动的的特殊特殊过程过程。在执行某些特定的在执行某些特定的SQL语句（触发特定事件）时自动执语句（触发特定事件）时自动执行。行。表的创建，修改，删除是激发触发器的事件（表的创建，修改，删除是激发触发器的事件（DDL触发触发器）器）表中数据的增、删、改操作是激活触发器的事件表中数据的增、删、改操作是激活触发器的事件（DML触发器）。触发器）。触发器可以实施比触发器可以实施比foreign key约束、约束、check约束更为约束更为复杂的检查和操作，具有更精细和更强大的数据控制能复杂的检查和操作，具有更精细和更强大的数据控制能力。力。15nSqlserver2005中的触发器中的触发器触发器的定义？触发器的定义？在定义触发器时应指定触发器的：在定义触发器时应指定触发器的：名称名称目标表格目标表格触发事件触发事件类型（执行动作体的方式）类型（执行动作体的方式）动作体（过程块）动作体（过程块）触发器的定义语句触发器的定义语句Create Trigger On As BeginEND16nSqlserver2005中的触发器中的触发器触发器的功能？触发器的功能？完成更复杂的数据约束。完成更复杂的数据约束。检查所做的检查所做的SQL是否允许。是否允许。修改其它数据表里的数据修改其它数据表里的数据 返回自定义的错误信息。返回自定义的错误信息。触发器的分类触发器的分类Instead of 触发器触发器触发器触发后，用触发器中定义的过程块取代原操作（激活触触发器触发后，用触发器中定义的过程块取代原操作（激活触发器的事件）发器的事件）After 触发器触发器原操作（激活触发器的事件）完成后执行触发器过程块中的内原操作（激活触发器的事件）完成后执行触发器过程块中的内容。容。17nSqlserver2005中的触发器中的触发器请看下面例题请看下面例题例例1：为：为student表格定义一个触发器表格定义一个触发器Stu1，完成以下，完成以下功能：每当向功能：每当向student表格中插入一次数据后，在控制表格中插入一次数据后，在控制台提示台提示“student表格中有数据插入表格中有数据插入”。create trigger stu1on studentafter insert as beginprint student 表格中有数据插入表格中有数据插入；end18nSqlserver2005中的触发器中的触发器请看下面例题请看下面例题例例2：为：为student表格定义一个触发器表格定义一个触发器Stu2，完成以下，完成以下功能：不允许再向功能：不允许再向student表格中插入任何数据。表格中插入任何数据。create trigger stu2on studentinstead of insert as beginprint 不让插入数据啦不让插入数据啦;end19nSqlserver2005中的触发器中的触发器请看下面例题请看下面例题例例2：第二种做法。：第二种做法。create trigger stu2on studentafter insert as beginRollback Transaction;print 不让插入数据啦不让插入数据啦;end20nSqlserver2005中的触发器中的触发器删除前两个例题定义的触发器，请看下面例题删除前两个例题定义的触发器，请看下面例题例例3：为：为student表格定义一个触发器表格定义一个触发器stu3，使，使student表格中只能插入男同学的数据。表格中只能插入男同学的数据。该怎么做？该怎么做？请先理解触发器的工作原理。请先理解触发器的工作原理。21nSqlserver2005中的触发器中的触发器触发器的工作原理触发器的工作原理激活触发器时触发器会仿照目标表建立两个临时表，分别为激活触发器时触发器会仿照目标表建立两个临时表，分别为inserted表和表和deleted表。表。当触发器被激活时会根据不同情况向两个表中存放相应记录当触发器被激活时会根据不同情况向两个表中存放相应记录inserted表中存放新的记录。表中存放新的记录。Deleted表中存放旧的记录。表中存放旧的记录。两个表的具体存放信息如下：两个表的具体存放信息如下：激活触发器事件激活触发器事件insertedinserted表表deleteddeleted表表insertinsert存放要插入的记录存放要插入的记录updateupdate存放要更后的记录存放要更后的记录存放更新前的记录存放更新前的记录deletedelete存放被删除的记录存放被删除的记录22nSqlserver2005中的触发器中的触发器触发器的工作原理（续）触发器的工作原理（续）两个临时表在触发器被激活时创建，在触发器工作结束后消失。两个临时表在触发器被激活时创建，在触发器工作结束后消失。两个表都是只读的，只能查询其中的数据，但是不能更新。两个表都是只读的，只能查询其中的数据，但是不能更新。在触发器的动作体中可以查询两个临时表，触发器工作结束后在触发器的动作体中可以查询两个临时表，触发器工作结束后则不能再查询则不能再查询23nSqlserver2005中的触发器中的触发器根据以上分析，我们再来看下例根据以上分析，我们再来看下例3例例3：为：为student表格定义一个触发器表格定义一个触发器stu3，使，使student表格中只能表格中只能插入男同学的数据。插入男同学的数据。create trigger stu3on studentinstead of insert as beginif(select Ssex from inserted)男男)beginprint 只能插入男同学记录只能插入男同学记录;endelsebegininsert into studentselect*from inserted;endend24删除之前建立的触发器，请看下面例题删除之前建立的触发器，请看下面例题例例4：为：为student表格定义一个触发器表格定义一个触发器stu4，当发生主键冲突时提示错误并拒绝，当发生主键冲突时提示错误并拒绝操作。操作。create trigger stu4on studentinstead of insert,updateas beginif(select count(*)from(select Sno from insertedwhere sno in(select sno from student)X)0 )beginprint 存在主键冲突存在主键冲突;endelsebegininsert into studentselect*from inserted;endend25请自行思考下面两个例题该如何完成请自行思考下面两个例题该如何完成例例5：为：为student表格定义一个触发器表格定义一个触发器stu5，完成下面功能：，完成下面功能：student表中年龄的取值范围为大于表中年龄的取值范围为大于16小于小于26，当对，当对student表进行插入或更新操作时，如果年龄大于等于表进行插入或更新操作时，如果年龄大于等于26，则自动变为则自动变为25；当年龄小于等于；当年龄小于等于16时自动变为时自动变为17。例例6：仿照：仿照student表新建一个日志表表新建一个日志表student_log，每当对，每当对student表中数据进行更新或删除操作时，将被删除和更新表中数据进行更新或删除操作时，将被删除和更新前的记录保存到前的记录保存到student_log中。中。