触发器操作:在触发器中嵌套多个游标

触发器操作:在触发器中嵌套多个游标

 1 if   exists ( select  name  from  sysobjects  where  name = ' Insert_PicType_T07_Trig '   and  type = ' TR ' )
 2 DROP   TRIGGER  Insert_PicType_T07_Trig
 3 go
 4
 5 CREATE   TRIGGER  Insert_PicType_T07_Trig  ON   PicType_T07
 6 FOR   INSERT  
 7 AS
 8 BEGIN
 9     print   ' 开始响应 '
10     DECLARE   @PicType_id   int , @PicRightsType_id   bigint         -- 图片类型ID,更新浏览图片权限类型ID
11     DECLARE  PicTypeId_cursor  CURSOR   FOR  
12       SELECT   RT07F01
13       FROM  INSERTED
14     OPEN  PicTypeId_cursor
15     FETCH   NEXT   FROM  PicTypeId_cursor 
16     INTO   @PicType_id
17     WHILE   @@FETCH_STATUS   =   0
18   BEGIN
19     --  声明嵌套的游标
20     --  从嵌套的游标取出另一个表(PicRightsType)的不重复的权限类型ID
21     DECLARE  PicRightsTypeID_cursor  CURSOR   FOR  
22     SELECT   distinct (RT16F01)
23     FROM    PicRightsType_T16
24     -- 打开游标
25     OPEN  PicRightsTypeID_cursor
26     FETCH   NEXT   FROM  PicRightsTypeID_cursor  INTO   @PicRightsType_id
27     IF   @@FETCH_STATUS   <>   0  
28        PRINT   ' No ID '      
29     WHILE   @@FETCH_STATUS   =   0
30     BEGIN
31        INSERT   INTO  PicRightsType_T16(RT16F01,RT16F02)  VALUES ( @PicRightsType_id , @PicType_id )
32        FETCH   NEXT   FROM  PicRightsTypeID_cursor  INTO   @PicRightsType_id
33     END
34     CLOSE  PicRightsTypeID_cursor
35     DEALLOCATE  PicRightsTypeID_cursor
36    
37     --  Get the next PicTypeId.
38     FETCH   NEXT   FROM  PicTypeId_cursor 
39     INTO   @PicType_id
40   END
41   CLOSE  PicTypeId_cursor 
42   DEALLOCATE  PicTypeId_cursor 
43 END
44 GO
45
46
47 -- INSERT INTO PICTYPE_T07(RT07F02,RT07F03)VALUES('frj照片','1')   
48
49

1 CREATE   TRIGGER  CheckDate4  ON  cardtype_t11
2 FOR   update
3 AS
4 begin
5 print   ' hao '
6 SELECT   *   FROM  inserted
7 ROLLBACK    // 回滚刚才的操作
8 end
9 drop   trigger  CheckDate4

posted on 2005-12-09 12:49 DotNet编程 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/furenjun/archive/2005/12/09/293694.html