标题标题  显示论坛会员列表名单  搜索论坛搜索  HelpHelp
  注册注册  登入登入
ASP教学区
 DoReMe : ASP教学区
主题 话题: 第十二章 高级 SQL 回复发表新主题
作者
贴子内容 << Prev Topic下一个主题 >>
bibi
Senior Member
Senior Member


加入: 2005/1月/07
Online Status: Offline
回复: 103
Posted: 2005/1月/07 9:26上午 | IP记录 引用 bibi

使用存储过程

该部分将向你介绍如何创建和使用你自己的存储过程。在SQL Server中存储过程是和传统的计算机应用程序最相近的事物,并具有如下的优点:

假如你有一套复杂的SQL语句需要在多个Active Server Pages中执行。你可以把他们放入一个存储过程,然后执行该存储过程。这能够减少你Active Server Pages的大小。同时还能确保在每一页上执行的SQL语句都相同。

当你执行一个SQL的批处理时。服务器首先必须编译在批处理中的所有语句。这不但需要时间,还要花费服务器资源。相比较而言,在 存储过程第一次执行后,它就不需要重新编译了。通过使用存储过程,你可以跨过编译这一步,更快地执行SQL语句集合。从一个Ac tive Server页中执行一个存储过程比执行一个SQL语句的集合更有效。

你可以对存储过程输入输出值。这意味著存储过程非常的灵活,相同的存储过程可以根据不同的输入值返回不同的信息。

当你向数据库服务器传递一个SQL语句集合时,必须传递其中的每一个独立语句,而当你执行存储过程时,相反的,仅仅传递一个简单 的语句。通过使用存储过程,你可以减少在网络上的阻塞。

你可以配置表的权限,比如用户只能通过使用存储过程来修改表。这就能增加在你数据库中表的安全性。

你可以在其它的存储过程内部执行你的存储过程。这种策略就允许你在非常小的存储过程上建立非常复杂的存储过程。这也意味著你可以 为许多不同的编程任务使用相同的存储过程。

当你在Active Server页中添加SQL语句时,你必须仔细考虑能否把这些语句放置到存储过程中。上面提到的优点都是实质性的。如下一部分所 示,存储过程是非常容易创建的。

使用CREATE PROCEDURE创建存储过程

你可以使用CREATE PROCEDURE来创建一个存储过程。下面就是一个非常简单的存储过程的一个例子:

CREATE PROCEDURE retrieve_authors AS SELECT * FROM Authors

当你创建存储过程时,你必须给它指定一个名称。在本例子中,存储过程的名称为retrieve_authors。你可以给存储过 程赋予任何你想要的名称,但最好你能够使该名称在一定程度上描述存储过程的功能。

每一个存储过程都包括一个或多个SQL语句。为了指明是存储过程一部分的SQL语句,你只需简单地在关键词AS后面包含它们。在 前面例子中的存储过程只包含一个SQL语句。当该存储过程执行时,它返回在Authors表中所有的记录。

你可以使用EXECUTE语句来执行一个存储过程。比如,为了执行retrieve_authors存储过程,你可以使用如下的 语句:

EXECUTE retrieve_authors

当你执行该存储过程时,所有包括在其中的SQL语句都会执行,在上面的例子中,会返回所有在Authors表中的记录。

当在批处理中的第一个语句是调用存储过程时,你并不需要使用EXECUTE语句。你可以简单地提供存储过程的名称来执行存储过程 。比如在ISQL/W中,可以象下面所示来执行存储过程:

retrieve_authors

这起同样的作用。存储过程会被执行,并会返回结果。然而如果在该存储过程之前还有其它的任何语句,你就会收到错误信息(一般地, 语法错误)。

当你创建和执行一个存储过程时,这仅仅是在某一个数据库的范围内完成。假设你在数据库MyDatabase内创建了存储过程re trieve_authors。如果没有指明过程调用,你就不能在另一个数据库比如MyDatabase2中调用存储过程ret rieve_authors。假如你需要在Mydatabase2中执行存储过程retrieve_authors,你必须使用 如下的语句(注意下面的两个点号):

EXECUTE Mydatabase..retrieve_authors

一旦你已经创建了一个存储过程,你就能使用系统存储过程sp_helptext来观看在该存储过程的的SQL语句。比如,如果你 输入命令sp_helptext retrieve_authors,就会显示下面的结果:

text

……………………………………………

CREATE PROCEDURE retrieve_authors AS SELECT * FROM Authors

注意

你可能感到奇怪的是,sp_helptext系统过程本身就是一种存储过程类型。它是一种系统的存储过程。(系统存储过程存储在 Master数据库中,能够被所有的数据库访问。)为了满足你的好奇心,你可以使用命令sp_helptext sp_helptext来观看组成sp_helptext本身的SQL语句。

你在创建完存储过程后,不能对其进行修改。假如你需要修改一个存储过程。你必须首先破坏它,然后重新构建之。为了破坏一个存储过 程。你可以使用DROP PROCEDURE语句,例如下面的语句删除retrieve_authors存储过程:

DROP PROCEDURE retrieve_authors

注意

你可以使用系统存储过程sp_help来观看在当前数据库中所有存储过程的列表。假如你不加任何修改地执行了sp_help。该 过程会显示在当前数据库中所有的存储过程、触发器和表。假如在sp_help后面跟上指定的存储过程,sp_help会仅仅显示 那个存储过程的信息。

Back to Top 查看 bibi's 资料 搜索其它贴子 bibi 访问 bibi's
 
bibi
Senior Member
Senior Member


加入: 2005/1月/07
Online Status: Offline
回复: 103
Posted: 2005/1月/07 9:26上午 | IP记录 引用 bibi

使用SQL Enterprise Manager 创建存储过程

假如你要创建一个复杂的存储过程,使用SQL Enterprise Manager要比ISQL/w要容易得多,该程序允许你更容易地修改存储过程,它可以自动为你删除和重新创建存储过程。

要使用SQL Enterprise Manage来创建一个存储过程。首先启动该程序,选择你要用于存储表的数据库,然后在菜单上选择Manage|Manage Stored Procedure,这时就会显示如图12。4所示的窗口。

当你第一次打开存储过程管理对话框窗口时,会自动显示文本CREATE PROCEDURE <PROCEDURE NAME>。如果学过上面部分的内容,你就会对该文本非常的熟悉。这是CREATE PROCEDURE语句。替换<PROCEDURE NAME>成你所需要的名称。

在关键词AS后面,你可以输入组成你存储过程的语句。比如,假设你在数据库中有一个名为Philosophers的数据库表,其 中包含了一些哲学家的列表。下面的存储过程在如果在数据库中有Plato存在时,会打印「Plato is a philosopher」;否则它会打印「Plato is not a philosopher」

CREATE PROCEDURE check_philosophers AS

IF EXISTS(SELECT name FROM Philosophers WHERE name=」Plato」)

PRINT 「Plato is a philosopher」

ELSE

PRINT 「Plato is not a philosopher」

当你已经完成输入存储过程后,你可以点击保存对像按钮来保存之(它看起来像一个绿色的三角形)。

图12.4 存储过程管理对话框窗口

 

 

 

 

 

 

 

 

 

 

 

 

为了观看在你第一次保存之后的存储过程的文本,你需要在Procedure下拉菜单中再次选择该存储过程。SQL Server会自动在你的文档中插入新的语句。比如,在保存完check_philosophesr过程之后,会显示如下的文本 :

if exists (select * from sysobjects where id=object_id(『dbo.check_philisophers』) and sysstat & 0xf=4)

drop procedure dbo.check_philosophers

GO

CREATE PROCEDURE check_philosophers AS

IF EXISTS(SELECT name FROM Philosophers WHERE name=」Plato」)

PRINT 「Plato is a philosopher」

ELSE

PRINT 「Plato is not a philosopher」

GO

为什么SQL Server要添加这些语句呢?这些语句是什么用的呢?这些添加的语句并不包含在存储过程中。它们的使用是使存储过程的使用变得 更加容易。

当在过程中的第一个语句执行时,它检查是否有存储过程check_philosophers已经存在于数据库中。假如该过程已经 存在,语句会删除该过程。这样,假如你修改了你的存储过程,然后点击Save Object按钮时,该语句会确保过程在修改后再次创建之前已经被删除了。

注意

在修改你的存储过程时,注意不要删除那些添加的语句。假如说你删除了第一个语句,你就不能正确地保存你的存储过程。相反的,你就 会获得一个错误信息告诉你,存储过程已经存在于数据库中(假如你正处于这种情况下,你只需要用语句DROP PROCEDURE来删除前一版本)。

使用SQL Enterprise Manager而不是ISQL/w来创建你的存储过程的主要优点是使用Enterprise Manage可以在将来很容易地修改存储过程,在已经保存了存储过程之后,你可以使用管理存储过程对话框来选择和修改它。使用I SQL/w在创建了存储过程之后对其进行修改是非常困难的。

Back to Top 查看 bibi's 资料 搜索其它贴子 bibi 访问 bibi's
 
bibi
Senior Member
Senior Member


加入: 2005/1月/07
Online Status: Offline
回复: 103
Posted: 2005/1月/07 9:26上午 | IP记录 引用 bibi

给存储过程传值

当你调用一个存储过程时,你可以使用参数来传值给它,从而使你的存储过程变得非常的灵活。比如,你想修改过程check_phi losophers,使之能够检测是否存在某一个哲学家。你可以使用如下的语句进行修改:

CREATE PROCEDURE check_philosophers

(@philosopher VARCHAR(30))

AS

IF EXISTS(SELECT name FROM Philosophers WHERE name=@philosopher)

PRINT 「A philosopher」

ELSE

PRINT 「Not a philisopher」

当该过程执行时,它检查传递给变量@philosopher的姓名是否存在于表Philosophers中。假如@philos opher的值存在于表中,打印文本「A philosopher」,否则打印文本「Not a philisopher」

当你在存储过程中包含参数时,你把它们列在CREATE PROCEDURE语句的后面的括号内,但必须在关键词AS之前。对于每一个参数,你都必须指明数据类型。在一个单独的过程中, 你最多能设置255个参数。比如下面的过程检查是否在传递的姓名当中,至少有一个是哲学家的姓名:

CREATE PROCEDURE check_philosophers

(@firstname VARCHAR(30),@secondname VARCHAR(30))

AS

IF EXISTS(SELECT name FROM Philosophers

WHERE name=@firstname OR name=@secondname)

PRINT 「At least one of them is a philosopher」

ELSE

PRINT 「Neither one of them is a philisopher」

为了执行一个具有一个或多个参数的存储过程,你只需简单地在存储过程名称的后面列出参数的值。比如下面的语句检查Plato和A ristotle中是否至少有一个是哲学家:

EXECUTE check_philisophers 「Plato」,」Aristotle」

假如一个存储过程具有多个参数。你必须以正确的顺序来传值。有时候这并不是很方便。你可以使用另一种方法,通过名称来传递参数, 如下所示:

EXECUTE check_philisophers @firstname=「Plato」, @secondname=」Aristotle」

该语句完成于前一语句完全相同的事情。然而通过使用参数名,你可以使用任何你想要的顺序来传递参数。

Back to Top 查看 bibi's 资料 搜索其它贴子 bibi 访问 bibi's
 
bibi
Senior Member
Senior Member


加入: 2005/1月/07
Online Status: Offline
回复: 103
Posted: 2005/1月/07 9:27上午 | IP记录 引用 bibi

从存储过程中获得值

你可以从存储过程中接受值。这些值可以直接在你的Active Server Pages中使用(参看第24章「使用Commands」)。同样,你可以在其它的存储过程中获得这些值。假如第一个过程调用了 第二个存储过程,则第一个过程能接受有第二个过程设置的参数值。

例如,下面的存储过程输出变量@conclusion的值:

CREATE PROCEDURE check_philosophers

(@philosopher VARCHAR(30),@conclusion VARCHAR(30) OUTPUT)

AS

IF EXISTS(SELECT name FROM Philosophers WHERE name=@philosopher)

SELECT @conclusion= 「A philosopher」

ELSE

SELECT @conclusion= 「Not a philisopher」

注意在本例子中关键词OUTPUT的使用。该关键词紧跟在参数@conclusion的定义后面。这指明该参数将会用于从该过程 中输出信息。在这个简单的例子中,参数的值将会是「A philosopher」或「Not a philisopher」,根据变量@philosophe的值的不同而变化。

为了这些一个具有输出参数的存储过程,你需要在EXECUTE语句中使用关键词OUTPUT 。假如你在一个批处理或者另外一个存储过程中执行该过程时,你必须首先定义一个变量用于存储从过程中传递出的值,如下面的例子所 示:

DELCARE @proc_results VARCHAR(30)

EXECUTE check_philosophers 「Plato」,@proc_results OUTPUT

PRINT @proc_results

在该例子中的第一个语句定义了将用于存储从过程check_philosophersZ中传出的参数值的变量。该变量将和输出参 数的数据类型一模一样。第二个语句执行存储过程。注意变量@proc_results后面必须紧跟关键词OUTPUT。最后变量 @proc_results的值被打印到屏幕上。

你同样可以使用名称来接收输出参数的值,下面就是一个简单的例子:

DECLARE @proc_results VARCHAR(30)

EXECUTE check_philosophers @philosopher=」Plato」,@conclusion=@proc_results OUTPUT

PRINT @proc_results

注意在该EXECUTE语句中,参数的名称总是列在前面。你要使用@conclusion=@proc_results来接收参 数@conclusion的值,而不是你可能期望的@proc_results=@conclusion。

Back to Top 查看 bibi's 资料 搜索其它贴子 bibi 访问 bibi's
 
bibi
Senior Member
Senior Member


加入: 2005/1月/07
Online Status: Offline
回复: 103
Posted: 2005/1月/07 9:27上午 | IP记录 引用 bibi

 

在存储过程中使用RETURN语句

在上面的文章中我们已经介绍了RETURN语句的使用。在存储过程中该语句的用法和你在批处理中的用法是一模一样的。RETUR N语句会导致过程立即退出。考察下面的例子:

CREATE PROCEDURE check_tables

(@who VARCHAR(30))

AS

IF EXISTS(SELECT name FROM Philosophers WHERE name=@who)

BEGIN

PRINT 「In the Philosophers Table」

RETURN

END

IF EXISTS(SELECT author_name FROM Authors WHERE author_name=@who)

BEGIN

PRINT 「In the Authors Table」

RETURN

END

PRINT 「Not in any tables!」

RETURN

该过程检查了两个表以判断一个人是否是哲学家或作者。假如提供的名称即不是哲学家也不是作者,打印文本「Not in any tables!」。RETURN语句会在一旦存在匹配时,立刻退出该过程。

比如,假定你使用参数「Plato」来执行该过程。首先使用表Philosophers来检测是否存在「Plato」。由于该名 字存在于该表中,所以过程打印文本」In the Philosophers table」,然后过程在执行到RETURN语句时,退出。

 

 

注意

你可以使用RETURN语句结束任何过程。这实际上并不完成任何事情。因为在任何情况下,过程总是要退出的。

当你在存储过程而不是在批处理中使用RETURN语句时,你能够返回一整数值。该整数值代表一个状态码。下面就是上面的例子经过 改写后,返回特定的整数值。

CREATE PROCEDURE check_tables

(@who VARCHAR(30))

AS

IF EXISTS(SELECT name FROM Philosophers WHERE name=@who)

BEGIN

RETURN(1)

END

IF EXISTS(SELECT author_name FROM Authors WHERE author_name=@who)

BEGIN

RETURN(2)

END

RETURN(3)

该过程完成和前面的那个过程相同的任务。然而当在表中发现该姓名时,并不打印信息,代替地,该过程使用RETURN语句来指明从 那个表中找到该名称。例如,假如你使用参数「James Joyce」来执行该过程时。存储过程会返回值2,因为James Joyce在表Authors中,而不是在表Philosophers中。你可以在Active Server Page或其它的存储过程中使用状态值来确定该姓名所在的表。

当使用状态值时,你必须使用对于1或小于-99的值。SQL Server使用值0来报告一个存储过程的成功执行。它同时使用小于0对于-100的值来报告错误(参看表12。1以获得SQL Server使用的状态值的完全列表)。

 


 意思
 
0
 过程成功执行。
 
-1
 对像丢失。
 
-2
 发生数据类型错误。
 
-3
 处理过程被死锁。
 
-4
 发生权限错误。
 
-5
 发生语法错误。
 
-6
 发生恶意用户错误。
 
-7
 发生资源错误,比如空间不够等。
 
-8
 遭遇非致命的内部问题。
 
-9
 遭遇系统限制。
 
-10
 发生致命的内部不稳定性。
 
-11
 发生致命的内部不稳定性。
 
-12
 表或索引被破坏。
 
-13
 数据库被破坏。
 
-14
 发生硬盘错误。
 
包含在-15到-99之间的值有SQL Server保留以在将来使用。
 

 

表12.1 过程状态值

注意

当你使用RETURN语句返回状态值时,切记不要返回NULL值。这会导致错误的结果。

为了获得在前面例子中过程返回的状态值,你可以使用如下的语句:

DECLARE @conclusion INT

EXECUTE @conclusion=check_tables 「James Joyce」

SELECT @conclusion

变量@conclusion用于保存状态值。它必须定义成INT整数数据类型。当你执行存储过程check_tables时,使 用下面的语句将状态值赋予该变量:

EXECUTE @conclusion=check_tables 「James Joyce」

Back to Top 查看 bibi's 资料 搜索其它贴子 bibi 访问 bibi's
 
bibi
Senior Member
Senior Member


加入: 2005/1月/07
Online Status: Offline
回复: 103
Posted: 2005/1月/07 9:27上午 | IP记录 引用 bibi

使用触发器

触发器是一系列当在表中的数据修改时要执行的SQL语句的集合。你可以创建一些当在表中的数据在插入,修改或删除时触发的触发器 。在本节中,你将学习如何创建和使用触发器。

使用命令CREATE TRIGGER来创建触发器

你可以使用语句CREATE TRIGGER来创建一个触发器。与存储过程不同,每个触发器都必须和某一个特定的表相关,而且每一个触发器都只和作用于该表上 的一个或多个动作有关,下面就是CREATE TRIGGER语句的一个简单例子:

CREATE TRIGGER tr_webusers_insert ON webusers FOR INSERT AS

EXECUTE master..xp_sendmail 「administrator」 「New user registered!」

该触发器的名称为tr_webusers_insert。你可以使用任何你想用的名称来命名触发器,但是每个触发器都必须有唯一 的名称。在触发器的名称内包含和触发器相关联的表和动作作为触发器名称的一部分是一个很好的主意。

在CREATE TRIGGER语句中等ON短语用于指明和触发器相关联的数据表。本例子中的触发器和表webusers相关联,它会在该表中的 数据变动时触发。

FOR短语指明会触发该触发器的动作。在本例子中,触发器会在有任何新的数据添加入该表时被触发。同样的,你可以创建一个在表内 的数据进行更新或删除时触发的触发器,你只需要使用关键词UPDATE或DELETE。

在AS短语后面,你可以列出一个或多个SQL短语。在本例子中,触发器仅包含一个单独的语句。当有新的记录添加到表webuse rs中时,管理员会自动发送Email信息,New User registered!

对于触发器,请牢记以下重要几点:

触发器和某一指定的表格有关,当该表格备删除时,任何与该表有关的触发器同样会被删除。比如,当表格webusers被删除时, 触发器tr_webusers_insert也同样会被删除。

在一个表上的每一个动作只能有一个触发器与之关联。例如:你不能在表webusers上创建第二个触发器,该触发器在有数据插入 表中时触发。

在一个单独的表上,你最多只能创建三个触发器与之关联,一个INSERT触发器,一个DELETE触发器和一个UPDATE触发 器。

当你添加第二个由相同动作触发的触发器时,第一个触发器会在没有任何警告信息的条件下被删除。这是件非常令人烦恼的事情。所以必 须仔细地记录你的触发器信息,以防止该类事情的发生。

注意

要观看所有在当前数据库中的触发器列表。你可以使用系统存储过程sp_help。假如你不加任何修改地执行sp_help,该过 程将会显示在当前的数据库中所有的过程,触发器及表。假如在触发器后面跟上一个指定的触发器名称(比如 sp_help tr_webusers_insert),sp_help将会只显示和该触发器有关的信息。

你可以让一个触发器和一个单独的表中多于一个的动作关联。比如你可以修改上面例子中的触发器,使其在表中的数据在添加或更新时触 发。下面就是你修改后的结果:

CREATE TRIGGER tr_webusers ON webusers FOR INSERT,DELETE AS

EXECUTE master..xp_sendmail 「administrator」 「User registered or modified!」

在你创建了触发器后,你可以使用系统存储过程sp_helptext来观看包含在触发器中的语句。该过程显示触发器的文本。例如 :命令 sp_helptext tr_webusers会显示下面的结果:

text

………………………………………..

CREATE TRIGGER tr_webusers ON webusers FOR INSERT,DELETE AS

EXECUTE master..xp_sendmail 「administrator」 「User registered or modified!」

触发器只有在其FOR短语后面指定的动作发生时执行。你不能直接执行一个触发器。你不能直接执行一个触发器。比如说假如你尝试执 行tr_webusers时,你会得到如下的错误信息:

The request for procedure 『tr_webusers』 failed because 『tr_webusers』 is a trigger object

有三种方法可以破坏一个触发器,你可以使用命令DROP TRIGGER后面跟触发器的名称来显式地删除一个触发器。比如,下面的语句删除触发器tr_webusers。

DROP TRIGGER tr_webusers

当数据表被删除时,与之相关的触发器会自动地被删除了。例如,当表webusers被删除时,触发器tr_webusers会自 动被删除掉。

最后,当一个新的在同一个表中与同一动作相关联的触发器创建时,旧的触发器会被自动删除。例如,当触发器tr_webusers 创建时,触发器tr_erbusers_insert会自动地被删除掉。这是因为两个触发器都由同一个数据表的INSERT动作 触发。

 

注意

如果触发器tr_webusers在触发器tr_webusers_insert之前创建。触发器tr_webusers只会被 部分删除,这是因为触发器tr_webusers由INSERT和UPDATE触发。触发器将在有数据更新时继续触发。然而触发 器将不会在有数据添加时触发。新的触发器tr_webusers_insert会取代该任务。

Back to Top 查看 bibi's 资料 搜索其它贴子 bibi 访问 bibi's
 
bibi
Senior Member
Senior Member


加入: 2005/1月/07
Online Status: Offline
回复: 103
Posted: 2005/1月/07 9:28上午 | IP记录 引用 bibi

使用SQL Enterprise Manager创建触发器

使用SQL Enterprise Manager创建触发器要比使用ISQL/w要容易的多。使用SQL Enterprise Manager创建触发器的主要优势是它能保存触发器的文本,这样你就可以在以后的某个时候返回来修改之。通过使用该程序,你可 以很容易地记录和管理你创建的触发器。

在使用SQL Enterprise Manager来创建触发器时,首先启动程序,选择存储你数据表的的数据库,从菜单中选择Manage|Triggers,这时 就会显示Manage Triggers对话框。(见图12.5)。

图12.5 触发器管理窗口

 

 

 

 

 

 

 

 

 

 

 

 

 

在该对话框的顶部有两个下拉式列表,在Table列表内,你可以选择一个数据表。每一个触发器必须和一个表格相关联而且只和一个 表格相关联,在触发器下拉列表框中在单词<new>的旁边包含三个小图标,这三个图标分别表示INSERT,UPD ATE和DELETE,你可以使用该列表框对被这些动作触发的触发器进行选择。单词<new>将在你保存该触发器后 被该触发器的名称所代替。

在对话框的文本区域内,提供了触发器的缺省文本,如下所示:

CREATE TRIGGER <TRIGGER NAME> ON dbo.webusers

FOR INSERT ,UPDATE,DELETE

AS

如果你已经学过前面的内容,你肯定对该文本非常熟悉;这是一个触发器的创建语句。使用你要用的触发器名称来代替表达式< TRIGGER NAME>

该自动创建语句创建一个由INSERT,UPDATE和DELETE触发的触发器。如果你要为各个动作创建不同的触发器,或者创 建一个只由一个动作触发的触发器,你只需要修改这个缺省的语句。简单地删除你不想使用的动作就可以了。

在关键字AS下面,你可以输入你触发器所要用的语句。比如下面的触发器在有一个新用户添加到表webusers中时在表webl og中添加一个新的记录:

CREATE TRIGGER tr_webusers_insert ON dbo.webusers

FOR INSERT

AS

INSERT weblog(activity) VALUES (「New User Added」)

你可以在Manage Triggers对话框内点击保存对像按钮(一个绿色的三角形)来保存一个触发器。当一个触发器第一次被保存时,触发器的文本从 屏幕上消失了。你需要再一次从触发器下拉列表中选择该触发器。

在触发器第一次被保存之后,SQL Server自动地在你的触发器语句内添加新增的语句。比如在触发器tr_webusers_insert保存之后,tr_we busers_insert之内的文本变为:

if exists (select * from sysobjects where id=object_id(『dbo.tr_webusers_insert』) and sysstat & 0xf =8)

drop trigger dbo.tr_webusers_insert

GO

CREATE TRIGGER tr_webusers_insert ON dbo.webusers

FOR INSERT

AS

INSERT weblog(activity) VALUES (「New User Added」)

GO

这些添加的语句有什么用呢?SQL Server为什么要以这种方式来修改你的语句呢?这些新增的语句添加到你的文本中是为了确保在你的触发器创建之前自动被删除。 第一个语句检查是否该触发器已经存在。如果该文件已经自动存在,则该语句会自动地删除它。

你可以使用SQL Enterprise Manager为每一个表创建三个触发器。在你已经保存了触发器之后,在将来你可以使用Manager Triggers对话框对该触发器进行修改。如果没有SQL Enterprise Manager,这并不是很容易完成的事情。

Back to Top 查看 bibi's 资料 搜索其它贴子 bibi 访问 bibi's
 
bibi
Senior Member
Senior Member


加入: 2005/1月/07
Online Status: Offline
回复: 103
Posted: 2005/1月/07 9:28上午 | IP记录 引用 bibi

使用Inserted和Deleted表

我们这里介绍两个特殊的表,Inserted表和Deleted表。此二表仅仅在触发器运行时存在。你可以使用该两个表来精确地 确定触发触发器的动作对数据表所做的修改。比如,通过检查Deleted表,你可以确定那些记录由某一动作删除。考虑下面的例子 :

CREATE TRIGGER tr_webusers_delete ON webusers

FOR DELETE

AS

INSERT weblog (activity) SELECT user_name FROM Deleted

该触发器自动地创建一个webusers表的记录。当在webusers表内删除一个用户的姓名时,触发器会自动地把该姓名插入 到weblog表中。假设你一不小心执行了下面的语句:

DELETE webusers

该语句会删除在webusers表内的所有记录。一般地,这些记录会永久地丢失,然而上面的触发器会在有记录从webusers 表中删除时自动的触发。该触发器会检查表Deleted来确定有那些在webusers表内的记录被删除,并且把所有删除了的记 录拷贝到weblog表中。

为了恢复那些意外被删除的记录,你可以使用INSERT和SELECT语句再一次把它们从weblog表中拷贝到webuser s表中。如果你不能允许意外地丢失一条记录时,你可以使用上面的方法来创建一个表内数据的备份。

Deleted表和有记录被删除的表的列结构一模一样。在前面的例子内,Deleted表具有和webusers表相同的结构。

现在假定你想跟踪所有插入某一表格的记录。比如,你想把每一条插入webusers表内的记录都在weblog表内做备份,你可 以使用下面的触发器来完成该任务:

CREATE TRIGGER tr_webusers_insert ON webusers

FOR INSERT

AS

INSERT weblog(activty) SELECT user_name FROM INSERTED

该触发器和前面的触发器非常相似,除了以下两点以外:

该触发器在有记录插入表格webusers时触发;该触发器是FOR INSERT。

该触发器把记录从Inserted表拷贝到weblog表内。

Inserted表内包含了所有已经插入到表内的新记录。假如一个新用户的姓名插入到webusers表内时,触发器会自动地把 新的用户姓名从webusers表拷贝到weblog表内。

当你想使用一个简单的表来记录所有发生在你数据库中一个非常重要的表的动作时,这种复制数据的方法非常有用。你可以使用该表来获 得对你的数据库的活动的记录,并且可以用于记录和诊断一些可能发生的问题。

你同样可以使用Inserted表和Deleted表来记录UPDATE对触发器所在的表所做的改动。当一个和触发器相关的表内 的数据被修改时,Deleted表包含了所有列在修改之前的值,而Inserted表包含了所有列在修改之后的值。参看下面的表 12.2,以明确每一个动作是如何影响Deleted和Inserted表的。

表12.2.Inserted和Deleted表的内容


 INSERT
 DELETE
 UPDATE
 
Inserted
 插入列
 空
 修改前的列
 
Deleted
 空
 删除列
 修改后的列
 

Inserted表和Deleted表仅仅在触发器在执行时存在,这一点是非常重要的,我们一定要明确。它们在某一特定时间和某 一特定表相关。一旦某一个触发器结束执行时,相应的在两个表内的数据都会丢失。如果你想创建一个在任意一个这些表内数据的永久拷 贝,你需要在触发器内把这些表内的数据拷贝到一个永久的表内。

Back to Top 查看 bibi's 资料 搜索其它贴子 bibi 访问 bibi's
 
bibi
Senior Member
Senior Member


加入: 2005/1月/07
Online Status: Offline
回复: 103
Posted: 2005/1月/07 9:28上午 | IP记录 引用 bibi

结合触发器和事务处理

触发器和事务是一个强有力的结合。你可以使用语句ROLLBACK TRANSACTION来取消触发触发器的动作。考察下面的触发器:

CREATE TRIGGER tr_webusers ON webusers FOR INSERT,UPDATE,DELETE AS

IF DATENAME(dw,GETDATE())=」Tuesday」

ROLLBACK TRANSACTION

该触发器阻止任何人在星期二向表webusers内插入新的记录,删除记录或修改记录。如果你企图在星期二向表中添加新记录,该 动作会被语句ROLLBACK TRANSACTION回转回去。

假如你目前正为如何阻止某一个特定的人访问你的网站而困惑。你不想让这个家伙能够在你的网站上注册。为了做到这一点,你可以创建 一个和表webusers相关的触发器,该触发器用于检查这个家伙的姓名,如下所示:

CREATE TRIGGER tr_webusers FOR INSERT,DELETE,UPDATE AS

IF EXISTS(SELECT user_name FROM Inserted WHERE user_name=」Andrew Jones」)

ROLLBACK TRANSACTION

该触发器阻止姓名为Andrew Jones的人添加入表webusers内,当姓名Andrew Jones包含在某个INSERT,DELETE或UPDATE语句内时,该动作就会被语句ROLLBACK TRANSACTION 倒转回去。

Back to Top 查看 bibi's 资料 搜索其它贴子 bibi 访问 bibi's
 
bibi
Senior Member
Senior Member


加入: 2005/1月/07
Online Status: Offline
回复: 103
Posted: 2005/1月/07 9:28上午 | IP记录 引用 bibi

使用计划任务

许多任务需要在某一特定的时间执行。比如,有时候你可能需要每一个小时收到一封有关你的数据表内数据总结信息的电子邮件。或者你 可能需要在每一星期的特定一天清除在数据库内的旧数据。为了在一个特定的时间执行一个存储过程,你需要使用计划任务。

本部分一步步的说明如何创建某一特定任务和计划该任务,在下面的过程中,你将会学习到如何创建一个任务,该任务会自动的把包含在 你Web服务器log内的数据的总结信息自动地Email给你。

注意

为了使用在该部分内所描述的计划任务,你需要使用一个邮件服务器,比如Microsoft Exchange Server(参看第四章,」Exchange Active Server,Index Server,和NetShow」)。你同样需要配置Internet Information Server(IIS)把它的log保存在SQL Server内而不是保存在文本文件内。为了做到该点,你必须在Internet 服务管理器选择ODBC logging做为当前的log格式(如果你想获得有关如何配置ODBC logging的信息,请参看Internet服务管理器的在线文档)。

创建计划任务的第一个步骤是创建一个你想设定计划的存储过程。在本例子中,我们创建一个接收在你的服务器log内不同的IP地址 的数目的存储过程。该数目可以对你站点的访问次数做一个粗略的估计。

该存储过程同样接收你网站的主页被访问的次数,你网站的主页假定为在你站点的根目录下面的default.cfml文件。假如你 的主页是另外一个文件,在下面的存储过程中,把文件default.cfml替换成你主页的文件名称。

该过程同样假定存储你web服务器的log的表的名称为weblog。假如你在配置IIS时使用了另外一个名称的表,在下面的存 储过程中做相应的修改。

最后,我们还假定你Email帐号的名称配置成管理员。你可以使用任何有效的Email地址(比如:someone@somewhere.com)来替换该名称。你甚至可以把该邮件发送给你的询呼机,如果你的询呼服务器支持接受Email的话。

下面就是该存储过程:

CREATE PROCEDURE get_stats AS

DECLARE @IpCount INT, @HomePageCount INT,

@LogSum VARCHAR(100), @Subj VARCHAR(100)

SELECT @IpCount=COUNT(DISTINCT ClientHost)

FROM weblog WHERE DATEDIFF(hh,LogTime,GETDATE())<1

SELECT @HomePageCount=COUNT(target)

FROM weblog WHERE target=』/default.cfml』

AND DATEDIFF(hh,LogTime,GETDATE())<1

SELECT @LogSum=』Number of visitors:』+CONVERT(VARCHAR(4),@IpCount)

SELECT @LogSum=@LogSum+』… Number of times home

page accessed:』+CONVERT(VARCHAR(4),@HomePageCount)

SELECT @Subj=』Hourly Site Stats』+CONVERT(VARCHAR(20),GETDATE())

EXECUTE master..xp_sendmail

@@recipients=』administrator』,

@@subject=@Subj,

@@massage=@LogSum

RETURN

所有在上面的存储过程内的语句对你来说一定非常的熟悉。前面的两个SELECT语句使用聚集函数获得访问该网站的不同IP地址的 数目和该网站的主页被访问的次数。该信息接收了在一个小时之内的数据,随后的两个SELECT语句把所得到的值格式化成可读的形 式。最后xp_sendmail系统存储过程把查询的结果以Email形式发送出去。

你可以使用任何一般的方法来创建该存储过程,比如ISQL/w或在SQL Enterprise Manager中的管理存储过程对话框。在继续下面的过程之前,在ISQL/w中试著执行该存储过程,如果你的数据库,电子邮件 ,Internet服务器配置都是正确的话,你就能收到一封Email。

该存储过程接收在前一个小时内你的Web服务器log的统计数字。假如你定制该过程,使其每个小时自动执行一次,你就能不断地记 录所有你网站的活动。

为了计划该存储过程,开始SQL Enterprise Manager。选择你的数据库,然后在菜单上选择Server|Scheduled Tasks。这时就会显示Manage Scheduled Tasks 对话框(见图12.6)。

点击新任务按钮(它看起来像一个具有微弱光芒的时钟)。这时就会显示新任务对话框窗口(见图12.7)。

 

 

 

 

 

 

 

 

 

 

 

 

 

图12.6 Scheduled Tasks对话框

 

 

 

 

 

 

 

 

 

 

 

 

 

 

图12.7 新任务对话框

跟随下面的步骤来填写在新任务对话框内的信息:

在名称文本框内,提供一个你的计划任务的名称。比如,你可以使用名称send_stats为该计划任务命名。

在类型下拉式菜单内,选择TSQL。这说明你将使用T-SQL语句。

在数据库下拉菜单内,选择要存储你的计划任务的数据库。

在命令文本窗口内,输入语句EXECUTE get_stats来执行你刚刚创建的过程。

在对话框的Schedule部分内,选择Recurring选项。这将会使你的过程在一定的时间间隔内执行。

在你输入完所有的上述指定的信息后,点击Change按钮。这里你可以改变你任务的计划时间表。当你点击Change按钮时,这 时就会显示Task Schedule对话框(见图12.8)。

 

 

 

 

 

 

 

 

 

 

 

 

 

图12.8 Task Schedule对话框

在Task Schedule对话框内,计划你的任务,使其每小时执行一次。在Occurs部分内,选择Daily选项。在Daily Frequency部分内,选择Occurs Every并且指明1Hour(s)。点击close来关闭Task Schedule对话框。

最后,在Manage Scheduled Tasks对话框内,点击Add按钮添加你的计划任务。此时在对话框内的Task list页内就会显示你计划任务的名称。你可以在将来的任何时候返回此处来检查该计划任务的状态,观看该计划任务最后一次执行的 时间或者看看是否该任务包含错误。

 

你可以使用Manage Scheduled Tasks对话框在你创建一个计划任务后删除之。如果你想删除该计划任务,只需要点击Delete Task 按钮(它看起来像一个有一条对角带的时钟)。

你同样可以使用Manage Scheduled Tasks对话框来观看你计划任务的历史。计划任务的历史提供了详细的关于你计划任务过去活动的信息。点击Task History按钮来观看该信息。

总结

本章介绍了Microsoft SQL Server几个非常强有力的特性。你学习了如何使用SQL Server Web 向导自动从数据库表中生成Web页。你同样也学习了如何创建存储过程,触发器和计划任务。

到目前为止,你已经学习了如何使用脚本,基本和查询语言来创建HTML网页,服务器端脚本和数据库查询。在本书的下一部分,你将 进一步深入学习这些知识,学习如何把这些语言集合到Active Server Pages内。这可以使你能够创建非常先进的网站。

Back to Top 查看 bibi's 资料 搜索其它贴子 bibi 访问 bibi's
 

如果你想回复的话你必须首先 login
如果你还没有注册的话你必须首先 注册

<< 上一页 页 of 2
  回复发表新主题
显示可打印的页面 显示可打印的页面

论坛跳转
不能 张贴新论题在这个讨论版
不能 回应论题在这个讨论版
不能 删除你的发言在这个讨论版
不能 编辑你的发言在这个讨论版
不能 新增投票标题在这个讨论版
不能 在这个讨论版投票

Edit by doreme Forums version 2004
Welcome ©2001-2004 doreme Guide

This page was generated in 0.1680 seconds.

 
保养品
保养品, Skin Care
www.elady.tw
美材批发
美材, Cosmetic
www.elady.tw/beauty_org
保养品批发
名牌保养品、保养品批发
gb.perfume.com.tw/skincare
饰品批发
饰品、饰品批发
gb.perfume.com.tw/ornament