触发器+游标的应用

<strong>例1：</strong>
CREATE TRIGGER [DeleteUserPermission] ON dbo.UserTBL 
FOR DELETE 
AS
DECLARE  @bank_id  varchar(20)
DECLARE  @user_id  varchar(20)
DECLARE  delcursor  CURSOR FOR
SELECT  BankID, UserID  FROM DELETED


OPEN delcursor
FETCH NEXT FROM delcursor
INTO @bank_id, @user_id


WHILE @@FETCH_STATUS=0
	BEGIN
		DELETE FROM PermissionTBL 
			WHERE BankID= @bank_id   
			      AND UserRoleID = @user_id 
		FETCH NEXT FROM delcursor
		INTO @bank_id, @user_id
	END


CLOSE delcursor
DEALLOCATE delcursor

例2：

CREATE TRIGGER JSFSDelete ON [dbo].[JSFSTbl] 
FOR DELETE 
AS

DECLARE @JSTYPE nvarchar(1),@YFSQSNO nvarchar(50),@JSQQSNO nvarchar(50),
	     @JSFLG1 nvarchar(1),@JSFLG2 nvarchar(1),@JSFLG3 nvarchar(1),@JSFLG4 nvarchar(1)

DECLARE YB_Cursor CURSOR FOR
	SELECT YFSQSNO,JSQQSNO,JSTYPE FROM deleted

OPEN YB_Cursor
FETCH NEXT FROM YB_Cursor
INTO @YFSQSNO,@JSQQSNO,@JSTYPE

WHILE @@FETCH_STATUS=0
	BEGIN	

	IF @JSTYPE='1' 	
		--更新信用证的JSFLG1，JSFLG2
		BEGIN
			/*信用证JSFLG1：
			   当信用证对应的结算金额等于该信用证对应的所有订单的总金额的时候，该信用证结算，JSFLG1＝‘1’，否则JSFLG1＝‘0’*/
			SET @JSFLG1=(CASE WHEN (SELECT SUM(JSAmt)
	          				 	             FROM JSFSTbl  
	         					          WHERE YFSQSNO=@YFSQSNO
	         				  	                AND JSTYPE = '1') 
	         		 		                     = 
						         (SELECT SUM(XSJE)
	         		         	 		             FROM OrderTbl
	         				 	           WHERE OrderNo IN (SELECT DISTINCT OrderNo
	                  						                      FROM LCDDTbl
	                  						                   WHERE KLQQSNo = @YFSQSNO)) 
					            THEN '1'  ELSE '0'  END) 
	
			/*信用证JSFLG2：
			   当信用证结算请求书对应的结算金额等于该信用证结算请求书对应的所有订单的总金额的时候，该信用证结算请求书结算，JSFLG2＝‘1’；
			   当信用证结算请求书对应的结算金额大于0，该信用证结算请求书部分结算，JSFLG2＝'2'；
			   否则JSFLG2＝‘0’*/
			SET @JSFLG2= (CASE 
					WHEN 	(SELECT SUM(JSAmt)
	 					    FROM JSFSTbl 
	 					 WHERE JSTYPE = '1' 
						       AND YFSQSNO=@YFSQSNO 
						       AND JSQQSNO=@JSQQSNO) 
	 				   	=
	  					(SELECT SUM(XSJE)
	 					    FROM OrderTbl  
	                    				 WHERE OrderNo IN (SELECT DISTINCT OrderNo
	          							            FROM LCDDTbl
	          							         WHERE KLQQSNo = @YFSQSNO 
									  AND JSQQSNO= @JSQQSNO))  THEN '1'
					WHEN  (SELECT SUM(JSAmt)
	 					    FROM JSFSTbl 
	 					WHERE JSTYPE = '1' 
						      AND YFSQSNO=@YFSQSNO 
						      AND JSQQSNO=@JSQQSNO)  > 0 
					THEN '2'  ELSE '0'  END) 
				
			UPDATE LCTbl SET JSFLG1=@JSFLG1 WHERE KLQQSNO=@YFSQSNO
			UPDATE LCJSTbl SET JSFLG2=@JSFLG2 WHERE KLQQSNO=@YFSQSNO AND JSQQSNO=@JSQQSNO
		END
	
	ELSE
		--@JSTYPE<>'1'，即汇款的场合，更新汇款的JSFLG4
		BEGIN
			/*汇款JSFLG4：
			   当汇款预付到帐确认金额大于等于预付货款情款书金额，而且汇款现金结算到帐确认金额大于余款请款书金额的时候，
			   JSFLG4='1'；
			   否则JSFLG4＝‘0’*/
			SET @JSFLG4=(CASE 
					WHEN (SELECT SUM(JSAmt) 
						  FROM JSFSTbl 
					            WHERE JSTYPE<>'1'          --预付款+余款
						     AND YFSQSNO=@YFSQSNO)
						>=
						(SELECT SUM(JSAmt)
						    FROM HKXJJSTbl 
						WHERE YFHKNo=@YFSQSNO)
						+
						(SELECT YFAmt FROM HKTbl WHERE YFHKNO=@YFSQSNO)
	                  	        		THEN '1'  ELSE '0'  END)

			UPDATE HKTbl SET JSFLG4=@JSFLG4 WHERE YFHKNO=@YFSQSNO
		END
	
	IF @JSTYPE='2'
		--更新汇款的JSFLG1
		BEGIN
			/*汇款JSFLG1：
			   当汇款预付货款到帐确认金额大于等于该汇款预付货款请款书的预付金额的时候，该汇款预付货款请款书结算，JSFLG1＝‘1’；
			   当汇款预付货款到帐确认金额大于0，该汇款预付货款部分结算，JSFLG1＝'2'；
			   否则JSFLG1＝‘0’*/
			SET @JSFLG1=(CASE 
					WHEN (SELECT SUM(JSAmt)
	         					   FROM JSFSTbl
	         					WHERE YFSQSNo = @YFSQSNO
	                  			   	      AND JSTYPE = '2')            --预付款
	      				      >=
	        				  	(SELECT YFAmt
	         					    FROM HKTbl
	         					 WHERE YFHKNo = @YFSQSNO ) THEN '1' 
	      				WHEN (SELECT SUM(JSAmt)
	         					   FROM JSFSTbl
	         				             WHERE YFSQSNo = @YFSQSNO
	                  				      AND JSTYPE = '2')             --预付款                 				
	      				     > 0  THEN '2'   ELSE '0'  END)
			
			UPDATE HKTbl SET JSFLG1=@JSFLG1  WHERE YFHKNO=@YFSQSNO
		END
	
	IF @JSTYPE='3'		
		--更新汇款的JSFLG2
		BEGIN
			/*汇款JSFLG2：
			   当汇款余款到帐金额大于等于该汇款余款请款书的余款金额的时候，该汇款余款请款书结算，JSFLG2＝‘1’；
			   当汇款余款到帐金额大于0，该汇款余款请款书部分结算，JSFLG2＝'2'；
			   否则JSFLG2＝‘0’*/
			SET @JSFLG2=(CASE 
					WHEN (SELECT SUM(JSAmt)
	                                		 	   FROM JSFSTbl
	                               			WHERE YFSQSNo = @YFSQSNO
	                  			     	      AND JSQQSNo = @JSQQSNO
						      AND JSTYPE = '3')              --余款
	      				      >=
	          					 (SELECT JSAmt
	         					     FROM HKXJJSTbl
	         					  WHERE YFHKNo =@YFSQSNO
						        AND XJJSNo = @JSQQSNO)  THEN '1' 							  
	                  			WHEN (SELECT SUM(JSAmt)
	         					   FROM JSFSTbl
	         				 	WHERE YFSQSNo = @YFSQSNO
	                  				      AND JSQQSNo = @JSQQSNO
	                  				      AND JSTYPE = '3')              --余款
	      				     > 0 	THEN '2'  ELSE '0'  END)
	
			UPDATE HKXJJSTbl SET JSFLG2=@JSFLG2  WHERE YFHKNO=@YFSQSNO AND XJJSNO=@JSQQSNO
		END

	FETCH NEXT FROM YB_Cursor
	INTO @YFSQSNO,@JSQQSNO,@JSTYPE

	END

CLOSE YB_Cursor
DEALLOCATE YB_Cursor