存储过程和触发器

存储过程：

定义：存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。

使用存储过程的优点：

1.    在服务器端运行，执行速度快。

2.       执行一次后，其执行规划就驻存高速缓冲存储器中，以后操作时只需从高速缓冲存储器中调用已编译后的二进制代码执行，提高了系统性能。

3.       确保数据库的安全。

4.       自动完成需要预先执行的任务。

存储过程的类型：

系统存储过程：由系统提供，作为命令执行各种操作。

本地存储过程：在用户数据库中创建，可完成特定操作任务。

临时存储过程：只能在一个用户会话中使用。名称以#开头。

远程存储过程：从远程服务器上调用。

扩展存储过程：是指在SQL Server环境之外执行的动态链接库，其前缀是sp_

存储过程的语句：

创建格式：

Create procedure procedue_name

[@parameter data_type][output]  

　　[with]{recompile|encryption}

　　as

sql_statement

解释：@paramete是存储过程的形参，data_type用于指定形参的数据类型

  Output指示参数是输出参数，可返回信息。

recompile|encryption参数的处理方式。

sql_statement代表过程体包含的T-SQL语句。

例1：返回081101号学生的成绩情况。该过程不适用参数。

USE_PXSCJ

GO

Create procedure student_info

  As

     Select *

         From CJB

Where 学号=’081101’

GO

 

执行存储过程：

通过EXEC命令可以执行一个已定义的存储过程

例2：执行例1的存储过程。

Exec student_info

用户存储过程的删除：

格式：Drop procedure procedure_name.

例3：删除例1的存储过程。

Drop procedure student_info

用户存储过程的修改：

格式：alter procedure procedure_name.

例4：创建名为select_students的存储过程，默认情况下，该存储过程可查询所有学生的信息，然后授权。当该存储过程需要更改为能检索计算机专业的学生信息时，用alter procedure 重新定义该存储过程。

程序如下：

Create procedure select_students//创建存储过程

  As

   Select *

From XSB

   Order by 学号

Go

Alter procedure select_students with encryption

 As

  Select *

From XSB

 Where 专业=’计算机’

Order by 学号

Go

触发器：

定义：触发器是一类特殊的存储过程，在对特定表或视图发出 UPDATE、INSERT 或 DELETE 语句时自动执行。

注意：触发器不需要调用，也不传参或接受参数，当对一个表的特殊事件出现时，它就会被激活。

作用：用于保护表中的数据。

触发器的类型：

DML触发器：当对表进行insert,update或delete操作时发生。作用是：保持数据库中数据的完整性。

DDL触发器：当进行alter,drop或create操作时发生。作用是：执行管理操作。

触发器中的特殊表：

inserted 逻辑表：当向表中插入数据时，触发INSERT触发器，新的记录插入到触发器表和inserted表中。

       deleted逻辑表：当从表中删除记录时，触发DELETE触发器，被删除的记录存放到deleted逻辑表中。           

 

触发器的创建：

格式：create trigger trigger_name

          On{table或view}//指定操作对象

          [With encryption]//加密

{

{{for |after|instead of}{[delete][,][insert][,][update]}//指定激活类型

[ WITH APPEND ]   

 [ NOT FOR REPLICATION ] /*触发器不作为 SQL Server 复制的一部分发布 */

 As

Sql_statement

}

}

例5：创建一个表table1,其中只有一列a。在表上创建一个触发器，每次插入操作时，将变量@str的值设为“Trigger is working”并显示。

Create table table1(a int)

Go

Create trigger table1_insert

   On table1 after insert

 As

Begin

Declare @str char(50)

Set @str=’ Trigger is working’

Print @str

end

例6: 在XS表上创建一触发器，若删除学生信息，则同时删除该学生的选课信息。

CREATE TRIGGER delete_trig  ON  XS  FOR delete

AS

GEGIN

                     delete xs_kc  where 学号  in  (select 学号 from deleted)

END

GO

修改触发器：

格式：ALTER TRIGGER trigger_name ON ( table | view )

例7：修改XSCJ数据库中在XS表上定义的触发器reminder

Use XSCJ

Alter trigger reminder on XS

  For update

  As raiserror(“执行的操作是修改”，16,10)

Go

删除触发器：

语法格式：

DROP TRIGGER { trigger_name } [ ,...n ]//n表示可以执行多个触发器

例8：删除触发器reminder。

USE XSCJ

IF EXISTS (SELECT name FROM sysobjects

      WHERE name = 'reminder' AND type = 'TR')

   DROP TRIGGER reminder

GO