游标,存储过程,触发器,事务

游标：对查询出的结果集进行操作

Declare 游标名scroll cursor

For

T-SQL 语句

[Declare 变量]

Open 游标名

[If @@fetch_status=0]

Fetch [first/next/absolutenumber/relative number/prior/last] from游标名[into 变量]

[T-SQL 语句]

Close 游标名

Deallocate 游标名

例：

Declare mycursor scroll cursor

For

Select * from student where stid=’123456’

Declare @name varchar(10)

Declare @sex varchar(2)

Fetch first mycursorinto into @name,@sex

Select @name as 姓名,@sex as 性别

while @@fetch_status=0

begin

Fetch nextmycursor into @name,@sex

Select @name as 姓名,@sex as 性别

end

Close mycursor

Deallocate mycursor

说明：1、@@fetch_status值：为0：fetch命令被成功执行

                                             为-1：命令执行失败或超过数据结果集范围

                                             为-2：所读取的数据已经不存在

         2、scroll：表明可执行所有操作（frist,last,prior(返回当前结果集中当前一行的前一条记录)，next）

         3、into：允许将使用fetch命令读取的数据存放到变量中

*******************************************************************************

存储过程（storedProcedure）：是一组为了完成特定功能的T-SQL语句集合，通过execute命令执行。

Create proc 过程名

@parameter 参数类型

@parameter 参数类型output

As

Begin

T-SQL 语句

End

执行：

Execute 过程名[参数值，……][output]

例：

Create proc myprocedure

As

@number varchar(10)

@ddnumber varchar(20)

Begin

If  exists(select* from student where idnumber=@number)

Begin

       If exists(select * from teacher where impart=@number)

              Begin

                     Updatestudent set enimpart=@ddnumber

                     Print‘success’

              End

       Else

              Print‘No success’

End

Else

       Print‘return to sender’

End

执行：

Execute myprocedure ‘123’ ,‘456’

例2：

create procedure myprocedure @aint output,@b int output,@c int output

as

begin

set @c=@a+@b

end

 

declare @a1 int,@b1 int,@c int

set @a1=1

set @b1=2

execute myprocedure @a1 output,@b1 output,@c output

print @c

/*存储过程类似于面向对象编程中的各种事件上的处理代码及函数，只是用来处理一些具体的事情，而触发器的执行过程类似于按钮响应的某种事件。*/

*******************************************************************************

触发器：通过事件触发而被执行，当对某一表进行诸如update\delete\insert等数据记录操作时，SQL Server就会自动执行触发器事先定义好的语句。

create trigger 触发器名称

on 表名

for insert | update | delete

as

T-SQL 语句

 

After

默认触发器，触发器在触发它们的语句完成后执行。如果该语句因错误（如违反约束或语法错误）而失败，触发器将不会执行，只能为表指定该触发器。

Instead of

执行触发器语句，但不执行触发触发器的 SQL 语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行 delete 语句。可在表和视图上指定 instead of 触发器。只能为每个触发操作（insert、update 和 delete）定义一个 instead of 触发器。instead of 触发器可用于对 insert 和 update 语句中提供的数据值执行增强的完整性检查。

if update(列名):

检查是否更新了某一列，用于 insert 或 update，不能用于 delete。

inserted、deleted:

这是两个虚拟表，inserted 保存的是 insert 或 update 之后所影响的记录形成的表，deleted 保存的是 delete 或 update 之前所影响的记录形成的表。如果向 inserted 或 deleted 虚拟表中取字段类型为 text、image 的字段值时，所取得的值将会是 null。

触发器回滚

我们看到许多注册系统在注册后都不能更改用户名，但这多半是由应用程序决定的， 如果直接打开数据库表进行更改，同样可以更改其用户名，在触发器中利用回滚就可以巧妙地实现无法更改用户名。

use 数据库名

go

create trigger tr

on 表名

for update

as

    if update(userName)

        rollback tran

关键在最后两句，其解释为：如果更新了 userName 列，就回滚事务。

*******************************************************************************

/**//*--开始事务--*/

begin transaction

declare @errorSum int    --定义变量，用于累计事务执行过程中的错误

set @errorSum = 0        --赋初值

/**//*--定义操作变量--*/

declare @a int,@b int,@c int

set @b=4

set @c=6

set @a=@b+@c

SET @errorSum=@errorSum+@@ERROR --累计是否有错,每执行一条命令后都要进行累计操作

/**//*--根据是否有错误，确定事务是提交还是回滚--*/

if @errorSum>0

   begin

       print '1'    --报错，执行回滚事务状态

       rollbacktransaction

   end

else

   begin

       print '2'    --正确，执行提交事务状态

        commit transaction

   end

 

 

在删除触发器之前可以先看一下触发器是否存在：

if Exists(select name from sysobjects where name=触发器名称 and xtype='TR')

注：关于触发器的介绍参照http://blog.163.com/aspnetshop@126/blog/static/173131335201071071217310/