项目八 公司管理数据库系统中触发器的使用,终极目标：1.会利用触发器保证公司管理数据库系统数据的完整性促成目标： 1能正确理解触发器的概念、功能和类型2会使用企业管理器和Transact-SQL语句创建管理触发器,一、触发器的概念,触发器是一种特殊类型的存储过程，用于实现数据完整性的方法。 共性：都是为了实现某个特定任务。特殊性：存储过程通过用存储过程名称被调用来执行，而触发器不能被显式的调用，是通过事件触发而被执行。数据完整性：是指存放在数据库中数据的一致性和准确性。,二、触发器的主要功能,实现数据库中相关表的级联操作强制实现比一般约束更为复杂的约束条件可以评估数据修改前后表的状态，并根据其差异采取对策。如取消插入或修改数据。,三、触发器的类型,DELETE触发器INSERT触发器UPDATE触发器,四、创建触发器的语法,语法： CREATE TRIGGER trigger_name ON 表名或视图名 FOR INSERT， | UPDATE， |DELETE AS sql_statement .n ,指定新建触发器名称,指在其上执行操作的表或视图,指定以下操作都成功后激活该触发器,指定在表或视图上执行何种操作时激活该触发器,指触发器将要执行的动作,指触发器激活后执行的一条或若干条SQL语句,指定要操作的表或视图,五、创建触发器,1使用企业管理器创建触发器 2. 利用Transact-SQL语句创建触发器,【例1】对p_order表创建名为“例1”的触发器，如果删除p_order表中的记录，那么就显示product表中的所有产品的信息。,1）用鼠标右击p_order表|【所有任务】|【管理触发器(T).】命令。2）在打开的 “触发器属性” 对话框中的“名称”文本框中选择“新建”，然后在文本框中输人以下创建触发器命令。 CREATE TRIGGER 例1 ON p_order FOR delete AS select * from product3）单击“检查语法”检查语句是否正确。4）单击“应用”按钮，在“名称”下拉列表中会有新创建的reminder触发器名字。5）单击 “确定”按钮,关闭窗口，创建成功。,1、使用企业管理器创建触发器,【例2】对p_order表创建名为“例2”的触发器，如果往p_order表中插入一条记录，那么显示P_order表插入数据后的情况。,1）用鼠标右击p_order表|【所有任务】|【管理触发器(T).】命令。2）在打开的 “触发器属性” 对话框中的“名称”文本框中选择“新建”，然后在文本框中输人以下创建触发器命令。CREATE TRIGGER 例2 ON p_order FOR insert ASSelect * from p_order3）单击“检查语法”检查语句是否正确。4）单击“应用”按钮，在“名称”下拉列表中会有新创建的reminder触发器名字。5）单击 “确定”按钮,关闭窗口，创建成功。,任务：,1、建立触发器名为项目8_1 ，如果删除employee表中的雇员的记录，那么显示在p_order表中订货数量大于50的订单的所有信息。2、建立触发器名为项目8_2 ，如果往customer表中插入一行记录,那么显示在employee表中所有女雇员的信息。3、建立触发器名为项目8_3 ，如果更新p_order表中记录字段值，那么显示在product表中库存量大于300的产品的产品名、单价、库存量信息 。4、建立触发器名为项目8_4 ，如果删除p_order表中记录，那么显示product表中产品id为“002 ”的产品信息。,补充知识：INSERTED表和DELETED表,1、两表特点执行触发器时，SQL Server系统会创建两个特殊的临时表：inserted表和deleted表。它们驻留在内存中，两个表的结构与触发器作用的表的结构相同。用户不能对它们修改，但可以从表中获取数据。触发器工作完成后，与此触发器相关的这两个表会被删除。,2、两表内容：,Inserted表：保存插入到表中的记录。当向表中插入数据时，INSERT触发器激活执行，新的记录插入到激活触发器的表中，同时也插入到Inserted表中。Deleted表：保存已从表中删除的记录。当触发一个DELETE触发器时，被删除的记录存放到deleted表中。 修改一条记录等于插入一条新记录，同时删除旧记录。所以对定义了UPDATE触发器的表进行修改时，表中的原记录移到deleted表中，修改过的记录插入到表中，同时也插入到inserted表中。,3、操作比较,2. 利用Transact-SQL语句创建触发器,1) 使用INSERT触发器 当触发INSERT触发器时，新的数据行就会被插入到触发器表和inserted表中。,五、创建触发器,【例3】 创建一个INSERT触发器，当在companyinfo数据库的employee表中插入一条新员工记录时，如果特长是“舞蹈”、“唱歌”的员工，则撤消该插入操作，并返回出错消息,CREATE TRIGGER 例3ON EmployeeFOR INSERTASDECLARE hoobby varchar(20)SELECT hoobby =inserted.特长FROM insertedIF hoobby =舞蹈 OR hoobby=唱歌 BEGIN print 不能插入非本公司设定部门的员工信息！ ROLLBACK TRANSACTION END,观察操作结果：,INSERT employee(雇员id,姓名,性别,雇佣日期,特长,薪水) VALUES(013 李亮, 男, 2008-7-20, 舞蹈, 4000)程序2：INSERT employee(雇员id,姓名,性别,雇佣日期,特长,薪水) VALUES( 014,洪皓, 男, 2001-7-21, 杂技, 4100),2) 使用DELETE触发器,当触发DELETE触发器后，从受影响的表中删除的行将被放置到deleted表中。,【例4】创建一个名为“例4”的触发器，其功能是：当对employee表进行删除操作时，那么p_order表中凡是该雇员处理的订单都要被删除。,create trigger 例4on employeefor deleteasDeclare id char(10)Select id=雇员idFrom deletedDelete p_orderWhere 雇员id=id,3）使用UPDATE触发器,修改触发器和插入触发器的工作过程基本上一致，修改一条记录相当于插入了一条新的记录，删除一条旧的记录。 可将UPDATE语句看成两步操作：即捕获数据前像的DELETE语句，和捕获数据后像的INSERT语句。当在定义有触发器的表上执行UPDATE语句时，原始行（前像）被移入到deleted表，更新行（后像）被移入到inserted表。,【例5】 在product表上创建了一个修改触发器，该触发器防止用户修改产品的库存量过大，如果库存量的变化超过100时给出错误提示，并回滚。,CREATE TRIGGER 例5ON PRODUCTFOR UPDATEASIF (SELECT ABS(INSERTED.库存量-DELETED.库存量) FROM INSERTED，DELETED where INSERTED.产品ID=DELETED.产品ID )100BEGIN PRINT 库存量修改过大！ ROLLBACK TRANSACTIONEND,任务2：,1、建立触发器项目8_5， 如果删除p_order表中的单个记录，那么把employee表中的相关雇员记录也删除。2、建立触发器项目8_6 ，如果往p_order表中插入一条新记录（如产品id为006），那么显示该产品的产品名。3、建立触发器项目8_7 ，如果更新employee表中的记录（如把雇员id为003的姓名改为王孔），则显示更新前后该记录的情况。4、建立触发器项目8_8，如果删除p_order表中的单个记录，那么把product表中的相关产品记录也删除。,六、修改触发器,1使用企业管理器修改触发器正文，其操作步骤如下：（1）打开企业管理器中，展开指定的服务器和数据库；（2）选择指定的数据库和表，用右键单击要修改的表，从弹出的快捷菜单中选择“所有任务”子菜单下的“管理触发器”选项，则会出现触发器属性对话框。（3）在名称选项框中选择要修改的触发器的名称，然后在文本框中修改触发器的SQL语句，单击“检查语法”按钮，可以检查语法是否正确。,2使用Transact-SQL语句修改触发器正文,语法： ALTER TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR AFTER | INSTEAD OF DELETE | INSERT | UPDATE ASsql_statements,七、删除触发器,使用企业管理器 表-管理触发器选项-触发器属性对话框-选择要删除的触发器-单击“删除”按钮，即可删除该触发器。使用系统命令DROP TRIGGER删除指定的触发器。语法： DROP TRIGGER trigger ,.n 删除触发器所在的表时，SQL Server将会自动删除与该表相关的触发器。,八、显示有关触发器的信息,1查看触发器的信息sp_ help trigger_name 显示触发器的所有者和创建时间sp_ helptext trigger_name 显示触发器的源代码sp_depends trigger_name 显示该触发器参考的对象清单,一、使用 RAISERROR返回信息,返回用户定义的错误信息并设系统标志，记录发生错误。通过使用 RAISERROR 语句，客户端可以从 sysmessages 表中检索条目，或者使用用户指定的严重度和状态信息动态地生成一条消息。这条消息在定义后就作为服务器错误信息返回给客户端。语法 RAISERROR (消息字符串 ,严重度，状态）,补充知识：,二、事 务 控 制,事务是将一系列操作变成独立的逻辑工作单元，其中任何一个语句执行时出错，系统都会自动回滚到事务开始前的状态，避免垃圾数据的产生。事务具有以下属性： (1)原子性：意味着对数据的修改，要么全都执行，要么全都不执行。 (2)一致性：完成事务后，所有的数据必须保持一致状态。 (3)隔离性：在并发环境中，各个事务是独立的。如果进行事务回滚操作，它能够重新装载起始数据，回到开始事务时刻的状态。(4)持久性：提交事务后，它对于系统的影响是永久性的。即使出现系统故障，事务对数据的修改也将一直保持。,事务控制语句有：,1、BEGIN TRAN语句BEGIN TRAN语句表示事务开始，其语法格式为： BEGIN TRAN transaction_name |tran_name_variableWITH MARK description参数： transaction_name为事务名。 tran_name_variable是用户定义的事务名称变量。WITH MARK 关键字指定在日志中标记事务，description是描述该标记的字符串。,