存储过程及触发器在SQL Server数据库开发中的应用

摘要：SQL Server数据库是当今有代表性的大型网络数据库之一，存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，可以作为一个单元被用户的应用程序直接调用。触发器是一种特殊类型的存储过程，主要是通过事件进行触发自动执行。笔者在开发了大宗商品现货电子撮合交易系统的基础上，总结了存储过程和触发器的基本概念及具体应用，展现了其在数据库开发中强大功能和使用特点。

关键词：SQL Server；存储过程；触发器

中图分类号：TP311.138 文献标识码：A文章编号：1007-9599(2012)01-0000-02

Application of Trigger and Stored Procedure in SQL Server Database Development

Yang Guixia

(China Cocoon & Silk Exchange,Jiaxing314001,China)

Abstract:SQL Server database is one of today's representative of a large network database stored procedure (Stored Procedure) is a set of SQL statements in order to accomplish a specific function set,stored in the database is compiled as a unit by the user invoked directly by applications.The trigger is a special type of stored procedure,trigger automated event.On the basis of developed commodity spot electronic matching trading system,the author summarizes the basic concepts and specific applications of stored procedures and triggers,and demonstrated its powerful database development and use characteristics.

Keywords:SQL Server;Stored procedures;Trigger

一、概述

Microsoft SQLServe作为一个可扩展的、高性能的、为分布式客户机/服务器计算所设计的数据库管理系统，性能可靠，数据管理能力强，实现了与Windows系统的有机结合，是目前应用最为广泛的关系型数据库系统之一。笔者开发的大宗商品现货电子撮合交易系统以C++ Builder为开发语言，SQL Server作为数据库服务器，采用客户机／服务器结构模式，这种运行环境通常是把表达逻辑层放在客户端，数据逻辑层放在服务器端，而业务逻辑层不是独立存在的，根据需要放在客户端或服务器端，从而均衡地将任务分配在服务器端与客户端（如下图），极大地减少了网络流量，具有良好的系统开放性和可扩展性，存储过程和触发器技术正是在基于这种数据库处理方式的网络数据库中发挥了巨大的优越性。

二、存储过程和触发器的概念及特点

（一）存储过程是为了实现特定任务而将一些需要多次调用的固定的操作编写成子程序并集中以一个存储单元的形式存储在服务器上，可由应用程序调用执行，其参数可被传递和返回。创建存储过程语法如下：

Create procedure procedure_name[;number]

[{@parameter data_type}

[varying][=default][output]

[,…n]

[with {reconpile |encryption | recompile,encryption}]

[for replication]

As sql_statement[,….n]

（二）存储过程的优点

1.由于被提前编译 且使用高速缓存，减少了服务器和客户端之间的数据交换，故加快了执行速度，提高了运行效率。2.集中存放经常用到的操作，简化客户端编程，有效利用现有资源，提高开发效率。3.存储过程的文件保存在数据库中，使用时只需向服务器发送请求执行该存储过程的指令，服务器自动执行存储过程中的所有SQL语句，这样使得客户端程序向服务器发送的指令大为减少，大大减少了网络数据的传输量，减少了网络开销。4.当服务器端数据发生变化时，只需简单地修改存储过程的SQL语句，而不必更改应用程序。5.使用存储过程可以将计算在服务器和客户机上适当分布，提高了系统的响应速度，改善了系统的运行性能。

（三）触发器是建立在表上的特殊的存储过程，当对该表进行插入、修改、删除操作时后，将自动执行相应的insert、update、delete触发器。触发器和存储过程不同，存储过程必须由用户、应用程序或者触发器来显式地调用并执行，而触发器是当特定事件出现的时候自动执行或者激活，与连接到数据库中的用户或者应用程序无关。创建触发器语法如下：

CREATE TRIGGER trigger_name

ON{ table | view }

{ {

{ FOR | AFTER | INSTEAD OF }

{ [ delete ] [,] [ insert ] [,] [ update ] }

AS

Sql_statement

} }

（四）触发器的优点及使用限制

1.能够实现比CHECK 语句更为复杂的约束。

2.可以侦测数据库内的操作，从而不允许数据库中未经许可的指定更新和变化，并自动地级联影响整个数据库的各项内容。

3.可以调用一个或多个存储过程。

4.触发器的缺点是不能传递和返回参数、使用性能较低且调试难度较大。

三、存储过程和触发器的应用实例介绍

（一）存储过程的使用

笔者开发的大宗商品现货电子撮合交易系统运行时需要进行大量的查询、计算、统计等数据处理，如成交时要及时计算交易商的成交保证金、转让合同时要及时计算交易商的转让价差及应退的保证金、行情发生变动时要及时计算每笔已订购合同的浮动价差等，由于在交易过程中要求很强的实时性和数据处理效率，采用SQL语句来实现上述功能需要多次与服务器交互，增加了较多的网络流量，显然效率较低，不能满易的要求，为此凡需要较多数据处理的业务功能，笔者均采用存储过程，较好地满足了实时性和效率的要求，且易于维护，运行效果良好。如交易指令输入时，需要查询资金表tr_fund判断该交易商资金余额是否足够，若够，则交易指令输入成功，将该交易指令插入指令表tr_instruction，返回成功标志，否则，输入不成功，返回失败标志。存储过程定义如下：

CREATE PROCEDURE [dbo].[ instruction_insert]

(

@trader_no char(4), --交易商号

@goods_no char(12),--品种号

@tr_date datetime(),--交易日期

@buyint, --买进手数

@sell int,--卖出手数

@price numeric(18,2), --价格

@is_success char(1)output--是否成功（输出参数）

）

AS

begin tran

declare @rest_fund numeric(18,2)

select @rest_fund=rest_fund from tr_fund where trader_no=@trader_no --查询资金余额

if @rest_fund>=(@buy+@sell)*@price*0.2

begin

--余额足够

insert tr_instruction (trader_no, goods_no, tr_date, buy, sell, price ) values(@trader_no, @goods_no, @tr_date, @buy, @sell, @price)

select @is_success= 0

Else

--余额不足

select @is_success= 1

end

commit tran

（二）触发器的使用

触发器能保证数据的完整性和一致性，它可以方便地基于一个表的修改，自动更新其他相关表的记录，以保证数据的完整性。以交易商进场为例，它包含对数据库的两项操作，即添加交易商信息表，添加交易商资金信息表。如果不使用触发器，则编写程序时需要依次访问两个表，如果使用触发器，则在添加交易商信息表时，SQL server服务器可以自动添加交易商资金信息表，要实现满足上述要求的触发器，用以下语句实现：

CREATE TRIGGER [trader_ins] ON [dbo].[tr_trader]

FOR insert

AS

insert tr_fund (trader_no, rest_fund) select trader_no,0 from inserted

return

使用该触发器，既减少了编程量，又维护了数据的完整性，还减少了网络上信息的流量。

四、总结

综上所述，我们可以体会到存储过程和触发器技术在数据库开发中的强大功能，在实际开发应用中，如果能熟练应用这两种技术并将其与数据库视图、函数等有机结合起来灵活运用，将能极大地提高计算机系统的运行效率，达到令人满意的效果。

[作者简介]

杨桂霞（1970-），女，浙江嘉兴人，职称：工程师，学历：本科，主要研究方向：计算机软件。