管理触发器课件

1

管理触发器2触发器是一种特殊类型的存储过程，它不同于普通的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名称而被直接调用。当往某一个表格中插入记录、修改记录或者删除记录时，SQLSERVER就会自动执行触发器所定义的SQL语句，以确保数据的完整性。触发器的基本概念3SQLSERVER可以检查数据完整性的还有什么情况？比如前面讲过的CHECK约束。但是，与CHECK约束相比，触发器可以强制实现更加复杂的数据完整性，而且可以参考其他表的字段。注意：触发器可以扩展SQLServer约束、默认值和规则的完整性检查逻辑，但只要约束和默认值提供了全部所需的功能，就应使用约束和默认值。4触发器的作用触发器可以使用T-SQL语句进行复杂的逻辑处理，它基于一个表创建，但是可以对多个表进行操作，因此常常用于复杂的业务规则。一般可以使用触发器完成如下功能：5触发器的作用1、级联修改数据库中相关的表。例如：在数据库company中有两个表project和customer，表project中包含项目信息和项目的合作客户名，而customer表中则存储所有客户的信息。如果删除了customer表中的某一客户，如张三，那么在project表中所有和该客户有关的项目记录都应该做相应的调整。如在project表中将所有合作客户为张三的字段改为null或者其它值，或者删除整条和张三有关的记录。而使用触发器就可以实现这样的功能。6触发器的作用2、执行比核查约束更为复杂的约束操作。在触发器中可以书写更复杂的T-SQL语句，例如可以引用多个表，并使用if…else等语句做更复杂的检查。3、拒绝或回滚违反引用完整性的操作。检查对数据表的操作是否违反引用完整性，并选择相应的操作。7触发器的作用4、比较表修改前后数据之间的差别，并根据差别采取相应的操作。例如：若想规定每次工资的变动幅度不能超过40％，使用触发器可以将修改后的表数据和修改前的表数据进行比较，若超出40%，可以回滚该修改操作。8触发器的类型触发器可以分为五种类型：Update触发器：在表上进行更新操作时触发。Insert触发器：在表上进行更新操作时触发Delete触发器：在表上进行更新操作时触发Insteadof触发器：不执行插入、更新或删除操作时，将触发Insteadof触发器。After触发器：在一个触发动作发生之后激发，并提供一种机制以便控制多个触发器的执行顺序。9SQLServer2000新增的触发器SQLServer2000新增了两种类型的触发器AFTER

触发器和INSTEADOF

触发器。其中AFTER要求只有执行某一操作（INSERT、UPDATE、DELETE）之后触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器。对于AFTER

触发器，可以定义哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程sp_settriggerorder

来完成此任务。10INSTEADOF

触发器表示并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅是执行触发器本身。既可在表上定义INSTEADOF

触发器，也可以在视图上定义INSTEADOF

触发器，但对同一操作只能定义一个INSTEADOF

触发器。11触发器的主要优点触发器是自动的：当对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。触发器可以通过数据库中的相关表进行层叠更改。触发器可以强制限制，这些限制比用CHECK约束所定义的更复杂。而且比使用核查约束更为方便的时，触发器可以引用其它数据表中的列。12创建触发器的规则和限制在创建和使用触发器时，需要遵循下列规则：CreateTrigger语句必须是批处理中的第一个语句，且该批处理中随后出现的其它所有语句都将被解释为CreateTrigger语句定义的一部分。在默认情况下，创建触发器的权限将分配给数据表的所有者，且不能将该权限转给其它用户。触发器是数据库对象，其名称必须遵循标识符的命名规则。虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。13虽然不能在临时数据表上创建触发器，但是触发器可以引用临时数据表。既不能在系统数据表创建触发器，也不可以引用系统数据表在包含使用delete或update操作定义的外键的表中，不能定义insteadof和insteadofupdate触发器。虽然TRUNCATETABLE语句类似于没有WHERE子句（用于删除行）的DELETE语句，但它并不会引发DELETE触发器，因为TRUNCATETABLE语句没有被记入日志。Writetext语句不会引发insert或update触发器。14下面的语句不可以用于创建触发器：AlterDatabase、CreateDatabase、DiskInit、DiskResize、DropDatabase、LoadDatabase、LoadLog、Reconfigure、RestoreDatabase、RestoreLog。当创建一个触发器时必须指定：⑴名称；⑵在其上定义触发器的表；⑶触发器将何时激发；⑷激活触发器的数据修改语句。15使用企业管理器管理触发器创建触发器修改触发器删除触发器161、打开企业管理器中，然后展开要在其上创建触发器的表。2、在表上单击鼠标右键，然后在弹出菜单中选择命令“所有任务－管理触发器”。3、在“触发器属性”对话框内的名称栏中选择“新建”4、在“文本”文本框中输入创建触发器的T-SQL语句。5、按下窗口中的“语法检查”按钮，若有错误，修改文本框中的SQL语句，若没有错误，则弹出“语法检查成功！”对话框。6、单击“语法检查成功”对话框中的“确定”按钮，关闭“触发器属性”对话框，即完成。创建触发器17修改触发器1、打开企业管理器，并展开需要修改触发器的表所在的数据库节点，然后选择将要修改的触发器的表。2、在表上单击鼠标右键，然后在弹出菜单中选择命令“所有认为－管理触发器”3、在“触发器属性”对话框中选择需要修改的触发器名4、在“文本”文本框中对触发器定义语句进行修改。直到语法无错误为止。5、关闭“触发器属性”对话框，修改完毕。18删除数据库1、打开企业管理器，并展开需要修改触发器的表所在的数据库节点，然后选择将要修改的触发器的表。2、在表上单击鼠标右键，然后在弹出菜单中选择命令“所有认为－管理触发器”3、在“触发器属性”对话框中选择需要删除的触发器名4、单击对话框中的“删除”按钮。5、在“确定要删除触发器吗？”对话框中单击“确定”按钮，确认删除。19使用T-SQL管理触发器创建触发器查看触发器相关数据修改触发器删除触发器20创建触发器其语法形式如下：CREATETRIGGERtrigger_name

ONtable|view

[WITHENCRYPTION]

{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}AS

sql_statements[…n]

各参数说明如下：

21各参数的说明如下：trigger_name：是用户要创建的触发器的名字。触发器的名字必须符合MSSQLServer的命名规则，且其名字在当前数据库中必须是惟一的Table：是与用户创建的触发器相关联的表的名字，并且该表已经存在。WITHENCRYPTION：表示对包含有CREATETRIGGER文本的syscomments表进行加密[DELETE][,][INSERT][,][UPDATE]：关键字用来指明哪种数据操作将激活触发器，至少要指明一个选项，在触发器的定义中三者的顺序不受限制，且各选项要用逗号隔开。22AFTER：表示只有在执行了指定的操作INSERT、DELETE、UPDATE之后触发器才被激活，执行触发器中的SQL语句。若使用关键字FOR，则表示为AFTER触发器，且该类型触发器仅能在表上创建。INSTEADOF：当为表或视图定义了针对某一操作INSERT、DELETE、UPDATE的INSTEADOF类型触发器，且执行了相应的操作时，尽管触发器被触发，但相应的操作并不被执行，而运行的仅是触发器SQL语句本身。23例子UsecompanyGo--创建触发器Createtrigger触发器_欢迎新员工OnemployeeAfterinsertAsPrint‘新员工加入本公司，欢迎欢迎！’go24--创建触发器Createtrigger触发器_员工走了OnemployeeAfterdeleteAsPrint‘真遗憾你离开公司！’go25Setnocounton--插入一条记录试试触发器操作Insertintoemployee(员工id,姓名，性别，工资)Values(20068,’赵一’,‘男’,1000)--删除一条记录试试DeletefromemployeeWhere员工id=‘20067’go26查看触发器相关数据1、查看表中存在哪些相关触发器使用企业管理器查看：打开企业管理器，在表上单击鼠标右键，然后选择命令“所有任务－管理触发器”，则在“触发器属性”窗口可以看到新建的触发器。27查看触发器相关数据使用系统存储过程sp_helptrigger：语法如下：execsp_helptrigger‘table’[,’type’]其中：table：触发器所在的表名Type：指定列出的操作类型的触发器。若不指定，则列出所有的触发器。例如：execsp_helptrigger‘employee’28查看触发器的定义文本使用企业管理器查看：在企业管理器的“触发器属性”窗口查看，也可以打开系统表syscomments查看。29查看触发器的定义文本触发器的定义文本存储在系统表syscomments中，但是可以通过执行系统存储过程sp_helptext进行查看，执行该存储过程的语法格式如下：Sp_helptext‘trigger_name’例如：execsp_helptext‘触发器_欢迎新员工’注意：若在定义触发器时对定义文本进行加密处理，则不能使用任何方法查看触发器的定义文本。30查看触发器的所有者和创建日期可以使用系统存储过程sp_help，其格式如下：Execsp_help‘trigger_name’例如：execsp_help‘触发器_欢迎新员工’31修改触发器若希望对现有触发器进行修改，可以使用altertrigger语句，其语法格式如下：ALTERTRIGGERtrigger_name

ONtable|view

[WITHENCRYPTION]

{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}

AS

sql_statement[...n]注意：就是创建触发器的语句中的create换为alter，其余部分完全相同。但是在altertrigger中引用的触发器名必须是已经存在的触发器名32触发器更名使用系统存储过程sp_rename完成。其语法格式如下：sp_renameoldname,newname例如：将触发器“触发器_欢迎新员工”更名为welcome_Trigger:Execsp_rename‘触发器_欢迎新员工’,‘welcom_trigger’33删除触发器使用droptrigger语句，语法如下：Droptriggertrigger_name[,…n]其中trigger_name是触发器名称，可以同时删除多个触发器。例如：droptrigger触发器_员工走了注意：当删除数据表时，也将同时删除所有与该数据表相关联的触发器。而且当删除触发器时，系统也将从sysobjects和syscomments系统表中删除有关该触发器的信息。34确保数据完整性Sp_dboptionpubs,’selectinto’,trueGoSelect*intotblstoresfrompubs..storesSelect*intotblsalesfrompubs..salesgo35selectsa.stor_id,st.stor_namefromtblstoresst,tblsalessawherest.stor_id=sa.stor_idgocreatetriggertrDelAuthorsontblsalesfordeleteasraiserror('%drowsaregoingtobedeletedfromthesalestable!',0,1,@@rowcount)36CreatetriggertrDelSalesOntblStoresForDeleteAsDeletetblsalesfromdeletedwheredeleted.stor_id=tblSales.stor_id最后，继续删除stor_id7067,即“news&brews”商店DeletefromtblstoresWheretblstores.stor_id=‘7067’37封装事务规则CreateTriggertrDelSalesOntblSalesForDeleteAsIf(SelectCount(*)fromDeletedWhereDeleted.qty>=20)>0BeginPrint'youcannotdeleteanyofthesestores'Print'somestoreshavemorethan20sales!'Print'Rollingbackyourtransaction!'RollbackTransactionend38DeleteFromtblSalesWherestor_id=‘7066’至少有一个商店（stor_id=‘7066’）有不少于20条的销售记录，为了验证是否如此，运行select语句：selectstor_id,qtyfromtblsales现在用stor_id=6380再次运行delete语句，正如预计的那样，这会删除qty域不多于20条的商店。Deletefromtblsaleswherestor_id=‘6380’39确保引用完整性CreatetriggertrInsUpdSalesOntblSalesForinsert,UpdateAsIf(selectcount(*)fromtblstores,insertedWheretblstores.stor_id=inserted.stor_id)=0BeginPrint'thestor_idyouhaveentereddoesnotexist'Print'inthestorestable!'RollbacktransactionEnd

触发器将会对任何单个的Update和Insert行为发生作用，确保在tblstores表中的都是有效的stor_id。然而，如果运行selectinto，触发器将不能正确触发