NOVOTS KMS 词汇表 Glossary    联系我们 Contact Us
查询 Search  
   
按类别浏览 Browse by Category
NOVOTS KMS .: 数据库 .: 触发器与存储过程

触发器与存储过程

SQL SERVER编程逻辑主要指在触发器与存储过程中进行编程的方法,SQL SERVER提供了流程控制语句用以实现程序的流程,还提供了游标用来处理多行记录。

游标的使用

使用游标(CURSOR)在需要一行一行处理时,游标十分有用。游标可以打开一个结果集合(按照指定的标准选择的行),并提供在结果集中一行一行处理的功能。基于游标的类型,可以对其进行回滚或者前进。

游标的声明             

DECLARE语句对游标进行声明,有两种方法可以指定一个游标。

SQL-92 语法

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

FOR select_statement

[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

Transact-SQL 扩展语法 

DECLARE cursor_name CURSOR

[ LOCAL | GLOBAL ]

[ FORWARD_ONLY | SCROLL ]

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

[ TYPE_WARNING ]

FOR select_statement

[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

例程5.8:定义一个游标,用于生成公司信息表中的所有记录。

DECLARE Company_cursor CURSOR

    FOR SELECT * FROM Company

打开游标           

打开游标就是创建结果集,执行游标定义时指定的 Transact-SQL 语句填充游标。语法如下:

OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }

例程5.9:定义一个游标,用于显示合同号为“101”的合同明细情况,并打开游标。

DECLARE Contract_Cursor CURSOR FOR

SELECT *

FROM ContractDetail

WHERE ContractID=’101’

OPEN Contract_Cursor

使用游标读取数据                

在从游标中读取数据的过程中,可以在结果集中的每一行上来回移动和处理。如果游标定义成了可滚动的(在声明时使用SCROLL关键字),则任何时候都可取出结果集中的任意行。对于非滚动的游标,只能对当前行的下一行实施取操作。结果集可以取到局部变量中。Fetch命令的语法如下:

FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]

FROM [GLOBAL] cursor_name} | cursor_variable_name}

[INTO @variable_name ][,……n]]

例程5.10:定义一个游标,返回公司信息表中所有的数据,打开游标,然后遍历公司信息表,直到找到公司名称为北京怡神工贸公司的记录为止,并且打印公司代码、公司名称。

DECLARE Company_cursor CURSOR

FOR SELECT CompanyID,CompanyName FROM Company

              DECLARE @ID char(10), @Name char(30)

              OPEN Company_cursor

              FETCH NEXT FROM Company_cursor INTO @ID, @Name

              WHILE @@fetch_status = 0

BEGIN

IF @Name = '北京怡神工贸有限公司'

BEGIN

                     PRINT "找到北京怡神工贸公司"

                     PRINT @ID+@Name

                     BREAK

END

FETCH NEXT FROM Company_cursor INTO @ID, @Name

END

存储过程的创建与管理

存储过程是一段在服务器上执行的程序,它在服务器端对数据库记录进行处理,再把结果返回到客户端。通过使用存储过程,一方面可以利用服务器强大的计算能力和速度,另一方面避免把大量的数据从服务器下载到客户端,减少网络上传输量,服务器只需将计算结果传给客户端,因此可以提高客户端的工作效率。存储过程包括系统存储过程和用户存储过程,系统存储过程中又分为一般系统存储过程和扩展存储过程。

存储过程的主要优点是可以提供对数据的轻松访问,客户端不需要知道复杂的数据结构或业务逻辑过程,只需要把查询指令通过存储过程发给服务器,服务器就能把需要的数据返回给用户

触发器是一种特殊的存储过程,常常用于实现强制业务规则和数据完整性。触发器由SQL Server自动执行,不能由应用程序调用,这是它与存储过程不同的地方,便于保护数据库的完整性和完全性。

触发器在对表进行操作时(UPDATEINSERT DELETE)激活。

使用触发器有如下优点:

触发器是自动的:它们在对表的数据作了任何修改(比如手工输入或者应用程序采取的操作)之后立即被激活。

触发器可以通过数据库中的相关表进行层叠更改。这比直接把代码写在前台的做法更安全合理。

触发器可以强制限制,这些限制比用 CHECK 约束所定义的更复杂。与 CHECK 约束不同的是,触发器可以引用其它表中的列。

触发器用CREATE TRIGGER语句创建。语法如下:

CREATE TRIGGER trigger_name

ON { table | view }

[ WITH ENCRYPTION ]

{

    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }

        [ WITH APPEND ]

        [ NOT FOR REPLICATION ]

        AS

        [ { IF UPDATE ( column )

            [ { AND | OR } UPDATE ( column ) ]

                [ ...n ]

        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )

                { comparison_operator } column_bitmask [ ...n ]

        } ]

        sql_statement [ ...n ]

    }

}

Microsoft 不支持在系统表上添加用户定义触发器。

使用触发器和存储过程必须注意的地方

触发器和存储过程为开发数据库应用带来了很大的方便,但是在使用他们时一定要注意一些限制,否则会带来负面的影响,为应用程序带来不可预见的错误。

触发器和存储过程为开发数据库应用带来了很大的方便,但是在使用他们时一定要注意一些限制,否则会带来负面的影响,为应用程序带来不可预见的错误。

重新编译存储过程和触发器 

存储过程和触发器所用的查询只在编译时进行优化。对数据库进行了索引其它会影响数据库统计的更改后,已编译的存储过程和触发器可能会失去效率。通过对作用于表上的存储过程和触发器进行重新编译,可以重新优化查询。

使用存储过程的注意事项 

使用存储过程,需要注意以下几点:
用户定义的存储过程只能在当前数据库中创建(临时过程除外,临时过程总是在 tempdb 中创建)

SQL Server 允许创建的存储过程引用尚不存在的对象。在创建时,只进行语法检查。

SQL Server 支持两种临时过程:局部临时过程和全局临时过程。局部临时过程只能由创建该过程的连接使用。全局临时过程则可由所有连接使用。

除了 SET SHOWPLAN_TEXT SET SHOWPLAN_ALL 之外(这两个语句必须是批处理中仅有的语句),任何 SET 语句均可以在存储过程内部指定。

触发器的T_SQL限制 

       使用触发器时,请注意有如下限制:

CREATE TRIGGER 必须是批处理中的第一条语句,并且只能应用到一个表中。

发器只能在当前的数据库中创建,不过触发器可以引用当前数据库的外部对象。

如果指定触发器所有者名称以限定触发器,请以相同的方式限定表名。

在同一条 CREATE TRIGGER 语句中,可以为多种用户操作(如 INSERT UPDATE)定义相同的触发器操作。

如果一个表的外键在 DELETE/UPDATE 操作上定义了级联,则不能在该表上定义 INSTEAD OF DELETE/UPDATE 触发器。

在触发器内可以指定任意的 SET 语句。所选择的 SET 选项在触发器执行期间有效,并在触发器执行完后恢复到以前的设置。

与使用存储过程一样,当触发器激发时,将向调用应用程序返回结果。若要避免由于触发器激发而向应用程序返回结果,请不要包含返回结果的 SELECT 语句,也不要包含在触发器中进行变量赋值的语句。包含向用户返回结果的 SELECT 语句或进行变量赋值的语句的触发器需要特殊处理;这些返回的结果必须写入允许修改触发器表的每个应用程序中。如果必须在触发器中进行变量赋值,则应该在触发器的开头使用 SET NOCOUNT 语句以避免返回任何结果集。

DELETE 触发器不能捕获 TRUNCATE TABLE 语句。尽管 TRUNCATE TABLE 语句实际上是没有 WHERE 子句的 DELETE(它删除所有行),但它是无日志记录的,因而不能执行触发器。因为 TRUNCATE TABLE 语句的权限默认授予表所有者且不可转让,所以只有表所有者才需要考虑无意中用 TRUNCATE TABLE 语句规避 DELETE 触发器的问题。

无论有日志记录还是无日志记录,WRITETEXT 语句都不激活触发器。

触发器中不允许以下 Transact-SQL 语句:ALTER DATABASE CREATE DATABASE DISK INIT DISK RESIZE DROP DATABASE LOAD DATABASE LOAD LOG RECONFIGURE RESTORE DATABASE RESTORE LOG


这篇文章对你多有用?

用户评语

添加评语
当前还没有评语.


.: .: .: .: .:
[ 登陆 ]
北京护航科技有限公司 2006

Novots Technologies Limited