《数据库原理与应用》10.触发器的创建和使用.ppt

孙 发 勤 扬州大学新闻与传媒学院 YZU.SUN163.COM,数据库原理与应用 第十讲 触发器创建和使用,1、创建触发器 2 、查看、修改和删除触发器 3 、触发器的应用,本讲主要内容,触发器,触发器是一种特殊类型的存储过程，它不同于前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名称而被直接调用。触发器是一个功能强大的工具，它使每个站点可以在有数据修改时自动强制执行其业务规则。触发器可以用于 SQL Server 约束、默认值和规则的完整性检查。,SQL SERVER可以检查数据完整性的还有什么情况? 比如前面讲过的CHECK约束。但是，与CHECK约束相比，触发器可以强制实现更加复杂的数据完整性，而且可以参考其他表的字段。 注意:触发器可以扩展SQL Server约束、默认值和规则的完整性检查逻辑，但只要约束和默认值提供了全部所需的功能，就应使用约束和默认值。,触发器可以使用T一SQL语句进行复杂的逻辑处理，它基于一个表创建，但是可以对多个表进行操作，因此常常用于复杂的业务规则。工般可以使用触发器完成如下功能,触发器的作用,1、级联修改数据库中相关的表。 例如:在数据库company中有两个表project和customer,表project中包含项目信息和项目的合作客户名，而customer表中则存储所有客户的信息。如果删除了customer表中的某一客户，如张三，那么在project表中所有和该客户有关的项目记录都应该做相应的调整。如在project表中将所有合作客户为张三的字段改为null或者其它值，或者删除整条和张三有关的记录。而使用触发器就可以实现这样的功能。,2、执行比核查约束更为复杂的约束操作。 在触发器中可以书写更复杂的T一SQL语句，例如可以引用多个表，并使用if . else等语句做更复杂的检查。 3、拒绝或回滚违反引用完整性的操作。 检查对数据表的操作是否违反引用完整性，并选择相应的操作。,4、比较表修改前后数据之间的差别，并根据差别采取相应的操作。例如:若想规定每次工资的变动幅度不能超过40%，使用触发器可以将修改后的表数据和修改前的表数据进行比较，若超出40%，可以回滚该修改操作。,触发器的类型,触发器可以分为五种类型: Update触发器:在表上进行更新操作时触发。 Insert触发器:在表上进行更新操作时触发 Delete触发器:在表上进行更新操作时触发 Instead of触发器:不执行插入、更新或删除操作时，将触发Instead of触发器。 After触发器:在一个触发动作发生之后激发，并提供一种机制以便控制多个触发器的执行顺序。,AFTER要求只有执行某一操作(INSERT,UPDATE、DELETE)之后触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器。对于AFTER触发器，可以定义哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程sp settriggerorder来完成此任务。,INSTEAD OF触发器表示并不执行其所定义的操作(INSERT、UPDATE、DELETE)，而仅是执行触发器本身。既可在表上定义INSTEAD OF触发器，也可以在视图上定义INSTEAD OF触发器，但对同一操作只能定义一个INSTEAD OF触发器。,触发器主要优点如下：,触发器是自动的：当对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。 触发器可以通过数据库中的相关表进行层叠更改。 触发器可以强制限制，这些限制比用 CHECK 约束所定义的更复杂。,创建触发器,创建触发器应该考虑以下几个问题： CREATE TRIGGER 语句必须是批处理中的第一个语句。 创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。 触发器为数据库对象，其名称必须遵循标识符的命名规则。 虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。 虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。,在含有用 DELETE 或 UPDATE 操作定义的外键的表中，不能定义 INSTEAD OF 和 INSTEAD OF UPDATE 触发器。 虽然 TRUNCATE TABLE 语句类似于没有 WHERE 子句（用于删除行）的 DELETE 语句，但它并不会引发 DELETE 触发器，因为 TRUNCATE TABLE 语句没有记录。 WRITETEXT 语句不会引发 INSERT 或 UPDATE 触发器。 当创建一个触发器时必须指定： 名称；在其上定义触发器的表；触发器将何时激发；激活触发器的数据修改语句。,下面的语句不可以用于创建触发器: Alter Database、Create Database、Disk Init、 Disk Resize、Drop Database、Load Database、 Load Log、Reconfigure、Restore Database、Restore Log. 当创建一个触发器时必须指定:(1)名称;(2)在其上定义触发器的表;(3)触发器将何时激发;(4)激活触发器的数据修改语句。,1. 使用企业管理器创建触发器,在企业管理器中，展开指定的服务器和数据库项，然后展开要在其上创建触发器的表所在的数据库，用右键单击该表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在该对话框中，在名称文本框中选择新建，然后在文本框中输入触发器文本。单击“检查语法”按钮，则可以检查语法是否正确。单击“应用”按钮，则在名称下拉列表中会有新创建的触发器名称。最后，单击“确定”按钮，即可关闭该对话框，成功创建触发器。,2. 使用CREATE TRIGGER命令创建触发器,其语法形式如下： CREATE TRIGGER trigger_name ONtable|view WITHENCRYPTION FOR | AFTER | INSTEAD OF DELETE, INSERT , UPDATE WITHAPPEND NOTFORREPLICATION AS,IFUPDATE(column) AND|ORUPDATE(column) .n |IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask) comparison_operatorcolumn_bitmask.n sql_statement.n ,各参数的说明如下:,AFTER:表示只有在执行了指定的操作INSERT, DELETE, UPDATE之后触发器才被激活，执行触发器中的SQL语句。若使用关键字FOR，则表示为AFTER触发器，且该类型触发器仅能在表上创建。 INSTEAD OF:当为表或视图定义了针对某一操作INSERT、DELETE、UPDATE的INSTEAD OF类型触发器，且执行了相应的操作时，尽管触发器被触发，但相应的操作并不被执行，而运行的仅是触发器SQL语句本身。,例子 Use company Go -创建触发器 Create trigger 触发器-欢迎新员工 On employee After insert As Print 新员工加入本公司，欢迎欢迎! go,例：,创建了一个触发器，在 titles 表上创建一个插入、更新类型的触发器，其程序清单如下： USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = reminder AND type = TR) DROP TRIGGER reminder GO CREATE TRIGGER reminder ON titles FOR INSERT, UPDATE AS sql_statements GO,查看、修改和删除触发器,1. 查看触发器 （）使用企业管理器查看触发器信息 （）使用系统存储过程查看触发器,sp_help、sp_helptext和sp_depends具体用途和语法形式如下。,sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。 sp_help 触发器名称 sp_helptext：用于查看触发器的正文信息 sp_helptext 触发器名称 sp_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。 sp_depends 触发器名称 sp_depends 表名,修改触发器,（）使用企业管理器修改触发器正文 在企业管理器中，展开指定的服务器和数据库，选择指定的数据库和表，用右键单击要修改的表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在名称选项框中选择要修改的触发器的名称，然后在文本框中修改触发器的SQL语句，单击“检查语法”按钮，可以检查语法是否正确。,（）使用sp_rename命令修改触发器的名称,sp_rename命令的语法形式如下： sp_rename oldname,newname,（）使用alter trigger命令修改触发器正文,alter trigger命令的语法形式如下： ALTERTRIGGERtrigger_name ON(table|view) WITHENCRYPTION ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE NOTFORREPLICATION AS sql_statement.n |,(FOR|AFTER|INSTEADOF)INSERT,UPDATE NOTFORREPLICATION AS IFUPDATE(column) AND|ORUPDATE(column) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operatorcolumn_bitmask.n sql_statement.n ,删除触发器,（）使用系统命令DROP TRIGGER删除指定的触发器，其语法形式如下： DROP TRIGGER trigger ,.n （）删除触发器所在的表时，SQL Server将会自动删除与该表相关的触发器。 （）在企业管理器中，用右键单击要删除的触发器所在的表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在名称选项框中选择要删除的触发器，单击“删除”按钮，即可删除该触发器。,触发器的应用,1. 使用INSERT触发器 INSERT触发器通常被用来更新时间标记字段，或者验证被触发器监控的字段中的数据满足要求的标准，以确保数据完整性。 2. 使用UPDATE触发器 修改触发器和插入触发器的工作过程基本上一致，修改一条记录等于插入了一条新的记录并且删除一条旧的记录。,3.使用DELETE触发器,DELETE触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除。 第二种情况是执行可删除主记录的子记录的级联删除操作。可以使用这样的触发器从主销售记录中删除所有的定单项。,4. 使用嵌套的触发器,如果一个触发器在执行操作时引发了另一个触发器，而这个触发器又接着引发下一个触发器这些触发器就是嵌套触发器。触发器可嵌套至 32 层，并且可以控制是否可以通过“嵌套触发器“服务器配置选项进行触发器嵌套。如果允许使用嵌套触发器，且链中的一个触发器开始一个无限循环，则超出嵌套级，而且触发器将终止。在执行过程中，如果一个触发器修改某个表，而这个表已经有其它触发器，这是就要使用嵌套触发器。,Select * into tblstores fonm pubs. stores Select * into tblsales from pups. sales go select sa.stor_id,st.stor_name from tblstores st,tblsales sa where st.stor_id=sa.stor_id go create trigger trDelAuthors On tblsales for delete as raiserror(%d rows are going to be deleted from the sales table!,0 ,1, rowcount),Create trigger trDelSales On tblStores For Delete As Delete tblsales from deleted where deleted.stor_id=tbISales.stor_id Delete from tblstores Where tblstores.stor