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

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

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

-- 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

--INSERT INTO PICTYPE_T07(RT07F02,RT07F03)VALUES('frj照片','1')
48

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