初学SQL server2005中用存储过程和触发器

 

一个很好的入门ppt:

 sqlserver2005-创建存储过程和触发器.rar   

 

 

一、存储过程初学

1.打开新建查询的按钮，选择好数据库，输入以下代码执行：

 

CREATE PROCEDURE proc_person

AS

SELECT uuid, org_id

FROM TB_SUBJECT

GO

 

这样便会产生一个叫proc_person的存储过程，这个作用就是选择uuid和org_id这两个字段.

用“exec proc_person”便可以执行这个存储过程

2.当再次在数据库树形栏上点击“可编程性”->“存储过程”->“修改”之后，便会出现以下代码：

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 

ALTER PROCEDURE [dbo].[proc_person]

AS

SELECT uuid, org_id

FROM TB_SUBJECT

 

 

这说明alter是用来修改存储过程的，而create是第一次用的。如果在“set QUOTED_IDENTIFIER ON”后面加入“WITH ENCRYPTION”，这样创建的存储过程便不能看到自己的代码了。

 

3. DROP命令可以用来删除存储过程

用“drop procedure proc_person”即可完成删除

 

4. 【例子】创建一个存储过程，以简化对sc表的数据添加工作，使得在执行该存储过程时，其参数值（@Param1,@Param2,@Param3）作为数据添加到表中。（其数据类型为char(10) ,char(2) ,real ）

 

可以用：

CREATE  PROCEDURE  [dbo].[ pr1_sc_ins]

@Param1 char(10) , @Param2  char(2) , @Param3 real

AS

BEGIN

insert into sc(sno,cno,score)

values(@Param1,@Param2,@Param3)

END

 

当使用“EXEC  pr1_sc_ins  @Param1=‘3130040101’,@Param2=‘c1’, @Param3=85”

即可执行以上的存储过程

 

 

二、触发器初学

1.触发器介绍

触发器是一种特殊的存储过程，它在执行语言事件时自动生效。

SQL Server2005 包括两大类触发器：DML 触发器和 DDL 触发器。常用DML.

   （1）DML 触发器在数据库中发生数据操作语言 （DML） 事件时将启用。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误，则整个事务即自动回滚。

   （2）DDL 触发器是 SQL Server 2005 的新增功能。当服务器或数据库中发生数据定义语言 （DDL） 事件时将调用这些触发器。

 

2.触发器的deleted 和 inserted 逻辑表

    DML 触发器使用 deleted 和 inserted 逻辑表。它们在结构上和触发器所在的表的结构相同，SQL Server 会自动创建和管理这些表。

可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件。Deleted表用于存储delete，update语句所影响的行的副本。在执行delete或 update 语句时，行从触发器表中删除，并传输到deleted表中。Inserted 表用于存储  Insert 或  update 语句所影响的行的副本，在一个插入或更新事务处理中，新建的行被同时添加到  Inserted  表和触发器表中。Inserted 表中的行是触发器表中新行的副本。

 

3.找到新建触发器的地方:

 

在表下面的

 

4.新建以下触发器:

 

CREATE TRIGGER sc_ins  ON  tb_subject

AFTER  INSERT

AS

BEGIN

DECLARE @bh char(5)--申请bh变量

Select @bh=Inserted.uuid from Inserted--从Inserted临时表中拿到数据

If not exists(select uuid from tb_subjectdetail where tb_subjectdetail.uuid=@bh)

Delete tb_subject where uuid=@bh

END

 

 

 

这样便生成了一个叫做sc_ins的触发器。

再次打开之后便是alter：

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 

ALTER TRIGGER [sc_ins]  ON  [dbo].[TB_SUBJECT]

AFTER  INSERT

AS

BEGIN

DECLARE @bh char(5)--申请bh变量

Select @bh=Inserted.uuid from Inserted--从Inserted临时表中拿到数据

If not exists(select uuid from tb_subjectdetail where tb_subjectdetail.uuid=@bh)

Delete tb_subject where uuid=@bh

END

 

如果要删除触发器的话，用“DROP TRIGGER sc_ins”即可

 

5.【举例】建立触发器（ tr_del_s ），当删除表 s 中的记录时，自动删除表sc 中对应学号的记录。（可设变量@bh ）

程序清单如下：

CREATE TRIGGER tr_del_s ON s

AFTER DELETE AS

BEGIN

DECLARE @bh char(5)

SELECT @bh=deleted.sno from deleted   --从deleted表中得到要删除的临时数据

DELETE sc where sno=@bh

END

 

6. INSTEAD OF触发器使SQL SERVER 执行触发器中的代码。而不是执行导致触发器激活的操作。

 

7. DDL 触发器会为响应多种数据定义语言 （DDL） 语句而激发。这些语句主要是以 CREATE、ALTER 和 DROP 开头的语句。DDL 触发器可用于管理任务

例如：使用 DDL 触发器safety来防止数据库中的任一表被修改或删除。程序清单如下：

 CREATE  TRIGGER  safety

 ON DATABASE

 AFTER  DROP_TABLE, ALTER_TABLE

 AS

 PRINT 'You must disable Trigger "safety" to drop or alter tables!'

 ROLLBACK