SQLServer存储过程和触发器学习记录及简单例子

一、存储过程

　　存储过程即为能完成特定功能的一组SQL语句集。如果需要对查出的多条数据进行操作的话，这里需要理解游标（CURSOR）的概念，对于oracle有for each row命令，可以不用游标。

游标可以理解为一个结果集，描述cursor的关键字有：

SCROLL关键字指明游标可以在任意方向上滚动

FORWARD_ONLY指明游标只能向前滚动。

READ ONLY指明在游标结果集中不允许进行数据修改。

UPDATE关键字指明游标的结果集可以修改。

GLOBAL关键字使得游标对于整个连接全局可见

LOCAL关键字指明游标是局部的，它只能在它所声明的过程中使用。

等等。。。

一般结果集需要遍历，如例，将一些表中的字段查出来，插入到另外一张表中：

例1：查询表数据插入到另外一张表

CREATE PROCEDURE sp_insertIntoTest_hibernate 
AS 
DECLARE @projectid VARCHAR(20) 
DECLARE @projectcode VARCHAR(20) 
DECLARE @projectname VARCHAR(100) 
DECLARE @projectamount DECIMAL(16,2) 
DECLARE @remark VARCHAR(1000) 

DECLARE cs CURSOR FOR  --定义游标的数据集
 
SELECT L.PROJECTID,P.CODE,L.PROJECTNAME,L.LEASINGAMOUNT, L.proInfoSummary 
FROM LAPROJECT L LEFT JOIN PARTY_CUSTOMER P ON P.ID=L.TENANTID 
WHERE (L.TENANTID<>'' AND L.TENANTID IS NOT NULL) ORDER BY L.PROJECTID 

BEGIN 
DELETE FROM test_hibernate 
OPEN cs --打开游标
FETCH NEXT FROM cs INTO @projectid,@projectcode,@projectname,@projectamount,@remark --从游标中取值赋给变量
	WHILE @@FETCH_STATUS = 0  --fetch语句执行成功返回0，fetch语句执行失败或者此行不在结果集中返回-1，被提取的行不存在则返回-2.
		BEGIN 
		PRINT '====start insert====' 
		INSERT INTO test_hibernate VALUES(@projectid,@projectcode,@projectname,@projectamount,@remark) 
		PRINT '==== end  insert====' 
		FETCH NEXT FROM cs INTO @projectid,@projectcode,@projectname,@projectamount,@remark --继续取下一行数据
		END 
	CLOSE cs --关闭游标
	DEALLOCATE cs --删除游标
END

　　这个sp起初运行的时候老是报游标已存在问题，时不时还报游标不存在，郁闷。后来修改了下存储过程换了个游标名称就好了，网上查了很多说是游标没关闭没释放或者是关闭释放位置不对等问题，自己实践证明我的问题不是这样的，至今搞不懂是啥原因造成的问题，可能是第一次写sp的时候没释放，后面加上了释放和删除那两句话吧。

例2：分页查询

CREATE PROCEDURE sp_getpermbal  
@pageSize INT,
@page INT 
AS 
DECLARE @temp INT SET @temp=@pageSize*(@page-1)
BEGIN 
SELECT TOP (SELECT @pageSize) * FROM LAPERMBAL 
WHERE projectid+contractid+grantid+convert(varchar(20),retsn)+convert(varchar(10),ratebtdt,120) NOT IN 
(SELECT TOP (SELECT @temp) projectid+contractid+grantid+convert(varchar(20),retsn)+convert(varchar(10),ratebtdt,120) 
FROM LAPERMBAL ORDER BY projectid,contractid,grantid,retsn,ratebtdt DESC) 
ORDER BY projectid,contractid,grantid,retsn,ratebtdt DESC
END

　　这个查数据需要用到主键，而本例的表使用联合主键，所以进行拼接来识别一条数据的唯一性。

二、触发器（SQLServer）

触发器都说是一种特殊的存储过程，那我也就这么理解了，还挺有道理的、让人比较容易接受的解释。在数据库的表数据有增、删、改的操作时可自动触发的存储过程。

首先理解inserted和deleted两张虚表，看了下oracle相关的内容，应该是用new和old两张虚表，取值可用new.字段名，old.字段名。

新增操作新增的记录存到系统建的虚表inserted中，deleted表无记录；

删除操作删除的记录存到系统建的虚表deleted中，inserted表无记录；

修改操作（分解为先删除再插入）删除的记录存到系统建的虚表deleted中，新增的记录存到inserted表中。

然后就是触发器类型，after触发器和instead of 触发器，这个不理解的可以百度，有很多比我解释的更清楚的大神写的文章。

理解完之后，记录下相关语法：

新建触发器：

例：更新test_hibernate表，则将被更新记录的字段值置为“test success---”

CREATE TRIGGER test_trigger 
ON test_hibernate 
FOR UPDATE 
AS 
DECLARE @projectid VARCHAR(20),@projectcode VARCHAR(20) 
DECLARE cs CURSOR local FOR 
SELECT projectid,projectcode FROM deleted 
BEGIN 
OPEN cs 
FETCH NEXT FROM cs INTO @projectid,@projectcode 
WHILE @@FETCH_STATUS=0 
　　BEGIN 
　　UPDATE test_hibernate SET remark='test success---' WHERE projectid=@projectid AND projectcode=@projectcode
　　FETCH NEXT FROM cs INTO @projectid,@projectcode 
　　END 
CLOSE cs 
DEALLOCATE cs 
END 
GO

　　修改触发器：

ALTER TRIGGER test_trigger  
ON test_hibernate 
FOR UPDATE 
AS 
......

　　禁用、启用触发器：

ALTER TABLE test_hibernate DISABLE TRIGGER test_trigger
ALTER TABLE test_hibernate ENABLE TRIGGER test_trigger

ALTER TABLE test_hibernate DISABLE TRIGGER ALL 
ALTER TABLE test_hibernate ENABLE TRIGGER ALL

关于触发器的内容，可以参考这位大哥的http://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.html，个人觉得写得简单易懂。