SQLServer存储过程和触发器

SQLServer存储过程和触发器4.1SQLServer存储过程

存储过程概述存储过程(StoredProcedure)是一组为了完成特定功能T-SQL语句集合，经编译后存储在SQLServer服务器端数据库中。存储过程可以分为两类：系统存储过程和自定义存储过程。系统存储过程系统存储过程系统存储过程在SQLServer安装成功后，就已经存储在系统数据库Master中，这些存储过程都是以sp_为前缀命名的它们主要是从系统表中获取信息，系统管理员可以通过简单调用系统存储过程而完成复杂的SQLServer管理工作。可以通过系统存储过程完成许多管理性或信息的操作。系统存储过程在Master数据库中，在其他数据库中可以直接调用，调用时不必在存储过程名前加上数据库名。自定义存储过程自定义存储过程是由用户创建并能完成某一特定功能的存储过程。存储过程的优点1．提高应用程序的通用性和可移植性2．可以更有效地管理用户操作数据库的权限3．可以提高T-SQL的速度4．减轻服务器的负担5．块化程序设计。6．减少操作错误。7．能自动处理复杂的或敏感的事务。8．可以实现管理任务自动化。

存储过程的创建与执行创建前确定所有的输入参数以及传给调用者的输出参数。被执行的针对数据库的操作语句，包括调用其它存储过程的语句。返回给调用者的状态值，以指明调用是成功还是失败。一个存储过程的最大尺寸为128M1.直接创建存储过程(1)打开MicrosoftSQLServerManager管理器(2)单击数据库前面的“+”号，然后单击“Material_Data1”数据库前面的“+”号，再单击“可编程性”前面的“+”号，选择“存储过程”，单击鼠标右键，在弹出的快捷菜单中单击“新建存储过程”命令。(3)打开了一个创建存储过程的数据库引擎查询模板，修改相应参数即可。直接创建存储过程2.代码创建存储过程语法CREATEPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}

[VARYING][=default][OUTPUT]

][,...n]

WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

ASsql_statement[...n]begin命令行或命令块end说明procedure_name：用于指定要创建的存储过程的名称。number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。@parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。data_type：用于指定参数的数据类型。Default：用于指定参数的默认值。OUTPUT：表明该参数是一个返回参数。recompile:表示每次执行此存储过程时都重新编译一次encryption:所创建的存储过程的内容会被加密3.代码执行存储过程利用T-SQL执行存储过程的语法格式如下：execute过程名[参数值，…[output](1)没有参数的存储过程创建createprochyproclasselect*frommanagerwherewage>1800执行该存储过程executehyprocl(2)有参数存储过程创建createprochyproc2@mingzint,@maxgzintasselect*frommanagerwherewagebetween@mingzand@maxgz执行实例假设要显示工资在1000到2000之间的manager信息，具体代码：executehyproc21005，18004.1.3修改存储过程修改存储过程具体格式如下：alterproc过程名@parameter参数类型@parameter参数类型outputassql_statement[...n]begin命令行或命令块end实例修改存贮过程hyproc2,输出manager性别分类人员数与总工资。

alterprochyproc2@sex1char(2),@managercountintoutput,@wagetotalrealoutputasbeginSelect*frommanagerselect@managercount=count(wage)frommanagerwheresex=@sex1select@wagetotal=sum(wage)frommanagerwheresex=@sex1end实例假设要显示manager信息及输出工资的最大值与平均值，具体代码如下：Declare@x1char(2),@x2realexecutehyproc2'男',@x1output,@x2output4.2SQLServer触发器触发器是一种特殊类型的存储过程，是用户自定义的复杂的完整性控制过程。特点：功能强、开销高维护行级数据的完整性与CHECK约束相比，能实现更加复杂的数据完整性数据完整性完整性是指数据的正确性相容性（一致性）三类基本完整性规则域完整性规则使基本表的列输入有效。控制域完整性有效的方法有：限制数据类型、格式、可能的取值范围、修改列值时必须满足的条件等。实体完整性规则实体完整性规则用来约束现实世界中的实体是可区分的，即它们具有唯一性标识。这一规则在关系模型中的体现是基本表所有主属性都不能取空值（NULL）。参照完整性规则参照完整性规则用来约束具有参照关系的两个表中，主码和外码的数据要保持一致。触发器的作用完成比约束更复杂的数据约束检查所做的SQL是否允许触发器可以检查SQL所做的操作是否被允许。例如：在产品库存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该产品库存数量是否为零，如果不为零则取消该删除操作。修改其它数据表里的数据当一个SQL语句对数据表进行操作的时候，触发器可以根据该SQL语句的操作情况来对另一个数据表进行操作。例如：一个订单取消的时候，那么触发器可以自动修改产品库存表，在订购量的字段上减去被取消订单的订购数量。调用更多的存储过程触发器本身就是一种存储过程，而存储过程是可以嵌套使用的，所以触发器也可以调用一个或多过存储过程。触发器的作用发送SQLMail在SQL语句执行完之后，触发器可以判断更改过的记录是否达到一定条件，如果达到这个条件的话，触发器可以自动调用SQLMail来发送邮件。例如：当一个订单交费之后，可以物流人员发送Email，通知他尽快发货。返回自定义的错误信息约束是不能返回信息的，而触发器可以。例如插入一条重复记录时，可以返回一个具体的友好的错误信息给前台应用程序。更改原本要操作的SQL语句触发器可以修改原本要操作的SQL语句例如原本的SQL语句是要删除数据表里的记录，但该数据表里的记录是最要记录，不允许删除的，那么触发器可以不执行该语句。防止数据表构结更改或数据表被删除为了保护已经建好的数据表，触发器可以在接收到Drop和Alter开头的SQL语句里，不进行对数据表的操作。触发器的种类在SQLServer2005中，触发器可以分为两大类：DML触发器和DDL触发器DML触发器：DML触发器是当数据库服务器中发生数据操作语言（DataManipulationLanguage）事件时执行的存储过程。DML触发器又分为两类：After触发器和InsteadOf触发器DDL触发器：DDL触发器是在响应数据定义语言（DataDefinitionLanguage）事件时执行的存储过程。DDL触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。SQLSERVER2005新增添功能After触发器和InsteadOf触发器After触发器是在记录更变完之后才被激活执行的。以删除记录为例：SQLServer先将要删除的记录存放在删除表里，然后把数据表里的记录删除。再激活After触发器，执行After触发器里的SQL语句。执行完毕之后，删除内存中的删除表，退出整个操作。InsteadOf触发器是在这些操作进行之前就激活了，并且不再去执行原来的SQL操作，而去运行触发器本身的SQL语句。触发器的工作原理在SQLServer2005里，为每个DML触发器都定义了两个特殊的表，一个是插入表，一个是删除表。这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。这两个表的结构与触发器所在数据表的结构是完全一致的，当触发器的工作完成之后，这两个表也将会从内存中删除。插入表里存放的是更新前的记录对于插入记录操作来说，插入表里存放的是要插入的数据对于更新记录操作来说，插入表里存放的是要更新的记录。删除表里存放的是更新后的记录对于更新记录操作来说，删除表里存放的是更新前的记录（更新完后即被删除）；对于删除记录操作来说，删除表里存入的是被删除的旧记录。触发器的工作原理激活触发器的动作Inserted表Deleted表Insert存放要插入的记录Update存放要更新的记录存放更新前的旧记录Delete

存放要删除的旧记录其他注意事项After触发器只能用于数据表中，InsteadOf触发器可以用于数据表和视图上，但两种触发器都不可以建立在临时表上。一个数据表可以有多个触发器，但是一个触发器只能对应一个表。在同一个数据表中，对每个操作（如Insert、Update、Delete）而言可以建立许多个After触发器，但InsteadOf触发器针对每个操作只有建立一个。如果针对某个操作即设置了After触发器又设置了InsteadOf触发器，那么Insteadof触发器一定会激活，而After触发器就不一定会激活了。触发器定义语法after触发器：createtrigger触发器名on表名[withencryption]forinsert[，update，delete]asbegin命令行或程序块endInsteadof触发器：createtrigger触发器名on表名或视图名insteadofinsert[，update，delete]asbegin命令行或程序块end实例创建一个触发器，向manager中插入一条记录，同时创建一个数据库表并向表中插入两条记录。(1)打开MicrosoftSQLServerManager管理器。(2)新建一个数据库引擎查询文档。(3)在数据库引擎查询文档中输入如下代码：UseMaterial_Data1(4)按键盘上的"F5”键，显示如下提示信息：命令已成功完成。(5)这样就打开要使用的数据库。实例createtriggerhytriggerlonmanagerforupdateasbegincreatetabletriuser(useridintidentity(1,1)primarykey,usermamevarchar(50),userpwdvarchar(50))insertintotriuser(username,userpwd)values('李明','111')insertintotriuser(usermame,userpwd)values('王明','222')insertintotriuser(usermame,userpwd)values('刘芳','333')end实例(6)选择创建触发器的代码，按键盘上的"F5”键，显示如下提示信息：命令已成功完成。(7)这样就成功创建了触发器。在这里要注意，只是创建了触发器，并没有执行触发器中的代码，即表triuser还不存在，当然该表中也不会有记录。(8)下面来通过对manager表的更新操作调用触发器hytriggerl，具体代码如下：Updatemanagersetwage=wage+100wheremanagerNo='001'(9)选择SQL语句，按下键盘上的"F5”键执行该SQL语句，显示如图提示信息：实例

图4.5执行触发器

实例(10)提示信息表示影响了四行，即更新了仓库表中的一条记录，创建triuser表，并向该表中插入三条记录，下面通过select*fromtriuser来显示触发器产生新表中的数据信息，如图所示。实例图4.6显示触发器执行后的结果

实例在订单明细表里，折扣字段不能大于，如果插入记录时，折扣大于的话，回滚操作。CREATETRIGGER订单明细_InsertON订单明细AFTERINSERTASBEGINif(Select折扣frominsertedbeginprint'折扣不能大于0.6'RollbackTransactionendENDGO实例在订单明细表里，折扣字段不能大于，如果插入记录时，折扣大于的话，回滚操作。CREATETRIGGER订单明细_InsertON订单明细InsteadOfINSERTASBEGINdeclare@订单IDint,@产品IDint,@单价money,@数量smallint,@折扣realset@订单ID=(select订单IDfrominserted)set@产品ID=(select产品IDfrominserted)set@单价=(select单价frominserted)set@数量=(select数量frominserted)set@折扣=(select折扣frominserted)if(@折扣print'折扣不能大于0.6‘elseINSERTinto订单明细(@订单ID,@产品ID,@单价,@数量,@折扣)实例如果更改了学生的学号,希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号)CreateTriggertruStudent

OnStudent

--在Student表中创建触发器

forUpdate

--为什么事件触发

As

--事件触发后所要做的事情

ifUpdate(StudentID)

begin

UpdateBorrowRecord

SetStudentID=

FromBorrowRecordbr,Deleted

d,Insertedi

Where=

end

实例如果该学生已经毕业，希望删除他的学号的同时，也删除它的借书记录。CreatetriggertrdStudent

OnStudent

forDelete

As

DeleteBorrowRecord

FromBorrowRecordbr,Deltedd

Where=

4.2.2查看触发器基本信息通过sp_help能够查看触发器的基本信息触发器名、所有者、创建者和创建时间。其语法格式如下：execsp_help触发器名如查看触发器hytriggerl信息execsp_helphytriggerl实例图4.7查看触发器基本信息

4.2.3查看触发器代码通过sp_helptext能够查看触发器SQL的代码信息，但要注意如果在创建触发器时使withencrypdon选项，则执行该命令也看不到SQL代码。其语法格式如下：execsp_helptext触发器名。若要查看触发器hytriggerl代码execsp_helptexthytriggerl实例图4.8查看触发器hytriggerl代码4.2.4修改触发器

修改触发器的方法很简单，利用T-SQL修改触发器的语法格式如下：after触发器：altertrigger触发器名On表名[withencryption]forinsert[，update，delete]asbegin命令行或程序块End实例修改hytriggerl插入行数据‘刘芳’为‘张清’altertriggerhytriggerlonmanagerforupdateasbegincreatetabletriuser(useridintidentity(1,1)primarykey,usermamevarchar(50),userpwdvarchar(50))insertintotriuser(username,userpwd)values('李明','111')insertintotriuser(usermame,userpwd)values('王明','222')insertintotriuser(usermame,userpwd)values('张清','333')end修改insteadof触发器altertrigger触发器名on表名或视图名insteadofinsert[,update,delete]asbegin命令行或程序块end修改触发器与创建触发器几乎相同，只是把create改为alter即可。删除触发器删除触发器的方法很简单，利用T-SQL删除触发器的语法格式如下：droptrigger触发器名4.2.6DDL触发器DDL触发器是SQLServer2005新增的一个触发器类型，是一种特殊的触发器，它在响应数据定义语言（DDL）语句时触发。一般用于数据库中执行管理任务。与DML触发器一样，DDL触发器也是通过事件来激活，并执行其中的SQL语句的。但与DML触发器不同，DML触发器是响应Insert、Update或Delete语句而激活的，DDL触发器是响应Create、Alter或Drop开头的语句而激活的。以下几种情况下可以使用DDL触发器：数据库里的库架构或数据表架构很重要，不允许被修改。防止数据库或数据表被误操作删除。在修改某个数据表结构的同时修改另一个数据表的相应的结构。要记录对数据库结构操作的事件语法CREATETRIGGER触发器名ON

ALLSERVER或DATABASEFOR或AFTER激活DDL触发器的事件AS

要执行的SQL语句实例启动ManagementStudio，登录到指定的服务器上。在【对象资源管理器】下选择【数据库】，定位到【Northwind】数据库上。单击【新建查询】按钮，在弹出的【查询编辑器】的编辑区里输入以下代码：CREATETRIGGER禁止对数据表操作ONDATABASEFORDROP_TABLE,ALTER_TABLEAS

PRINT'对不起，您不能对数据表进行操作'

ROLLBACK;实例建立一个DDL触发器，用于保护当前SQLServer服务器里所有数据库不能被删除。具体代码如下：CREATETRIGGER不允许删除数据库ONallserver

FORDROP_DATABASEAS

PRINT'对不起，您不能删除数据库'

ROLLBACK;GO在ManagementStudio如果要修改DDL触发器内容，就只能先删除该触发器，再重新建立一个DDL触发器。触发器的创建例：对S表定义一个删除触发器，使得当删除学生记录时，将S_C表中相应的选课记录删除。S（SNO,SNAME）S_C（SNO,CNO,SCORE）触发器的创建例：对S_C表定义一个插入触发器，使得当插入选课记录时，检查所参照的学生学号和课程号是否存在，如果不存在，则撤消所做的插入操作。S（SNO,SNAME）C（CNO，CNAME）S_C（SNO,CNO