100165617.htm
来自「C#高级编程(第三版),顶死你们。。 。up」· HTM 代码 · 共 127 行 · 第 1/2 页
HTM
127 行
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">当把该过程作为批处理命令来执行时</span><span style="FONT-FAMILY: 宋体">，</span><span style="FONT-FAMILY: 宋体">会把一个新行插入到</span><span lang="EN-US">Category</span><span style="FONT-FAMILY: 宋体">表中</span><span style="FONT-FAMILY: 宋体">，</span><span style="FONT-FAMILY: 宋体">返回新记录的标识</span><span lang="EN-US">(identity)</span><span style="FONT-FAMILY: 宋体">值</span><span style="FONT-FAMILY: 宋体">，</span><span style="FONT-FAMILY: 宋体">然后把该列显示给用户。</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">假定过了几个月后，有人要添加一个简单的审计跟踪，记录所有插入的记录，以及对</span><span lang="EN-US">Category</span><span style="FONT-FAMILY: 宋体">名的修改。定义如图</span><span lang="EN-US">21-10</span><span style="FONT-FAMILY: 宋体">所示的一个表，记录</span><span lang="EN-US">Category</span><span style="FONT-FAMILY: 宋体">的新名和旧名。</span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p align="center"><span lang="EN-US"><img height="84" src="21/image010.jpg" width="402" alt="" /></span></p>
<p style="FTEL: 8.15pt" align="center"><span style="FONT-FAMILY: 宋体">图</span><span lang="EN-US"> 21-10</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">这个表的脚本在</span><span lang="EN-US">StoredProcs.sql</span><span style="FONT-FAMILY: 宋体">文件中。</span><span lang="EN-US">AuditID</span><span style="FONT-FAMILY: 宋体">列定义为一个</span><span lang="EN-US">IDENTITY</span><span style="FONT-FAMILY: 宋体">列，然后构造两个数据库触发器，记录对</span><span lang="EN-US">CategoryName</span><span style="FONT-FAMILY: 宋体">字段的修改：</span></p>
<p class="2" style="MARGIN-TOP: 8.15pt; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-GB">CREATE TRIGGER CategoryInsertTrigger</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-GB"> ON Categories</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-GB"> AFTER UPDATE</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-GB">AS</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-GB"> INSERT INTO CategoryAudit(CategoryID , OldName , NewName )</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-GB"> SELECT old.CategoryID, old.CategoryName, new.CategoryName</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-GB"> FROM Deleted AS old,</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-GB"> Categories AS new</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-GB"> WHERE old.CategoryID = new.CategoryID;</span></p>
<p class="2" style="MARGIN-TOP: 0cm; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-GB">GO</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">对于习惯使用</span><span lang="EN-US">Oracle</span><span style="FONT-FAMILY: 宋体">存储过程的用户来说</span><span style="FONT-FAMILY: 宋体">，</span><span lang="EN-US">SQL Server</span><span style="FONT-FAMILY: 宋体">其实没有</span><span lang="EN-US">OLD </span><span style="FONT-FAMILY: 宋体">和</span><span lang="EN-US"> NEW</span><span style="FONT-FAMILY: 宋体">行的概念</span><span style="FONT-FAMILY: 宋体">，</span><span style="FONT-FAMILY: 宋体">而是使用一个插入触发器</span><span style="FONT-FAMILY: 宋体">，</span><span style="FONT-FAMILY: 宋体">在内存中有一个</span><span lang="EN-US">Inserted</span><span style="FONT-FAMILY: 宋体">表可用于插入记录</span><span style="FONT-FAMILY: 宋体">，</span><span style="FONT-FAMILY: 宋体">在</span><span lang="EN-US">Deleted</span><span style="FONT-FAMILY: 宋体">表中删除和更新旧记录。</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">该触发器获取要处理的记录的</span><span lang="EN-US">CategoryID</span><span style="FONT-FAMILY: 宋体">，把它与</span><span lang="EN-US">CategoryName</span><span style="FONT-FAMILY: 宋体">列的新旧值一起存储在起来。</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">现在，调用原来的存储过程插入一个新</span><span lang="EN-US">CategoryID</span><span style="FONT-FAMILY: 宋体">时，会得到一个</span><span lang="EN-US">identity</span><span style="FONT-FAMILY: 宋体">值，但它不再是插入到</span><span lang="EN-US">Categories</span><span style="FONT-FAMILY: 宋体">表中的行的</span><span lang="EN-US">identity</span><span style="FONT-FAMILY: 宋体">值，而是</span><span lang="EN-US">CategoryAudit</span><span style="FONT-FAMILY: 宋体">表中为该行生成的新值。</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">要查看这些值，可打开</span><span lang="EN-US">SQL Server Enterprise</span><span style="FONT-FAMILY: 宋体">管理器的一个副本，查看</span><span lang="EN-US">Categories</span><span style="FONT-FAMILY: 宋体">表的内容，如图</span><span lang="EN-US">21-11</span><span style="FONT-FAMILY: 宋体">所示。</span></p>
<p align="center"><span lang="EN-US" style="FONT-SIZE: 7pt"><img height="93" src="21/image011.jpg" width="421" alt="" /></span></p>
<p style="FTEL: 8.15pt" align="center"><span style="FONT-FAMILY: 宋体">图</span><span lang="EN-US"> 21-11</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">这个图列出了</span><span lang="EN-US">Northwind</span><span style="FONT-FAMILY: 宋体">数据库中所有的类别。</span></p>
<p class="MsoNormal"><span lang="EN-US">Categories</span><span style="FONT-FAMILY: 宋体">表中下一个</span><span lang="EN-US">identity</span><span style="FONT-FAMILY: 宋体">值是</span><span lang="EN-US">9</span><span style="FONT-FAMILY: 宋体">，所以执行上面的代码插入一个新行，看看返回了什么</span><span lang="EN-US">ID</span><span style="FONT-FAMILY: 宋体">，如下所示。</span></p>
<p class="2" style="MARGIN-TOP: 8.15pt; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-GB">DECLARE @CatID int;</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-GB">EXECUTE CategoryInsert 'Pasties' , 'Heaven Sent Food' , @CatID OUTPUT;</span></p>
<p class="2" style="MARGIN-TOP: 0cm; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-GB">PRINT @CatID;</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">其输出值是</span><span lang="EN-US">1</span><span style="FONT-FAMILY: 宋体">。如果查看</span><span lang="EN-US">CategoryAudit</span><span style="FONT-FAMILY: 宋体">表，则这是新插入的审核记录的</span><span lang="EN-US">identity</span><span style="FONT-FAMILY: 宋体">值，不是新建</span><span lang="EN-US">category</span><span style="FONT-FAMILY: 宋体">记录的值，如图</span><span lang="EN-US">21-12</span><span style="FONT-FAMILY: 宋体">所示。</span></p>
<p align="center"><span lang="EN-US"><img height="31" src="21/image012.jpg" width="276" alt="" /></span></p>
<p style="FTEL: 8.15pt" align="center"><span style="FONT-FAMILY: 宋体">图</span><span lang="EN-US"> 21-12</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">问题是</span><span lang="EN-US" style="FONT-SIZE: 9pt">@@</span><span lang="EN-US">IDENTITY</span><span style="FONT-FAMILY: 宋体">仍在起作用，它返回由会话创建的最后一个</span><span lang="EN-US">identity</span><span style="FONT-FAMILY: 宋体">值，所以不是</span><span lang="EN-US">100%</span><span style="FONT-FAMILY: 宋体">的可靠。</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">除了</span><span lang="EN-US" style="FONT-SIZE: 9pt">@@</span><span lang="EN-US">IDENTITY</span><span style="FONT-FAMILY: 宋体">外，还可以使用另外两个</span><span lang="EN-US">identity</span><span style="FONT-FAMILY: 宋体">函数，它们都不会出任何问题。第一个函数是</span><span lang="EN-US">SCOPE</span><span lang="EN-US" style="FONT-FAMILY: 'Baskerville BE Regular'">_</span><span lang="EN-US">IDENTITY()</span><span style="FONT-FAMILY: 宋体">，它返回在当前“范围”内最后一个创建的</span><span lang="EN-US">identity</span><span style="FONT-FAMILY: 宋体">值。</span><span lang="EN-US">SQL Server</span><span style="FONT-FAMILY: 宋体">把该范围定义为一个存储过程、触发器或函数。在大多数情况下，这个函数可以正常工作。但如果因某种原因，有人在存储过程中添加了另一个</span><span lang="EN-US">INSERT</span><span style="FONT-FAMILY: 宋体">语句，就会得到这个值，而不是意想不到的值。</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">另一个函数是</span><span lang="EN-US">IDENT</span><span lang="EN-US" style="FONT-FAMILY: 'Baskerville BE Regular'">_</span><span lang="EN-US">CURRENT()</span><span style="FONT-FAMILY: 宋体">，它返回任何范围中为给定表生成的最后一个</span><span lang="EN-US">identity</span><span style="FONT-FAMILY: 宋体">值。例如，如果两个用户同时访问</span><span lang="EN-US">SQL Server</span><span style="FONT-FAMILY: 宋体">，其中一个用户就有可能得到另一个用户生成的</span><span lang="EN-US">identity</span><span style="FONT-FAMILY: 宋体">值。</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">可以想像，跟踪这个问题的原因是不太容易的。在</span><span lang="EN-US">SQL Server</span><span style="FONT-FAMILY: 宋体">中使用</span><span lang="EN-US">Identity</span><span style="FONT-FAMILY: 宋体">列时要多加小心。</span></p></div>
<!-- page -->
<div class="page" style="text-align: center">
<a href="100165616.htm">上一页</a> <a href="index.html">首页</a> <a href="100165618.htm">下一页</a>
</div>
<div style="margin: 0px auto; width: 700px; border: solid 1px #0b5f98;">
<div style="float: left; width: 16px; background-color: #0b5f98; color: White; padding: 1px;">
图书导读
</div>
<div style="float: right; width: 670px; text-align: left; line-height: 16pt; padding-left: 2px">
<!--导读-->
<h1 id="divCurrentNode2" style="color: #b83507; width: 100%; text-align: left; font-size: 12px; padding-left: 2px">当前章节:<a href='100165617.htm'><font color='red'>21.9.2 SQL Server的键生成</font></a></h1>
<div id="divRealteNod2" style="padding-left: 2px">
<div style='float:left;width:49%'>·<a href='100165614.htm'>21.8.2 写入XML输出结果</a></div><div style='float:right;width:49%'>·<a href='100165615.htm'>21.9 使用ADO.NET</a></div><div style='float:left;width:49%'>·<a href='100165616.htm'>21.9.1 分层开发</a></div><div style='float:right;width:49%'>·<a href='100165618.htm'>21.9.3 命名约定</a></div><div style='float:left;width:49%'>·<a href='100165619.htm'>21.10 小结</a></div><div style='float:right;width:49%'>·<a href='100165620.htm'>22.1 DataGrid控件</a></div></div>
</div>
</div>
</div>
<!-- 评论 -->
<!-- 今日推荐 -->
</div>
<!-- 页脚 -->
<div id="foot">
<img src="../../book/readbook.aspx@node=5617&bookid=16&bookname=21.9.2++SQL+Server_25b5_25c4_25bc_25fc_25c9_25fa_25b3_25c9" alt="" width="0" height="0" />
<style>
</div>
</body>
</html>
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?