sql2005常用存储过程、视图、索引器与触发器

Chapter6

常用存储过程、视图、索引器与触发器

本章目标掌握如何创建视图掌握如何创建索引掌握如创创建触发器掌握常用的存储过程什么是视图CREATEVIEWview_stuInfo_stuMarksASSELECT姓名=stuName,

学号=stuInfo.stuNo,…基于学员信息表和成绩表创建视图教员需要的视图：

方便查看学员的成绩班主任需要的视图：

方便查看学员的档案什么是视图视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上视图中并不存放数据，而是存放在视图所引用的原始表（基表）中同一张原始表，根据不同用户的不同需求，可以创建不同的视图什么是视图视图的用途筛选表中的行防止未经许可的用户访问敏感数据降低数据库的复杂程度将多个物理数据库抽象为一个逻辑数据库如何创建视图使用T-SQL语句创建视图的语法CREATEVIEW

view_name

AS<select语句>如何创建视图创建方便教员查看成绩的视图IFEXISTS(SELECT*FROMsysobjectsWHEREname='view_stuInfo_stuMarks')

DROPVIEW

view_stuInfo_stuMarksGOCREATEVIEW

view_stuInfo_stuMarks

ASSELECT姓名=stuName,学号=stuInfo.stuNo,

笔试成绩=writtenExam,机试成绩=labExam,

平均分=(writtenExam+labExam)/2FROMstuInfoLEFTJOINstuMarks

ONstuInfo.stuNo=stuMarks.stuNoGOSELECT*FROMview_stuInfo_stuMarks检测是否存在创建视图使用视图删除视图什么是索引汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等我们可以根据拼音或偏旁部首，快速查找某个字词IndexesUseKeyValuestoLocateData（根据索引键查找定位数据行）

DataPages（数据页）…IndexPages（索引页）

什么是索引什么是索引SQLServer中的数据也是按页（8KB）存放索引：是SQLServer编排数据的内部方法。它为SQLServer提供一种方法来编排查询数据。索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页。索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。索引类型唯一索引：唯一索引不允许两行具有相同的索引值主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个如何创建索引演示使用企业管理器创建如何创建索引CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameONtable_name(column_name…)[WITHFILLFACTOR=x]UNIQUE表示唯一索引，可选CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，可选FILLFACTOR表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比

使用T-SQL语句创建索引的语法：唯一索引聚集索引或非聚集索引填充因子（系数）:指定一个0～100之间的值，表示索引页填充的百分比USEstuDBGOIFEXISTS(SELECTnameFROMsysindexes

WHEREname='IX_writtenExam')DROPINDEXstuMarks.IX_writtenExam

/*--笔试列创建非聚集索引：填充因子为30％--*/CREATENONCLUSTEREDINDEXIX_writtenExamONstuMarks(writtenExam) WITHFILLFACTOR=30GO/*指定按索引IX_writtenExam

查询*/SELECT*FROMstuMarks(INDEX=IX_writtenExam)WHEREwrittenExamBETWEEN60AND90如何创建索引

在stuMarks表的writtenExam列创建索引：检测索引是否存在：

索引存放在系统表sysindexes中索引的优缺点优点加快访问速度加强行的唯一性缺点带索引的表在数据库中需要更多的存储空间操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新创建索引的指导原则请按照下列标准选择建立索引的列。该列用于频繁搜索该列用于对数据进行排序请不要使用下面的列创建索引：列中仅包含几个不同的值。表中仅包含几行。为小型表创建索引可能不太划算，因为SQLServer在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长小结1视图是一张虚拟表，可以方便不同用户的查询，提高数据的安全性，筛选特定的数据行建立索引有助于快速检索数据。索引分为唯一索引、主键索引、聚集索引、非聚集索引触发器是一种特殊类型的存储过程,基于某个表或视图(触发表、触发视图)的一个或多个列创建，用于监控它们的insert、update、delete操作（触发操作），当发生以上操作时自动执行事先定义的步骤，可进行复杂的数据完整性监测和约束触发器的特点不能直接调用，只有在对触发器表的数据进行更改时，才自动执行不能传递和接受参数触发器可以实施更为复杂的数据完整性约束触发器触发时：系统自动在内存中创建deleted表或inserted表只读，不允许修改；触发器执行完成后，自动删除inserted表临时保存了插入或更新后的记录行可以从inserted表中检查插入的数据是否满足业务需求如果不满足，则向用户报告错误消息，并回滚插入操作deleted表临时保存了删除或更新前的记录行可以从deleted表中检查被删除的数据是否满足业务需求如果不满足，则向用户报告错误消息，并回滚插入操作

inserted和deleted表2-1

inserted和deleted表2-2修改操作inserted表deleted表增加(INSERT)记录存放新增的记录删除(DELETE)记录存放被删除的记录修改(UPDATE)记录存放更新后的记录存放更新前的记录inserted表和deleted表存放的信息创建触发器的语法：

如何创建触发器CREATETRIGGERtrigger_name

ONtable_name

[WITHENCRYPTION]

FOR[DELETE,INSERT,UPDATE]

AS

T-SQL语句GOWITHENCRYPTION表示加密触发器定义的SQL文本DELETE,INSERT,UPDATE指定触发器的类型INSERT触发器transInfocardIDtransType

transMoney1001000210010002存入

300存入

500insertedcardIDtransType

transMoney10010001

支取200transInfocardIDtransType

transMoney1001000210010002存入

300存入50010010001支取200插入记录行触发insert触发器。向inserted表中插入新行的副本触发器检查inserted表中插入的新行数据，确定是否需要回滚或执行其他操作INSERT触发器的工作原理：INSERT触发器示例3-1问题：解决上述的银行取款问题：当向交易信息表(transInfo)中插入一条交易信息时，我们应自动更新对应帐户的余额。

分析：在交易信息表上创建INSERT触发器从inserted临时表中获取插入的数据行根据交易类型(transType)字段的值是存入/支取，增加/减少对应帐户的余额。

关键代码CREATETRIGGER

trig_transInfo

ON

transInfo

FORINSERT

ASDECLARE@typechar(4),@outMoneyMONEYDECLARE@myCardIDchar(10),@balanceMONEYSELECT@type=transType,@outMoney=transMoney，

@myCardID=cardIDFROMinsertedIF(@type='支取')UPDATEbankSETcurrentMoney=currentMoney-@outMoneyWHEREcardID=@myCardIDELSEUPDATEbankSETcurrentMoney=currentMoney+@outMoneyWHEREcardID=@myCardID…..GOINSERT触发器示例3-2从inserted表中获取交易类型、教员金额等根据交易类型，减少或增加对应卡号的余额INSERT触发器示例3-3transInfocardIDtransType

transMoney1001000210010002存入

300存入

50010010001支取200DELETE触发器transInfocardIDtransType

transMoney1001000210010002存入300存入500deletedcardIDtransType

transMoney10010001支取

200删除记录行触发delete触发器向deleted表中插入被删除的副本触发器检查deleted表中被删除的数据，决定是否需要回滚或执行其他操作DELETE触发器的工作原理：问题：当删除交易信息表时，要求自动备份被删除的数据到表backupTable中。分析：在交易信息表上创建DELETE触发器被删除的数据可以从deleted表中获取DELETE触发器示例3-1关键代码CREATETRIGGERtrig_delete_transInfoONtransInfoFORDELETEASprint'开始备份数据，请稍后'IFNOTEXISTS(SELECT*FROMsysobjects

WHEREname='backupTable')SELECT*INTObackupTableFROMdeletedELSEINSERTINTObackupTableSELECT*FROMdeletedprint'备份数据成功，备份表中的数据为:'SELECT*FROMbackupTable

GO从deleted表中获取被删除的交易记录DELETE触发器示例3-2DELETE触发器示例3-3Deleted（更新前的数据）customerNamecardID

currentMoney李四100000021bankcustomerName

cardID

currentMoney张三

100100011000

李四100000021UPDATE触发器删除记录行向deleted表中插入被删除的副本检查deleted和inserted表中的数据，确定是否需要回滚或执行其他操作UPDATE触发器的工作原理：李四

1000000220001向inserted表中插入被添加的副本Inserted（更新后的数据）customerNamecardID

currentMoney李四1000000220001插入记录行问题：跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。分析：在bank表上创建UPDATE触发器修改前的数据可以从deleted表中获取修改后的数据可以从inserted表中获取UPDATE触发器示例3-1关键代码CREATETRIGGERtrig_update_bankONbankFORUPDATEASDECLARE@beforeMoney

MONEY,@afterMoneyMONEYSELECT@beforeMoney=currentMoneyFROMdeletedSELECT@afterMoney=currentMoneyFROMinsertedIFABS(@afterMoney-@beforeMoney)>20000BEGINprint'交易金额:'+convert(varchar(8),

ABS(@afterMoney-@beforeMoney))RAISERROR('每笔交易不能超过2万元，交易失败',16,1)ROLLBACKTRANSACTIONENDGO从deleted表中获取交易前的余额，从inserted表中获取交易后的余额UPDATE触发器3-2交易金额是否>2万回滚事务，撤销交易UPDATE触发器3-3列级UPDATE触发器3-1UPDATE触发器除了跟踪数据的变化（修改）外，还可以检查是否修改了某列的数据使用UPDATE（列）函数检测是否修改了某列问题：交易日期一般由系统自动产生，默认为当前日期。为了安全起见，一般禁止修改，以防舞弊。

分析：UPDATE（列名）函数可以检测是否修改了某列

关键代码CREATETRIGGERtrig_update_transInfoONtransInfoFORUPDATEASIFUPDATE(transDate)BEGINprint'交易失败'RAISERROR(‘安全警告：交易日期不能修改，由系统自动产生',16,1)ROLLBACKTRANSACTIONENDGO检查是否修改了交易日期列transDate回滚事务，撤销交易列级UPDATE触发器3-2列级UPDATE触发器3-3查看触发器（1）使用SQLServer管理平台查看触发器信息。在SQLServer管理平台中，展开服务器和数据库，选择并展开表，然后展开触发器选项，右击需要查看的触发器名称，如图9-4所示，从弹出的快捷菜单中，选择“编写触发器脚本为→create到→新查询编辑器窗口”，则可以看到触发器的源代码。图9-4查看触发器

查看触发器（2）使用系统存储过程查看触发器。系统存储过程sp_help、sp_helptext和sp_depends分别提供有关触发器的不同信息。其具体用途和语法形式如下。sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。

sp_help‘触发器名称’sp_helptext：用于查看触发器的正文信息。

sp_helptext‘触发器名称’sp_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。

sp_depends‘触发器名称’

sp_depends‘表名’修改触发器通过SQLServer管理平台、存储过程，可以修改触发器的正文和名称。1．使用SQLServer管理平台修改触发器正文。在管理平台中，展开指定的表，右击要修改的触发器，从弹出的快捷菜单中选择“修改”选项，则会出现触发器修改窗口，如图9-5所示。在文本框中修改触发器的SQL语句，单击“语法检查”按钮，可以检查语法是否正确，单击“执行”按钮，可以成功修改此触发器。图9-5触发器修改窗口

修改触发器修改DML触发器的语法形式如下：

ALTERTRIGGERschema_name.trigger_nameON(table|view)[WITH<dml_trigger_option>[,...n]](FOR|AFTER|INSTEADOF){[DELETE][,][INSERT][,][UPDATE]}[NOTFORREPLICATION]AS

{sql_statement[;][...n]|EXTERNALNAME<methodspecifier>[;]}<dml_trigger_option>::=[ENCRYPTION][&lEXECUTEASClause>]<method_specifier>::=assembly_name.class_name.method_name修改DDL触发器的语法形式如下：

ALTERTRIGGERtrigger_nameON{DATABASE|ALLSERVER}[WITH<ddl_trigger_option>[,...n]]{FOR|AFTER}{event_type[,...n]|event_group}AS{sql_statement[;]|EXTERNALNAME<methodspecifier>[;]}<ddl_trigger_option>::=[ENCRYPTION][&lEXECUTEASClause>]<method_specifier>::=assembly_name.class_name.method_name

修改触发器例9-13修改触发器。程序清单如下：

CREATETRIGGERs_reminderONSWITHENCRYPTIONAFTERINSERT,UPDATEASRAISERROR('不能对该表执行添加、更新操作',16,10)ROLLBACKGO--下面修改触发器.ALTERTRIGGERs_reminderONSAFTERINSERTASRAISERROR('不能对该表执行添加操作',16,10)ROLLBACKGO

修改触发器2．使用sp_rename命令修改触发器的名称。

sp_rename命令的语法形式如下：

sp_rename

oldname,newname

删除触发器由于某种原因，需要从表中删除触发器或者需要使用新的触发器，这就必须首先删除旧的触发器。只有触发器所有者才有权删除触发器。删除已创建的触发器有三种方法：（1）使用系统命令DROPTRIGGER删除指定的触发器。其语法形式如下：

DROPTRIGGER{trigger}[,...n]

（2）删除触发器所在的表。删除表时，SQLServer将会自动删除与该表相关的触发器。（3）在SQLServer管理平台中，展开指定的服务器和数据库，选择并展开指定的表，右击要删除的触发器，从弹出的快捷菜单中选择“删除”选项，即可删除该触发器。

触发器是在对表进行插入、更新或删除操作时自动执行的存储过程，触发器通常用于强制业务规则触发器还是一个特殊的事务单元，当出现错误时，可以执行ROLLBACKTRANSACTION回滚撤销操作触发器一般都需要使用临时表：deleted表和inserted表，它们存放了被删除或插入的记录行副本触发器类型：

INSERT触发器

UPDATE触发器

DELETE触发器小结2常用存储过程我们在项目开发中有一些常用的存储过程大批量数据的插入分页批量插入在项目的开发中，有时会碰到批量数据录入数据库的情况，编写多条插入语句效率太低批量数据插入1-1CreateprocedureSplitString

(

@stringnvarchar(1000),

--要分隔的字符串

@splitcharnvarchar(10)=,,

--分隔字符

@tablenamenvarchar(50),

--存入的表名称

@fieldnamenvarchar(50)=[id]

--存入的字段名称

)

as

--将字符串分隔开放进表中

declare@lint

--第一个分隔字符的位置

declare@sint

--第二个分隔字符的位置

set@l=0

set@s=charindex(@splitchar,@string,@l)

while@l<=len(@string)

begin

declare@idnvarchar(50)

if@s=0set@s=len(@string)+1

--如果到最后一个字符串那么第二个分隔字符的位置就是这个字符串的长度加一

set@id=substring(@string,@l,@s-@l)

--取值

set@l=@s+1

set@s=charindex(@splitchar,@string,@l)

ifltrim(rtrim(@id))=continue

--如果是空字符串就跳过

declare@sqlnvarchar(1000)

set@sql=insertinto+@tablename+(+@fieldname+)values(+@id+)

execsp_executesql@sql

end

go批量插入多行数据2-1示例数据库：NorthWind涉及表：OrdersOrderDetails多行数据批量插入2-2分隔字符串存储过程CutStringcreateprocCutString(@sourcestringvarchar(100)output,@outstringvarchar(10)output)asdeclare@positionintset@position=charindex(',',@sourcestring)if(@position=0)beginset@outstring=@sourcestringset@sourcestring=nullendelsebeginset@outstring=substring(@sourcestring,1,@position-1)set@sourcestring=substring(@sourcestring,@position+1,(len(@sourcestring)-@position))end多行数据批量插入2-3CreateprocSubmitOrder(@customeridvarchar(5),@employeeidint,@productidlistvarchar(100),@unitpricelistvarchar(100),@quantitylistvarchar(100),@discountlistvarchar(100))assetxact_aborton--开启自动事务会滚，出任何错误都自动回滚

declare@orderidintdeclare@productidvarchar(20)declare@unitpricevarchar(20)declare@quantityvarchar(20)declare@discountvarchar(20)begintransaction--使用事务来执行

insertintoOrders(CustomerID,EmployeeID)values(@customerid,@employeeid)--selecttop1OrderIDfromOrdersorderbyOrderIDdescselect@orderid=@@identity--@@identity取得刚刚完成插入的全局自动增长列的值

while(@productidlistisnotnull)beginexecCutString@productidlistoutput,@productidoutputexecCutString@unitpricelistoutput,@unitpriceoutputexecCutString@quantitylistoutput,@quantityoutputexecCutString@discountlistoutput,@discountoutputinsertinto[OrderDetails]values(@orderid,convert(int,@productid),convert(money,@unit