触发器存储过程数据库安全Word文件下载.docx
《触发器存储过程数据库安全Word文件下载.docx》由会员分享，可在线阅读，更多相关《触发器存储过程数据库安全Word文件下载.docx（17页珍藏版）》请在冰点文库上搜索。
下面举例说明触发器的实现步骤。
为减少篇幅，对表结构作了简化处理。
以下操作在Microsoft
SQL
SERVER环境中完成。
（1）建立教材表JCB，并定义主键
CREATE
TABLE
［dbo］.［JCB］
（
［教材代码］
［char］
（10）
NOT
NULL
［教材名］
（30）
［价格］
［decimal］（18,
2）
［库存量］
［int］
［出版社］
（20）
）
ON
［PRIMARY］ALTER
WITH
NOCHECK
ADD
CONSTRAINT
［PK－JCB］
PRIMARY
KEY
CLUSTERED
（［教材代码］
［PRIMARY］
（2）建立教材进出明细表MXB，并定义主键和外?
br>
［dbo］.［MXB］
［日期］
［datetime］
［教材进］
［教材出］
［备注］
（40）
［PRIMARY］
ALTER
［PK－MXB］
（［教材代码］,
［日期］
［FK－MXB－JCB］
FOREIGN
（［教材代码］）
REFERENCES
［教材代码］）
DELETE
CASCADE
图1显示了JCB和MXB两个表的关系。
（3）在MXB上建立INSERT触发器
TRIGGER
MXB－INSE
FOR
INSERT
ASUPDATE
JCB
SET
库存量=库存量+
（SELECT
教材进-教材出
FROM
INSERTED）
JCB,INSERTEDWHERE
JCB.教材代码=INSERTED.教材代码
（4）在MXB上建立DELETE触发器
MXB－DELE
AS
库存量=库存量-
DELETED）
JCB,DELETEDWHERE
JCB.教材代码=DELETED.教材代码〖HT〗
（5）在MXB上建立UPDATE触发器
MXB－UPDA
BEGINUPDATE
JCB,DELETED
WHERE
JCB.教材代码=DELETED.教材代码
END
通过以上步骤，设置了MXB表的3类触发器，当用户对MXB表进行插入、删除和修改时，将根据MXB中教材进出的情况动态地修改JCB中对应教材的库存量。
由于在触发器中，涉及到的inserted表和deleted表均存放在内存中，因此，触发器的执行速度较快。
3设计触发器的考虑
在写触发器代码时需要考虑的一个重要问题就是，引发触发器的语句可以是一个影响单行的语句，也可以是一个影响多行的语句。
这在
和DELETE
触发器中很常见，因为这些语句经常作用于多行。
而这在
触发器中就比较少见，因为基本的
语句只添加一行。
然而，由于
触发器可由
INTO
（table_name）
SELECT
语句激发，所以，插入许多行可能导致单个的触发器调用。
上面讨论的涉及MXB的3类触发器都是针对影响单行的语句。
因此，有必要考虑影响多行的语句，这里对MXB的INSERT触发器进行讨论。
（1）可处理多行的MXB上的INSERT触发器
如果要进行多行插入，上面示例中的触发器可能就不能正确处理，因为
语句赋值表达式右边的表达式只能是一个值，而不能是一个值列表。
因此，该触发器的作用就是获取
表中任意一行的值，并将其添加到JCB表中特定教材代码值的已有库存量值上。
如果某个教材代码值在inserted
表中出现了多次，则可能无法得到预期的结果。
为了正确地更新JCB表，触发器就必须适应inserted表中出现多行的可能性。
这可以通过
SUM
函数实现，它为
表中每个教材代码计算教材进出的总计。
函数存放于相关子查询中（SELECT
语句在括号内）。
该子查询为
表中与JCB表的教材代码匹配或相关的每个教材代码返回一个单一值。
SUM（教材进-教材出）
INSERTED
JCB.教材代码=INSERTED.教材代码）
JCB.教材代码
IN
教材代码
该触发器对单行插入同样适用，不过，使用该触发器时，WHERE
子句中所使用的相关子查询和
运算符需要额外处理，而这对于单行插入来说是不必要的。
（2）可区分单行和多行插入的MXB上的INSERT触发器可以通过系统函数@@ROWCOUNT以区分单行插入和多行插入，以使触发器针对不同行数使用最优方法。
MXB_INSE
IF
@@ROWCOUNT=1
BEGIN
JCB,INSERTED
ELSE
二、结论
触发器应用于支持企业级商业解决方案时，是一个功能十分强大的工具。
它可以用于实现业务规则，可以检查事务，可以在同一表上创建多个触发器来分离代码的功能。
另外，通过使用触发器收集的信息，可以提高数据库的性能，可以用来维护那些使用外键所不能实现的复杂参数完整性。
因此，对于维护数据表之间一致性，保持数据的相关完整性的情况，触发器应作为首先考虑的技术。
储过程中返回结果
从存储过程中返回结果有三种方式：
1、
返回结果集
这是客户端应用程序返回结果的最通用的方法。
结果集是通过使用SELECT语句选择数据产生的。
结果集可以从永久表、临时表或局部变量中产生。
将结果返回到另一个存储过程不是一种有效的方法。
存储过程不能访问另一个存储过程建立的结果集。
例如从永久表中返回结果集：
USE
pubs
GO
PROCEDURE
ap_CreateResultFromPermtable
au_iname
authors
例如从局部变量中创建结果集：
ap_CreateResultFromVariable
DECLARE
@au_iname
char（20）
=
au_id
‘172-32-1176’
@au_id
2、
设置OUTPUT参数的值
输出参数经常用来从存储过程中检索出结果。
如果某个参数在传输到存储过程中时被定义成OUTPUT，则对该参数的任何修改在退出存储之后仍然有效。
例如：
ap_SetOutputVar
@count
integer
OUTPUT
count（*）
从输出参数中检索出值：
ap_GetOutputVar
@num
EXECUTE
PRINT
“the
count
is”+convert（char,@num）
•
将游标使用成OUTPUT参数。
游标可以使用OUTPUT（输出）参数，但不能使用成输入参数。
也就是说，游标可以作为结果返回，但却不能传输到过程中去。
当游标被用作参数时，需要限定其为OUTPUT和VARYING。
VARYING关键字指出该结果集要用来支持输出参数。
这样就提供了将结果集返回到调用过程的能力。
GetTitleCount
@count_cursor
CURSOR
VARYING
au_id,count（*）
titleauthors
GROUP
BY
OPEN
3、
通过RETURN参数返回状态
这是一种从存储过程返回错误码的方法。
存储过程总是返回一个状态值，用户也可以使用RETURN语句返回自己的状态。
ap_SetReturnStatus
0
RETURN
（1）
RETURN
（0）
例如检索出返回的状态：
ap_GetReturnStatus
@status
1
“No
rows
found”
“successful”
在存储过程中进行错误处理
如同其它程序一样，在存储过程中进行错误处理是非常重要的。
系统变更@@error在执行每一个Transact
SQL语句之后都会得到一个值。
对于成功的执行，@@error的值为0，如果出现错误，则@@error中将包含错误信息。
@@error系统变量对存储过程的错误处理是非常重要的。
注意：
为了防止错误，@@error所能设置的值在sysmessages表的“error”中反映了出来。
在存储过程中的错误有两种类型：
数据库相关的错误
这些错误是由数据库的不一致性引起的，系统使用非0的@@error值表示特定的数据库问题。
在Transact
SQL执行之后，可以通过@@error获得所出现的错误。
如果发现@@error不为0，则必须采取必要的行动，大多数情况下，存储将不再继续进行处理而返回。
下面的示例展示了典型的获取数据库错误的方法。
该过程将错误代码放置到输出变量中，这样，调用程序就能够访问到。
ap_TrapDatabaseError
@return_code
“Jackson”
“Smith”
@@error
<
>
业务逻辑错误
这些错误是由于违反了业务规则而引起的。
要获取这些错误，首先需要定义业务规则，基于这些规则，需要在存储过程中增加必要的错误检测代码。
人们经常使用RAISERROR语句通报这些错误。
RAISERROR提供了返回用户定义错误及将@@error变量设置成用户定义错误号的能力。
错误消息可以被动态地建立，或者基于错误号从“sysmessages”表中检索到。
一旦出现了错误，错误就会以一种服务器错误消息的方式返回到客户机。
下面是RAISERROR命令的语法：
RAISERROR
（msg_id
|
msg_str,
severity,
state
[,
argument
][,…n]））
[WITH
options]
Msg_id指明用户定义消息的id，该消息存储在“sysmessages”系统表中。
Msg_str用于动态创建消息的消息字符串。
这与C语言中的“printf”非常相似。
Severity定义用户赋值的错误消息严重程度。
State是从1到127的任意整数值，它表示错误的调用状态信息。
负数的state值将缺省为1。
OPTIONS指明错误的定制选项。
OPTIONS的有效值如下：
1）
LOG。
将错误记录到服务器错误日志和NT事件日志中。
该选项需要消息带有从19到25的严重程度。
而只有系统管理员才能发出这种消息。
NOWAIT。
将消息立即发送到客户端服务器。
3）
SETERROR。
不管其严重级别如何，将@@error的值设置为msg_id或5000。
远程过程调用
Server提供了调用驻留在不同服务器上的存储过程的能力。
调用这样的存储过程称谓远程存储过程调用。
为了使得调用能从一个SQL
Server转移到另一个服务器，两个服务器应该相互定义成对方的有效远程服务器。
设置远程服务器的配置：
扩展某个服务器的组。
右击该服务器并点击“Properties”。
设置选项“Allow
other
Servers
to
connect
remotely
this
server
via
RPC”。
设置“Query
time
out”选项的值，该值指定从一个查询处理返回所能等待的秒数。
缺省值为0，表示允许无限的等待时间。
设置完成配置选项之后，点击“OK”。
重新启动服务器之后，修改将会生效。
在另一台远程服务器上重复相同的步骤。
调用远程存储过程需要指明服务器的名称，后带数据库的名称和拥有者的名称。
下面是在不同的服务器（Server2）上调用一个存储过程的示例。
Exec
server2.pubs.dbo.myproc
豆豆的后话：
这里只是粗浅的介绍了SQL
Server常用的知识，对象也是基于SQL
Server数据库编写应用程序的编程人员，而非数据库管理者。
但对于应用程序编程者，了解数据库的管理也是非常有用的。
建议在以后的时间自行去了解数据库的管理，这对于优化程序也是相当有用的。
查看锁信息
create
table
#t（req_spid
int,obj_name
sysname）
declare
@s
nvarchar（4000）
@rid
int,@dbname
sysname,@id
int,@objname
sysname
tb
cursor
for
select
distinct
req_spid,dbname=db_name（rsc_dbid）,rsc_objid
from
master..syslockinfo
where
rsc_type
in（4,5）
open
fetch
next
into
@rid,@dbname,@id
while
@@fetch_status=0
begin
set
@objname=name
['
+@dbname+'
]..sysobjects
id=@id'
exec
sp_executesql
@s,N'
@objname
out,@id
int'
@objname
insert
#t
values（@rid,@objname）
end
close
deallocate
进程id=a.req_spid
数据库=db_name（rsc_dbid）
类型=case
when
then
'
资源（未使用）'
2
数据库'
3
文件'
4
索引'
5
表'
6
页'
7
键'
8
扩展盘区'
9
RID（行
ID）'
10
应用程序'
对象id=rsc_objid
对象名=b.obj_name
rsc_indid
a
left
join
b
on
a.req_spid=b.req_spid
go
drop
Transact-SQL
参考
sysobjects
在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行。
只有在
tempdb
内，每个临时对象才在该表中占一行。
列名
数据类型
描述
name
对象名。
Id
int
对象标识号。
xtype
char
（2）
对象类型。
可以是下列对象类型中的一种：
C
CHECK
约束
D
默认值或
DEFAULT
F
L
日志
FN
标量函数
内嵌表函数
P
存储过程
PK
约束（类型是
K）
RF
复制筛选存储过程
S
系统表
TF
表函数
TR
触发器
U
用户表
UQ
UNIQUE
V
视图
X
扩展存储过程
uid
smallint
所有者对象的用户
ID。
info
保留。
仅限内部使用。
status
base_schema_
ver
replinfo
供复制使用。
parent_obj
父对象的对象标识号（例如，对于触发器或约束，该标识号为表
ID）。
crdate
datetime
对象的创建日期。
ftcatid
为全文索引注册的所有用户表的全文目录标识符，对于没有注册的所有用户表则为
0。
schema_ver
in
升级会员 